LINQ to SQL: Left join a table with itself and compute an average

Question

I'm trying to write the following query in LINQ to SQL. The table contains a list of sessions arranged by users, and the query computes the average amount of time between consecutive sessions for each user. It uses a left join, so that users who have only one session have a NULL value.

SELECT t1.workerId, AVG(DATEDIFF(s, t1.endTime, t2.startTime)) 
FROM e_userLongSessions t1 
LEFT JOIN e_userLongSessions t2 
ON t1.workerId = t2.workerId AND t1.sessionNum = t2.sessionNum - 1
GROUP BY t1.workerId
ORDER BY t1.workerId

Based on the questions LINQ to SQL Left Outer Join and How to do joins in LINQ on multiple fields in single join, I've gotten to the following query:

from s1 in gzClasses.e_userLongSessions
join s2 in gzClasses.e_userLongSessions
    on new {w = s1.workerId, n = s1.sessionNum} equals new {w = s2.workerId, n = s2.sessionNum - 1}
    into joined
from s2 in joined.DefaultIfEmpty(null)
group new {s1, s2} by s1.workerId into g                   
select g.Average(e => e.s2 == null ? (double?) null : (e.s2.startTime - e.s1.endTime).TotalSeconds);

I'm getting a Unsupported overload used for query operator 'DefaultIfEmpty' message. Any suggestions?

score 2 · Accepted Answer · edited Mar 24 '14 at 03:56

2

Your LINQ query is not structured the same way the T-SQL query is. Specifically, you are only including s in the grouping. In fact, s is named in a misleading way. It should be s2. Include both:

from s1 in gzClasses.e_userLongSessions                    
join s2 in gzClasses.e_userLongSessions
    on new {w = s1.workerId, n = s1.sessionNum} 
equals new {w = s2.workerId, n = s2.sessionNum - 1}
    into joined                    
from s2Null in joined.DefaultIfEmpty()                    
group new {s1, s2Null} by s1.workerId into g                    
orderby g.Key // workerId
select g.Average(e => (e.s2Null.startTime - e.s1.endTime).TotalSeconds);

Now, you have data from both tables available in the aggregate. I don't think both of your queries are taking into account that s2.startTime can be null. But that is a bug that is not the point of the question.

edited Mar 24 '14 at 03:56

Andrew Mao

35,740
23
143
224

answered Mar 21 '14 at 22:32

usr

168,620
35
240
369

That lambda expression has no idea what `s1` and `s2` are. – Andrew Mao Mar 21 '14 at 22:47
It's `e.s1/s2`. That code was quickly typed down and not tested. You'll get it to work. – usr Mar 21 '14 at 23:00
I'm now getting a different error...see edits to question. Sorry, C# noob here. I don't understand what the first `s2` and second `s2` are representing. – Andrew Mao Mar 22 '14 at 00:00
`Unsupported overload used for query operator 'DefaultIfEmpty'` => Don't give it the `null` because LINQ can't deal with it. `null` is implicit if you don't give it an argument. Apart from that, the query looks good to me. If there's still an issue, it should be a small one. Fundamentally, this approach will work. – usr Mar 22 '14 at 10:18

LINQ to SQL: Left join a table with itself and compute an average

1 Answers1