SQL: SELECT id，MAX(y.DateCreated) FROM x LEFT JOIN y in Linq？
SQL: SELECT id，MAX(y.DateCreated) FROM x LEFT JOIN y in Linq？
提问于 2018-10-16 06:13:04
我似乎无法将此SQL重写为EF Linq：
SELECT Conversation.Id
FROM Conversation LEFT JOIN Message
ON Conversation.Id = Message.ConversationId
GROUP BY Conversation.Id
ORDER BY MAX(Message.DateCreated) DESC我认为像这样的东西是可行的：
_dbContext.Conversation
.OrderByDescending(c => c.Messages.DefaultIfEmpty().Max(m => m.DateCreated))
.Select(cm => cm.Id);但是这给出了错误System.InvalidOperationException : Sequence contains no elements.
还有这一点：
_dbContext.Conversation
.Select(c => new {c.Id, MaxDate = c.Messages.DefaultIfEmpty().Max(m => m.DateCreated)})
.OrderByDescending(c => c.MaxDate)
.Select(cm => cm.Id);但这给了我System.ArgumentException : At least one object must implement IComparable.。
执行此操作的正确方法是什么？
回答 3
Stack Overflow用户
回答已采纳
发布于 2018-10-17 06:38:28
我相信这将生成您正在寻找的SQL (加上或减去嵌套子查询)：
var ans = from c in Conversation
join m in Message on c.Id equals m.ConversationId into mj
from m in mj.DefaultIfEmpty()
group m by c.Id into mg
orderby mg.Max(m => m.DateCreated) descending
select mg.Key;但是，在LINQ中，您可以使用group join来代替常规join。这也应该做您想做的事情，但是在SQL中使用子选择，所以我不确定哪一个在服务器上更有效。常见的智慧似乎是连接更好，但按group by进行操作可能会使其变得毫无意义，就像现代的优化器一样。我找到的另一篇文章说，子查询(CIS)效率更高，所以这可能更好。
var ans2 = from c in Conversation
join m in Message on c.Id equals m.ConversationId into mj
orderby mj.Max(m => m.DateCreated) descending
select c.Id;Stack Overflow用户
发布于 2018-10-16 07:35:36
你能试试这个吗：
from r in Conversation
join ru in Message
on r.Id equals ru.ConversationId into ps
from ru in ps.DefaultIfEmpty()
group ru by new { ru.ConversationId, ru.DateCreated } into rug
select new {
id = ru.ConversationId,
datecreated = rug.Max(ru => ru.datecreated)
}).OrderByDescending(x => x.datecreated);这可能无法编译，因为我没有测试它的代码(就像小提琴一样)
Stack Overflow用户
发布于 2018-10-16 07:36:25
您已经很接近了，只需放弃DefaultIfEmpty即可
_dbContext.Conversation.Select(con => new
{
con.Id,
MaxDateCreated = (DateTime?) con.Messages.Max(msg => msg.DateCreated)
})
.OrderByDescending(con => con.MaxDateCreated)
.ToArray()下面是将要生成的内容
SELECT
[Project2].[C1] AS [C1],
[Project2].[Id] AS [Id],
[Project2].[C2] AS [C2]
FROM ( SELECT
[Project1].[Id] AS [Id],
1 AS [C1],
CAST( [Project1].[C1] AS datetime2) AS [C2]
FROM ( SELECT
[Extent1].[Id] AS [Id],
(SELECT
MAX([Extent2].[DateCreated]) AS [A1]
FROM [dbo].[Message] AS [Extent2]
WHERE [Extent1].[Id] = [Extent2].[ConversationId]) AS [C1]
FROM [dbo].[Conversation] AS [Extent1]
) AS [Project1]
) AS [Project2]
ORDER BY [Project2].[C2] DESC页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/52825428
复制相关文章
相似问题