这是需要转换为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)
};
最后一个返回结果的查询出现了错误: “无法创建类型为'Anonymous type'的常量值。在此上下文中,仅支持基元类型或枚举类型。” 有没有更好的方法可以将所有不同的LINQ查询组合在一起?