如何使用LINQ在用户之间获取最新消息? [英] How to get the last messages among users with LINQ?
本文介绍了如何使用LINQ在用户之间获取最新消息?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个表,显示消息之间的关系.像这样:
I have a table that shows the relationship between messages. Like this:
我想从每个用户那里获得最后一条消息.因此,例如,结果将是表中的ID 91和ID 92
I want to get the last one message from each user. So, for example, result will be id 91 and id 92 from the table
var messages = await _dbContext.Messages.Include(x =>x.User).Where(x =>x.SenderId == userId | x.ReceiveId == userId).OrderByDescending(x =>x.CreatedAt).Select(x =>new Message {
Id = x.Id,
Content = x.Content,
CreatedAt = x.CreatedAt,
User = new User {
Id = x.User.Id,
UserName = x.User.UserName,
Name = x.User.Name,
LastName = x.User.LastName,
Profil = x.User.Profil
},
}).Distinct().ToListAsync();
我该怎么办?
推荐答案
答案是:窗口函数,EF不支持.因此,只需编写SQL并通过Dapper运行它
Answer is: Window Functions, which are not supported by EF. So just write SQL and run it via 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
其他解决方案只是解决方法.如果您需要LINQ来执行此任务,这是我第三次提议 linq2db.EntityFrameworkCore .>
Other solutions are just workaround. It is third time I've proposing linq2db.EntityFrameworkCore if you need LINQ for such task
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();
这篇关于如何使用LINQ在用户之间获取最新消息?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文