答案是:Window Functions,EF不支持。所以只需编写 SQL 并通过 Dapper 运行它
SELECT
s.Id,
s.SenderId,
s.ReceiveId,
s.Content,
s.CreatedAt
FROM
(
SELECT
m.Id,
m.SenderId,
m.ReceiveId,
m.Content,
m.CreatedAt,
ROW_NUMBER() OVER (PARTITION BY m.ReceiveId ORDER BY m.CreatedAt DESC) AS RN
FROM Messages m
) s
WHERE s.RN = 1
其他解决方案只是解决方法。这是我第三次求婚linq2db.EntityFrameworkCore https://github.com/linq2db/linq2db.EntityFrameworkCore如果您需要 LINQ 来完成此类任务
var query = from m in Messages
select new
{
m.Id,
m.SenderId,
m.ReceiveId,
m.Content,
m.CreatedAt,
RN = Sql.Ext.RowNumber().Over().PartitionBy(m.ReceiveId).OrderByDesc(m.CreatedAt).ToValue()
}
var messageQuery =
from m in query
where m.RN == 1
select new
{
m.Id,
m.SenderId,
m.ReceiverId,
m.Content,
m.CreatedAt,
}
// switch to alternative LINQ Translator
messageQuery = messageQuery.ToLinqToDB();