类似 Lambda 查询"ROW_NUMBER OVER Partition"问题

Question

我有这个Lambda

Tasks
// Join Task Assignments and Join User Assigned to the Assignment (There are multiple Assignments per Task, i need the latest assignment)
.GroupJoin(
// Ordered them as i need the latest assigned
TaskAssignments.OrderByDescending(o => o.DateAssigned)
// Join the User who it was assigned to
.Join(Users, ta => ta.UserId, u => u.Id, (ta, u) => new { ta.TaskId, Date = ta.DateAssigned, Name = $"{u.GivenNames} {u.Surname}", u.Email, u.Phone })
, a => a.Id, a => a.TaskId, (t, a) => new { Task = t, Assignmnets = a })
.SelectMany(o => o.Assignmnets.DefaultIfEmpty(), (l, r) => new { l.Task, Assignment = r })

生成如下:

SELECT [t0].[Id], [t0].[EncodedId], [t0].[OrgId], [t0].[ClientId], [t0].[ContactId], [t0].[PriorityId], [t0].[LocationId], [t0].[AssetId], [t0].[JobNumber], [t0].[Name], [t0].[Description], [t0].[ContactName], [t0].[ContactPhone], [t0].[Status], [t0].[GpsLongitude], [t0].[GpsLatitude], [t0].[RefCode], [t0].[CustOn], [t0].[TaskType], [t0].[LinkProcessed], [t0].[LinkProcessedDate], [t0].[ReadTask], [t0].[ReadDate], [t0].[RequestedDate], [t0].[DueDate], [t0].[CompletedDate], [t0].[LastUpdatedDate], [t0].[SubStatusId], [t0].[SubStatus], [t0].[QuoteEstimatorId], [t0].[QuoteTotalInc], [t0].[QuoteTotalTax], [t0].[QuoteTotalEx], [t0].[TotalMaterial], [t0].[TotalLabour], [t0].[TotalExpense], [t0].[TotalHours], [t0].[Deleted], [t0].[Inserted], [t0].[Updated], [t3].[test], [t3].[TaskId], [t3].[DateAssigned] AS [Date], [t3].[GivenNames] AS [arg0], [t3].[Surname] AS [arg1], [t3].[Email], [t3].[Phone]
FROM [Tasks] AS [t0]
LEFT OUTER JOIN (
SELECT 1 AS [test], [t1].[TaskId], [t1].[DateAssigned], [t2].[GivenNames], [t2].[Surname], [t2].[Email], [t2].[Phone]
FROM [TaskAssignments] AS [t1]
INNER JOIN [Users] AS [t2] ON [t1].[UserId] = [t2].[Id]
) AS [t3] ON [t0].[Id] = [t3].[TaskId]
ORDER BY [t3].[DateAssigned] DESC

我得到的麻烦是，每个任务可以有多个任务，我只需要最近我知道在SQL我可以这样做:

SELECT [t0].[Id], [t0].[EncodedId], [t0].[OrgId], [t0].[ClientId], [t0].[ContactId], [t0].[PriorityId], [t0].[LocationId], [t0].[AssetId], [t0].[JobNumber], [t0].[Name], [t0].[Description], [t0].[ContactName], [t0].[ContactPhone], [t0].[Status], [t0].[GpsLongitude], [t0].[GpsLatitude], [t0].[RefCode], [t0].[CustOn], [t0].[TaskType], [t0].[LinkProcessed], [t0].[LinkProcessedDate], [t0].[ReadTask], [t0].[ReadDate], [t0].[RequestedDate], [t0].[DueDate], [t0].[CompletedDate], [t0].[LastUpdatedDate], [t0].[SubStatusId], [t0].[SubStatus], [t0].[QuoteEstimatorId], [t0].[QuoteTotalInc], [t0].[QuoteTotalTax], [t0].[QuoteTotalEx], [t0].[TotalMaterial], [t0].[TotalLabour], [t0].[TotalExpense], [t0].[TotalHours], [t0].[Deleted], [t0].[Inserted], [t0].[Updated], [t3].[test], [t3].[TaskId], [t3].[DateAssigned] AS [Date], [t3].[GivenNames] AS [arg0], [t3].[Surname] AS [arg1], [t3].[Email], [t3].[Phone]
FROM [Tasks] AS [t0]
LEFT OUTER JOIN (
SELECT 1 AS [test], [t1].[TaskId], [t1].[DateAssigned], [t2].[GivenNames], [t2].[Surname], [t2].[Email], [t2].[Phone],
ROW_NUMBER() OVER (PARTITION BY [TaskId] ORDER BY [DateAssigned]) AS RN
FROM [TaskAssignments] AS [t1]
INNER JOIN [Users] AS [t2] ON [t1].[UserId] = [t2].[Id]
) AS [t3] ON [t0].[Id] = [t3].[TaskId]  AND [t3].[RN] = 1
ORDER BY [t3].[DateAssigned] DESC

我正在努力在Lambda中获得相同的结果。

I have try:

Tasks
// Join Task Assignments and Join User Assigned to the Assignment (There are multiple  Assignments per Task, i need the latest assignment)
.GroupJoin(
// Ordered them as i need the latest assigned
TaskAssignments.OrderByDescending(o => o.DateAssigned)
// Join the User who it was assigned to
.Join(Users, ta => ta.UserId, u => u.Id, (ta, u) => new { ta.TaskId, Date = ta.DateAssigned, Name = $"{u.GivenNames} {u.Surname}", u.Email, u.Phone })
, a => a.Id, a => a.TaskId, (t, a) => new { Task = t, Assignmnets = a })
.SelectMany((o, i) => o.Assignmnets.DefaultIfEmpty(), (l, r, i) => new { l.Task, Assignment = r, Index = i })

但是我得到这个错误CS0411 The type arguments for method 'Queryable.SelectMany<TSource, TCollection, TResult>(IQueryable<TSource>, Expression<Func<TSource, IEnumerable<TCollection>>>, Expression<Func<TSource, TCollection, TResult>>)' cannot be inferred from the usage. Try specifying the type arguments explicitly.

Answer 1

在@ kingkong的评论和尝试了这么多不同的变化之后，我只是无法让它工作，所以我删除了.GroupJoin，并在最后的.Select中分别查询了作业:

.Select(o => new TaskGridLine
{
// Other Properties Removed.
Assignments = _context.TaskAssignments
.OrderByDescending(o => o.DateAssigned)
.Join(_context.Users, ta => ta.UserId, u => u.Id, (ta, u) => new TaskAssignmentGridLine
{
TaskId = ta.TaskId,
Date = ta.DateAssigned,
Name = $"{u.GivenNames} {u.Surname}",
Email = u.Email,
Phone = u.Phone
})
.Where(x => x.TaskId == o.Task.Id)
.ToList()
})

性能似乎有点慢，但这是我需要的结果，可以忍受0.5秒的等待时间:p