[C#]在 LINQ 中的级联左的联接

标签: Linq SQLServer C#
发布时间: 2017/2/27 5:24:09
注意事项: 本文中文内容可能为机器翻译,如要查看英文原文请点击上面连接.

我需要将相关联的每个节点的最近行动 (如果有)。SQL 查询贝娄为我这样做︰

select n.*, o.* from Nodes n
left outer join (
  select min(s.ActionAt) as ActionAt, min(s.Ahead) as Ahead, s.NodeId
  from SuggestedActions s
  group by s.NodeId
) o1 
  on o1.NodeId=n.Id
left outer join SuggestedActions o 
  on o.NodeId=o1.NodeId and o.ActionAt=o1.ActionAt and o.Ahead=o1.Ahead

但我不能够制定使用 LINQ 的相同查询。我已经左联接选择最近的行动,我不知道如何继续加入更多的数据︰

from node in db.Nodes
join no in db.SuggestedActions on node.Id equals no.NodeId into no1
from o1 in no1.DefaultIfEmpty()
group o1 by node.Id into go1
select new {
  NodeId = go1.Key,
  ActionAt = go1.Min(c => c.ActionAt),
  Ahead = go1.Min(c => c.Ahead)
} ...

解决方法 1:

与这一试,它没有测试过,但可能会给你一个方向︰

var nodeActions = from node in db.Nodes
    join action in db.SuggestedActions on node.Id equals action.NodeId into actions
    from action in actions.DefaultIfEmpty()
    select new { node, action };

var minNodes = from nodeAction in nodeActions
    group nodeAction by nodeAction.node.Id
    into groupedActions
    select new
    {
        NodeId = groupedActions.Key,
        ActionAt = groupedActions.Min(c => c.action.ActionAt),
        Ahead = groupedActions.Min(c => c.action.Ahead)
    };

var result = from minNode in minNodes
    join nodeAction in nodeActions on new { Id = minNode.NodeId, minNode.ActionAt, minNode.Ahead }
        equals new { nodeAction.node.Id, nodeAction.action.ActionAt, nodeAction.action.Ahead } into nodeActionJ
    from action in nodeActionJ
    select action;
赞助商