我有以下实体,基本上User
和Message
通过MsgGroup
有M2M关系。线程(或组)中的第一条消息也具有ThreadId=MessageId
,其中其他人共享相同的ThreadId
public class User
{
public int UserId { get; set; }
[System.ComponentModel.DataAnnotations.Schema.InverseProperty("Received")]
public virtual System.Collections.Generic.ICollection<MsgGroup> ReceivedGroups { get; set; }
}
public class Message
{
public int MessageId { get; set; }
public int ThreadId { get; set; }
public virtual System.Collections.Generic.ICollection<MsgGroup> MsgGroups { get; set; }
}
public class MsgGroup
{
public int MsgGroupId { get; set; }
public int UserId { get; set; }
public virtual User Received { get; set; }
public int MessageId { get; set; }
public virtual Message Message { get; set; }
}
我想在组中为给定用户接收延迟消息。特别是以下 SQL 查询:
select * from messages where MessageId IN (
SELECT MaxId FROM (
select ThreadId , MAX(MessageId) as MaxId from messages where ThreadId in (
select distinct MessageId from msggroups where UserId = 1
)
GROUP BY ThreadId
) AS t1
)
我试过:
var query = from grp in db.MsgGroups.Where(g => g.UserId == userId)
select new
{
f = (from msg in db.Messages where (msg.ThreadId == grp.MessageId) select msg).OrderByDescending(m => m.Date).Take(1)
};
但是,它会创建一个非常复杂的查询,其中每个消息字段都有子查询。
有解决办法吗?我也可以以基于方法的格式和查询表达式格式提出相同的问题(因为我根本无法弄清楚查询表达式格式)
我想你可能正在寻找一个联接,也许是这样的东西,这将为你提供特定用户的最新消息,按线程 ID 排序,你可以用你想要的消息数替换 Take 部分中的 100:
var query = (from grp in db.MsgGroups
.Join(
db.Messages,
g => g.MessageId,
m => m.ThreadId,
(g, m) => new { grp = g, mgs = m })
.Where(x => x.grp.UserId == userId)
select new
{
Message = grp.mgs
})
.Take(100)
.OrderByDescending(t => t.Message.ThreadId);
这将为您提供一个匿名对象的集合,每个对象都具有一个名为 Message 的属性,即您想要的 Message 对象。
对于任何对以下查询感兴趣的人,查询完全按照我的要求做了,唯一的区别是它在我的问题中使用了 JOIN
s 而不是 IN
s:
var query =
from mm in
(
from grp in db.MsgGroups.Where(g => g.UserId == userId)
from msg in db.Messages
where msg.ThreadId == grp.MessageId
group msg by msg.ThreadId into thr
select new { t = thr.Key, m = thr.Max(t => t.MessageId) }
)
join omsg in db.Messages
on mm.m equals omsg.MessageId
select new { t = mm.t, m = omsg.Text } ;
使用导航属性并让 EF 处理 SQL 联接:
var query = from rg in db.MsgGroups
where rg.UserId == userId
group rg by rg.ThreadId into thread
select new {
Thread = thread.Key,
LastMessage = thread.Select(t => t.Message)
.OrderByDescending(m => m.Date)
.FirstOrDefault()
};
这为您提供了用户在其中包含消息的每个线程的最后一条消息。