Fluent LINQ query with multiple join conditions, one of which being a simple equals and another a less than comparison between ids

Question

I have a SQL query which includes a left join and a group by- so far so good- my trouble arises from one of the join conditions not being a straight "equals to" and I'm lost where to go with LINQ.

I know multiple join conditions usually involves creating a couple of anonymous objects and comparing them, but when I add an "equal to" and "a greater" than into the mix, I've no idea how that applies.

Here's what I'd like the query to look like if I had invented LINQ, but I know the "and" in my join condition is invalid;

        var query =
            from csp in db.ChatSessionPersons
            join cm in db.ChatMessages on 
                csp.ChatSessionId equals cm.ChatSessionId 
                and cm.Id > csp.LastReadChatMessageId  
                        // (i know these should be the other way round, 
                        // but for readability I present them like this!)
            into j1
            from j2 in j1.DefaultIfEmpty()
            group j2 by csp.ChatSessionId into grouped
            select new {
                  ChatSessionId = grouped.Key, 
                  UnreadCount = grouped.Count(t => t.Id != null)};

Any ideas anyone?

Answer 1

You can convert the non-equality condition to a lambda Where on the group join result.

var query = from csp in db.ChatSessionPersons
            join cm in db.ChatMessages on csp.ChatSessionId equals cm.ChatSessionId into cmj
            select new {
                ChatSessionId = csp.ChatSessionId, 
                UnreadCount = cmj.Where(cm => cm.Id > csp.LastReadChatMessageId).Count()
            };

NOTE: I modified the query a bit to remove the group by which isn't needed if you are using a group join that has already grouped the matching results, and to remove the left join DefaultIfEmpty which also isn't needed when processing a group join with something like Count, unless you wanted to return an UnreadCount of 1 when there are no matches, in which case you should put DefaultIfEmpty() before Count().

Of course, you could use query comprehension in the sub-query:

var query = from csp in db.ChatSessionPersons
            join cm in db.ChatMessages on csp.ChatSessionId equals cm.ChatSessionId into cmj
            select new {
                ChatSessionId = csp.ChatSessionId, 
                UnreadCount = (from cm in cmj where cm.Id > csp.LastReadChatMessageId select cm).Count()
            };