LINQ: self-binding request, how to do it?

Question

Can anyone help?

I have 1 class, basically it contains members and inside this class is List.

The members that I have on the list ... So basically this happens as if

I have 2 members and each member has several sessions.

I only want to return each member with 1 session.

I fulfilled the LINQ query, but of course it does not work ...

I think I need to make an independent connection, any ideas?

Mostly my mistake: m does not exist in my self-join subquery.

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 would be grateful for any feedback that anyone has.

Thanks in advance.

EDIT

Ok, I managed to do it as follows, 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} 

This sn method contains 1 entry, but I have access to all properties ...

But is this the best way to do this with LET?

Thanks.

Answer 1

If I am missing something you need, no?

 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 about the point of the object, not the point of SQL implementation. What I need? I need all the participants, each with their last time to end the session, and then act on this.

EDIT: The let option you used is fine, just keep something in mind. FirstOrDefault will return null if the member has an empty session list, and then sn.SessionEndTime removes the null reference. If, on the other hand, you are sure that each member has at least one session instead of First or an aggregate. Also, do not use FirstOrDefault() in let, it is somehow a mess up LINQ and does not allow you to bind it to the master (by calling a separate SQL query for each master to detect missing subsets), therefore useful 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 aggregation vs Max, 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 decreasing SessioEndTime index, the order of the script is about two times slower (you can get execution plans for them to check for yourself), without an index, it's about 5 times slower.