将SQL查询转换为LINQ查询

本文关键字：查询 LINQ 转换 SQL | 更新日期: 2023-09-27 18:17:35

这是需要转换为LINQ查询的SQL查询。

SELECT  pq.DocumentQueueID, 
    pq.DocumentQueueName, 
    pq.DepartmentName, 
    pq.UserLocation,
    ISNULL(T.DocumentCount, 0) DocCount, 
    ISNULL(CONVERT(VARCHAR(50),T.OldestDocumentDate),'') IngestionDateTime,
    ISNULL(B.UserName, '') UserName
FROM [dbo].[listPLDQueues] pq 
LEFT OUTER JOIN 
(
    SELECT dds.CurrentDocumentQueue, 
    SUM(dds.ImportPageCount) as DocumentCount, 
    MIN(dds.IngestionDateTime) as OldestDocumentDate
    FROM [dbo].[dataDocumentStats] dds
    GROUP BY dds.CurrentDocumentQueue
) AS T ON T.CurrentDocumentQueue = pq.DocumentQueueID
LEFT OUTER JOIN
(   SELECT duq.DocumentQueueID, UserName = 
    STUFF((SELECT ', ' + uq.UserDisplayName
    FROM [dbo].[dataUserQueues] uq
    WHERE uq.DocumentQueueID = duq.DocumentQueueID
    FOR XML PATH('')),1,2,'')
 FROM [dbo].[dataUserQueues] duq
 GROUP BY duq.DocumentQueueID
 ) AS B ON B.DocumentQueueID = pq.DocumentQueueID 
WHERE UPPER(WorkflowType) = 'INDEXING'

我在LINQ查询中做了什么…

 var indexSummary = _eimStatsDB.listPLDQueues
          .Join(_eimStatsDB.dataDocumentStats,
              pld => pld.DocumentQueueID,
              dds => dds.CurrentDocumentQueue,
              (pld, dds) => new { pldQueues = pld, dataDocument = dds })            
          .Where(a => a.pldQueues.WorkflowType.ToLower() == "indexing")
          .GroupBy(a => a.pldQueues.DocumentQueueID)
          .ToList()
          .Select(a => new
          {
              DocumentQueueId = a.Key,
              DocumentQueueName = a.Select(i => i.pldQueues.DocumentQueueName).FirstOrDefault(),
              DepartmentName = a.Select(i => i.pldQueues.DepartmentName).FirstOrDefault(),
              DocumentCount = a.Sum(i => i.dataDocument.ImportPageCount),
              OldestDocumentDate = a.Min(i => i.dataDocument.IngestionDateTime),
              UserLocation = a.Select(i => i.pldQueues.UserLocation).FirstOrDefault(),
              IsChecked = false
          });
        var userNames = _eimStatsDB.dataUserQueues
            .GroupBy(e => e.DocumentQueueID)
            .ToList()
            .Select(e => new
            {
                DocumentId = e.Key,
                UserName = string.Join(",", e.Select(i => i.UserDisplayName))
            });
        var listPLDQueue = from pldqueue in _eimStatsDB.listPLDQueues
                           where pldqueue.WorkflowType == "Indexing"
                           select pldqueue;
        var result = from pldqueue in listPLDQueue
                     join iS in indexSummary
                     on pldqueue.DocumentQueueID equals iS.DocumentQueueId into pldjoin
                     from pld in pldjoin.DefaultIfEmpty()
                     join un in userNames
                     on pld.DocumentQueueId equals un.DocumentId into gj
                     from subuser in gj.DefaultIfEmpty()
                     select new
                     {
                         DocumentQueueId = pld.DocumentQueueId,
                         DocumentQueueName = pld.DocumentQueueName,
                         DepartmentName = pld.DepartmentName,
                         DocumentCount = (pld.DocumentCount == null ? 0 : pld.DocumentCount),
                         OldestDocumentDate = (pld.OldestDocumentDate == null? Convert.ToDateTime(string.Empty) : pld.OldestDocumentDate),
                         UserLocation = pld.UserLocation,
                         IsChecked = pld.IsChecked,
                         Usernames = (subuser == null ? string.Empty : subuser.UserName)
                     };

最后一个返回结果的查询给出了错误:"无法创建"匿名类型"类型的常数值。在此上下文中只支持基本类型或枚举类型。"

是否有其他更好的方法来实现这一点，所有不同的LINQ查询可以组合在一起?

将SQL查询转换为LINQ查询

在检查整个查询结构后，我发现除了listPLDQueue之外的其他两个join源是IEnumerable集合，具有匿名类型参数，其中实体框架在执行连接操作时只能引用具有原始类型的IEnumerable作为类型参数或IQueryable。

试着删除或注释所有的ToList()方法来为indexSummary和userNames分配IQueryable，然后考虑使用像这样的匿名类型来代替适当的类名:

var indexSummary = _eimStatsDB.listPLDQueues
          .Join(_eimStatsDB.dataDocumentStats,
              pld => pld.DocumentQueueID,
              dds => dds.CurrentDocumentQueue,
              (pld, dds) => new { pldQueues = pld, dataDocument = dds })            
          .Where(a => a.pldQueues.WorkflowType.ToLower() == "indexing")
          .GroupBy(a => a.pldQueues.DocumentQueueID)
        //.ToList() --> this converts IQueryable to IEnumerable, which should be dropped
          .Select(a => new listPLDQueues() // change this assignment to your model class name
          {
              DocumentQueueId = a.Key,
              DocumentQueueName = a.Select(i => i.pldQueues.DocumentQueueName).FirstOrDefault(),
              DepartmentName = a.Select(i => i.pldQueues.DepartmentName).FirstOrDefault(),
              DocumentCount = a.Sum(i => i.dataDocument.ImportPageCount),
              OldestDocumentDate = a.Min(i => i.dataDocument.IngestionDateTime),
              UserLocation = a.Select(i => i.pldQueues.UserLocation).FirstOrDefault(),
              IsChecked = false
          });
var userNames = _eimStatsDB.dataUserQueues
            .GroupBy(e => e.DocumentQueueID)
          //.ToList() --> this converts IQueryable to IEnumerable, which should be dropped
            .Select(e => new dataUserQueues() // change this assignment to your model class name
            {
                DocumentId = e.Key,
                UserName = string.Join(",", e.Select(i => i.UserDisplayName))
            });

每个赋值将返回IQueryable<T> (T被赋给DB模型类名，即IQueryable<listPLDQueues>和IQueryable<dataUserQueues>)，适合在包含join查询的result赋值中使用它们。

相关问题&引用:

无法创建'匿名类型'的常量值。在此上下文中只支持基本类型或枚举类型

无法创建'匿名类型'的常量值。在此上下文中只支持基本类型或枚举类型

可查询的匿名类型

相关文章：