当子条件不匹配时，不包括父实体

Question

我有一个查询，其中我必须返回一些实体及其子实体。

目前，如果没有孩子，父母也不会回来。我想要的是拥有所有父母的列表，如果有孩子，则将其包括在内。

我的Linq查询当前如下：

var query = (from parent in Context.Parents
            join child in Context.Children on child.ParentId.DefaultIfEmpty() equals parent.Id
            where child.SomeProperty == SomeValue
            select new {parent, child});
var result = query.ToList().Select(e => e.parent).Distinct().ToList();

它生成以下SQL查询

SELECT 
    [Extent1].[ParentId] AS [ParentId], 
    [Join1].[ChildId] AS [ChildId], 
    [Join1].[SomeProperty] AS [SomeProperty], 
    FROM  [dbo].[Parent] AS [Extent1]
    INNER JOIN  (SELECT [Extent2].[ChildId] AS [ChildId], 
                        [Extent2].[SomeProperty] AS [SomeProperty]
        FROM   ( SELECT 1 AS X ) AS [SingleRowTable1]
        LEFT OUTER JOIN [dbo].[Child] AS [Extent2] ON 1 = 1 ) AS [Join1] ON [Extent1].[ParentId] = [Join1].[ParentId]
    WHERE [Join1].[SomeProperty] = @p__linq__0

我已经成功地创建了返回我想要的结果的SQL查询，但我仍然无法在Linq中翻译它

SELECT Distinct Parent.ParentID, fullData.ChildID 
    FROM Parent left outer join 
    (select Child.ParentId, Child.SomeProperty, Child.EmployeurSubsideID 
        from Child 
        Where Child.SomeProperty = 'SomeValue'  ) as fullData on Parent.ParentID = fullData.ParentID

Answer 1

这是一个带有附加右侧滤波器的经典LEFT OUTER JOIN。

您有两个选项（均有效）：

（A） 在加入前应用右侧过滤器：

var query = 
    from parent in Context.Parents
    join child in Context.Children
        .Where(child => child.SomeProperty == SomeValue)
    on parent.Id equals child.ParentId into children
    from child in children.DefaultIfEmpty()
    select new { parent, child };

（B） 使用组合键加入：

var query = 
    from parent in Context.Parents
    join child in Context.Children on new { K1 = parent.Id, K2 = SomeValue }
    equals new { K1 = child.ParentId, K2 = child.SomeProperty } into children
    from child in children.DefaultIfEmpty()
    select new { parent, child };

实际上，在LINQ to Entities中，您有第三个选项：

（C） 使用where而不是join（模式由查询转换器识别，生成的SQL查询仍将使用LEFT OUTER JOIN）：

var query = 
    from parent in Context.Parents
    from child in Context.Children
        .Where(child => parent.Id == child.ParentId && child.SomeProperty == SomeValue)
        .DefaultIfEmpty()
    select new { parent, child };

Answer 2

类似的东西

var query = (from parent in Context.Parents
            join child in Context.Children on child.ParentId equals parent.Id
            into ch from c in ch.DefaultIfEmpty()
            where c == null || c.SomeProperty == SomeValue
            select new {parent, c});

或

(from parent in Context.Parents
where parent.Children == null || parent.Children.Any(c=>c.SomeProperty == SomeValue)
select parent).Include("Children")

假设外键设置正确，并注意子项的Property名称可能不同。