c# - Entity Framework multiple join types with dates, group by and count -

i have chat system consist of 3 tables: users, roomjoins (private conversation room have joined) , chats (list of chat messages timestamp).

i want list of private rooms given user, followed number of new chat messages has received since last login.

the sql query works (maybe can improved, works) is:

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

that says: give me roomids (private conversations) userid=45, userid of person sent me message, picture , number of chat messages sentwhen > user.lastseen

the result like

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

meaning userid 43 has sent 3 messages since last login, while user 37 didn't sent new

now, try translate ef , kind of got working, problem can't find way query using sentwhen > lastseen date format because doesn't allow that. , if try clauses, never correct answer. here existing try (without sentwhen > lastseen)

from in db.roomjoins.where(c => c.userid == u.id && c.active == true).orderby(c => c.roomid)                                  b in db.roomjoins.where(fp => fp.roomid == a.roomid && fp.userid != a.userid)                                  m in db.users.where(m => b.userid == m.id)                                  join p in db.chats on a.roomid equals p.roomid j1                                  j2 in j1.defaultifempty()                                  group j2 new { a.roomid, b.userid, m.firstpicurl } g                                  select new                                  {                                      roomid = g.key.roomid,                                      userid = g.key.userid,                                      firstpicurl = g.key.firstpicurl,                                      count = g.count()                                  }; 

so code seems work has 2 problems: 1) doesn't take account timestamp, want count of messages after lastseen 2) count of 1 when should 0. this

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

anyone knows how achieve i'm looking for?

first answer: 1 seems work looks complex. there way make simpler?

from in db.roomjoins.where(c => c.userid == 45 && c.active == true)                                  b in db.roomjoins.where(fp => fp.roomid == a.roomid && fp.userid != a.userid)                                  m in db.users.where(m => b.userid == m.id)                                  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                                                                      } j1                                  group j1 new { j1.roomid, j1.userid, j1.firstpicurl } 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 j1