如何使用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();