LINQ:自联接查询，如何实现? [英] LINQ: Self join query, how to accomplish this?

问题描述

任何人都可以帮忙吗?

我有1个班级，基本上它拥有会员，并且该班级中有一个列表.

我在列表中的成员也...所以基本上是这样，

我有2个成员，每个成员都有多个会话.

我只希望让每个成员参加1次会议.

我已经完成了LINQ查询，但是当然不起作用...

我认为我需要进行自我加入，有什么想法吗?

基本上我的错误是我的子查询自联接中不存在m.

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}

我会很感激任何人的反馈.

谢谢.

编辑

好吧，我设法做到了以下几点，但这是最好的方法吗?

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包含1条记录，但是我可以访问所有属性...

但这是使用LET的最好方法吗?

谢谢.

解决方案

除非我缺少您需要的东西，否则吗?

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

您必须更改对LINQ查询的思考方式，从对象角度而不是从SQL实现角度考虑更多.我需要什么?我需要所有成员，每个成员都有最新的会话结束时间，然后采取行动.

您使用的let选项是可以的，只要记住一点，如果成员的会话列表为空，FirstOrDefault将返回null，然后sn.SessionEndTime达到null引用.另一方面，如果您确定每个成员至少有一个会话，请使用First代替或聚合. 同样不要在let中使用FirstOrDefault()，它会弄乱LINQ并阻止它与主服务器绑定(导致每个主服务器使用单独的SQL查询来检测丢失的子集)，因此let的可用查询是:

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};

关于排序与最大聚合，两个查询都将生成一个子查询:

-- 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]

在SessioEndTime上使用降序索引时，排序脚本的速度要慢大约两倍(您可以获取执行计划以供自己检查)，而索引的速度要慢5倍.

Can anyone help?

I have 1 class, basically it holds Members and within that class is a List.

The members i have in a List also... So basically it goes like this,

I have 2 members and each member has a number of sessions.

I wish to only return each member with 1 Session.

I have done a LINQ query, but of course it doesn't work...

I think i need to do a self join, any ideas?

Basically my error is 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 would appreciate any feedback anyone has.

Thanks in advance.

EDIT

Ok i managed to do it like 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}

This way sn contains 1 record, but i have access to all the properties...

But is this the best way to do using a LET?

Thanks.

解决方案

Unless I am missing something you need this, 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 from object point rather than from SQL implementation point. What is it that I need? I need all members, each with its latest session end time, then act on that.

EDIT: The let option you used is ok, just keep something in mind FirstOrDefault will return null if member has an empty list of Sessions, and then sn.SessionEndTime hits null reference. If on the other hand you are certain that every member has at least one session use First instead or aggregate. Also don't use FirstOrDefault() in the let, it kind of 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 its about 5times slower.

这篇关于LINQ:自联接查询，如何实现?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！