how to accomplish LINQ self join query

Question

Class members holds members and within that class is a List<Sessions>. The members I have in a List also.

I have 2 members and each member has a number of sessions. I wish to only return each member with 1 Session.

Why does my LINQ query get an error that m doesn't exist in my subquery self join?

var sessions =  
from m in this.members                     
join s in
(
    from se in m.Sessions
    group se by se.Name into g
    select new {Name = g.Key, SessioEndTime = g.Max(a=>a.SessioEndTime)}
)   
on m.Name equals s.Name                    
select new { MemberName = m.Name, SessionTime = s.SessioEndTime}

I managed to do it with the following, but is this the best way?

var sessions =  
from m in this.members                     
let sn = m.Sessions.OrderByDescending(a => a.SessionEndTime).FirstOrDefault()                
select new { MemberName = m.Name, SessionTime = sn.SessioEndTime}

sn contains 1 record, but I have access to all the properties.

Answer 1

You need

var sessions = 
   from m in members
   select new { 
      MemberName = m.Name, 
      SessionTime = m.Sessions.Max(s => s.SessioEndTime)
   };

You have to change the way you think about LINQ queries. Think more from an object point of view rather than from an SQL implementation point of view. "What is it that I need? I need all members, each with its latest session end time." Then act on that.

The let option you used is OK; just keep in mind that FirstOrDefault will return null if member has an empty list of Sessions, and then sn.SessionEndTime hits a null reference. If on the other hand you are certain that every member has at least one session use First instead or aggregate.

Don't use FirstOrDefault() in the let. It messes up the LINQ and prevents it from tying it to the master (causing a separate SQL query for each master to detect missing subsets). So usable queries with let are:

from m in Members                     
let sn = m.Sessions.Max(s => s.SessioEndTime)                
select new { MemberName = m.Name, SessionTime = sn};

from m in Members                     
let sn = m.Sessions.OrderByDescending(a => a.SessioEndTime).First()              
select new { MemberName = m.Name, SessionTime = sn.SessioEndTime};

As for ordering vs Max aggregation, both queries will generate a subquery:

-- MAX    
SELECT [t0].[Name] AS [MemberName], (
    SELECT MAX([t1].[SessioEndTime])
    FROM [Session] AS [t1]
    WHERE [t1].[memberId] = [t0].[id]
    ) AS [SessionTime]
FROM [Member] AS [t0]
GO

-- ordering
SELECT [t0].[Name] AS [MemberName], (
    SELECT [t2].[SessioEndTime]
    FROM (
        SELECT TOP (1) [t1].[SessioEndTime]
        FROM [Session] AS [t1]
        WHERE [t1].[memberId] = [t0].[id]
        ORDER BY [t1].[SessioEndTime] DESC
        ) AS [t2]
    ) AS [SessionTime]
FROM [Member] AS [t0]

With a descending index on SessioEndTime the ordering script is about twice slower (you can get execution plans for these to check for yourself). Without the index it's about five times slower.