实体框架多个连接类型，包括日期、分组和计数

本文关键字：日期 包括 框架 连接 类型 实体 | 更新日期: 2023-09-27 18:14:45

我有一个由3个表组成的聊天系统:Users, RoomJoins(您已加入的私人聊天室)和Chats(带时间戳的聊天消息列表)。

我想获得给定用户的所有私人房间的列表，然后是他自上次登录以来收到的新聊天消息的数量。

有效的sql查询(可能可以改进，但肯定有效)是:

select a.roomid, b.userid, m.firstpicurl, count(p.roomid) as cuenta from roomjoin a
 inner join roomjoin b
on b.roomid=a.roomid and a.userid<>b.userid
inner join myuser m on m.id=b.userid
 left join
 chat p on p.roomid=a.roomid and p.sentwhen > a.lastseen
 where a.userid=45 and a.active=1
  group by a.roomid, b.userid, m.firstpicurl

基本上说:给我userid=45的所有roomids(私人对话)，还有发送我消息的人的用户id，他的照片和在sendwhen> user.lastseen的聊天消息的数量

的结果将是类似于

roomid  userid  firstpicurl  cuenta
1        43     http://...     3
2        37     http://...     0

表示用户id 43自上次登录以来已向您发送了3条消息，而用户37没有向您发送任何新消息

现在，我试着把它翻译成EF，我有点让它工作，但问题是，我找不到一种方法来查询使用sentwhen> lastseen日期格式，因为它不允许。如果我试着用Where从句，我永远不会得到正确的答案。这是我现有的尝试(没有sentwhen> lastseen)

from a in db.RoomJoins.Where(c => c.userid == u.id && c.active == true).OrderBy(c => c.roomid)
                                 from b in db.RoomJoins.Where(fp => fp.roomid == a.roomid && fp.userid != a.userid)
                                 from m in db.Users.Where(m => b.userid == m.id)
                                 join p in db.Chats on a.roomid equals p.roomid into j1
                                 from j2 in j1.DefaultIfEmpty()
                                 group j2 by new { a.roomid, b.userid, m.firstpicurl } into g
                                 select new
                                 {
                                     roomid = g.Key.roomid,
                                     userid = g.Key.userid,
                                     firstpicurl = g.Key.firstpicurl,
                                     count = g.Count()
                                 };

所以我的代码似乎工作，但它有2个问题:1)它没有考虑到时间戳，我只是想要一个消息计数后，我最后一次看到2)当它应该是0时，我得到了一个1。所以我会得到这样的内容

roomid  userid  firstpicurl  cuenta
1        43     http://...     3
2        37     http://...     1  <-- this should be 0

有人知道如何实现我在寻找什么?

第一次回答:这个似乎可行，但看起来很复杂。有没有更简单的方法?

from a in db.RoomJoins.Where(c => c.userid == 45 && c.active == true)
                                 from b in db.RoomJoins.Where(fp => fp.roomid == a.roomid && fp.userid != a.userid)
                                 from m in db.Users.Where(m => b.userid == m.id)
                                 from p in db.Chats.Where(p => p.roomid==a.roomid && 
                                            p.sentwhen > a.lastseen).DefaultIfEmpty()
                                 select new
                                 {
                                     roomid = a.roomid,
                                     userid = b.userid,
                                     firstpicurl = m.firstpicurl,
                                     cid = p.Id                                    
                                 } into j1
                                 group j1 by new { j1.roomid, j1.userid, j1.firstpicurl } into g
                                 select new
                                 {
                                     roomid = g.Key.roomid,
                                     userid = g.Key.userid,
                                     firstpicurl = g.Key.firstpicurl,
                                     count = g.Count(e => e.cid!=null)
                                 };

实体框架多个连接类型，包括日期、分组和计数

join p in db.Chats.Where(x  => x.sentwhen > a.lastseen) 
              on a.roomid equals p.roomid into j1
相关文章：