LINQ Core中的枢轴和UNPIVOT

Question

我是Linq Core的新手。我想在LINQ Core中编写一个查询，以便在表格上进行枢轴和UNPIVOT。我已经为此编写了SQL查询，但是需要帮助转换为LINQ查询。我搜索了几篇文章，但没有帮助。

以下是我需要转换为linq查询的枢轴和undivot的SQL查询：

SELECT ResourceName,
            max(ENText)as ENText,
            max(FRText)as FRText,
            max(ZHText)as ZHText,
            max(DEText)as DEText,
            max(ITText)as ITText,
            max(JAText)as JAText,
            max(PTText)as PTText,
            max([PT-BRText]) as [PT-BRText],
            max(RUText) as RUText,
            max(ESText) as ESText,
            max(SVText) as SVText into #temp FROM   
GenericLanguageTranslation 
PIVOT  
(  
max(Translation) FOR LanguageID IN (
            ENText,
            ZHText,
            FRText,
            DEText,
            ITText,
            JAText,
            PTText,
            [PT-BRText],
            RUText,
            ESText,
            SVText)
) AS Tab2  
group by ResourceName
order by 1

SELECT NEWID() as Id,ResourceName, [LanguageID],[Translation]-- into #GenericLanguageTranslation
FROM #temp
UNPIVOT
(
       [Translation]
       FOR [LanguageID] IN 
       (
            ENText,
            ZHText,
            FRText,
            DEText,
            ITText,
            JAText,
            PTText,
            [PT-BRText],
            RUText,
            ESText,
            SVText
       )
) AS UnpivotTranslation

有人可以帮助我吗？

Answer 1

我能够使用下面的LINQ获取上述查询的枢轴：

var languageTranslation = await _genericlanguageTranslationService.GetAllLanguageTranslation();
            var query = languageTranslation.GroupBy(c => c.ResourceName)
                .Select((g, i) => new
                {
                    ResourceName = g.Key,
                    RowNumber = i + 1,
                        ENText = g.Where(c => c.LanguageId == "ENText").Max(c => c.Translation),
                        FRText = g.Where(c => c.LanguageId == "FRText").Max(c => c.Translation),
                        ZHText = g.Where(c => c.LanguageId == "ZHText").Max(c => c.Translation),
                        DEText = g.Where(c => c.LanguageId == "DEText").Max(c => c.Translation),
                        ITText = g.Where(c => c.LanguageId == "ITText").Max(c => c.Translation),
                        JAText = g.Where(c => c.LanguageId == "JAText").Max(c => c.Translation),
                        PTText = g.Where(c => c.LanguageId == "PTText").Max(c => c.Translation),
                        PT_BRText = g.Where(c => c.LanguageId == "PT-BRText").Max(c => c.Translation),
                        RUText = g.Where(c => c.LanguageId == "RUText").Max(c => c.Translation),
                        ESText = g.Where(c => c.LanguageId == "ESText").Max(c => c.Translation),
                        SVText = g.Where(c => c.LanguageId == "SVText").Max(c => c.Translation)
                    })
                    .AsEnumerable()
                    .OrderBy(x => x.ResourceName);

，但我还需要找到一种脱离的方法。