代码之家 › 专栏 › 技术社区 › AJ.

Linq to Entities:左外连接WHERE子句和投影

left-join linq-to-entities linq vb.net

AJ. · 技术社区 · 14 年前

我花了很长时间来研究如何将一个简单的SQL LEFT外部连接(带有两个条件where子句)转换为一个工作的Linq to Entities查询。只有两张桌子。我需要Table1中所有行的值,不管Table2中是否匹配,但是WHERE子句使用Table2中的字段。在SQL中,这两个参数是Table2WhereColumn1和Table2WhereColumn2,查询(可以工作)如下所示:

SELECT t1.Table1Id,
    t1.FieldDescription, 
    t2.FieldValue
FROM Table1 t1 WITH (NOLOCK)
LEFT JOIN Table2 t2 WITH (NOLOCK) ON t1.Table1Id = t2.Table1Id
WHERE (t2.Table2WhereColumn1 = @someId OR t2.Table2WhereColumn1 IS NULL)
AND (t2.Table2WhereColumn2 = @someOtherId OR t2.Table2WhereColumn2 IS NULL)
ORDER BY t1.OrderByColumn

我试过用 Group Join 具有 DefaultIfEmpty() ,以及隐式联接(没有实际的 Join 关键字),我只为表2中有值的项获取行。我相信这不会有帮助,但这里有一个我一直在尝试的Linq不起作用的例子:

Public Shared Function GetProfilePreferencesForCedent(ByVal dc As EntityContext, _
                                                      ByVal where1 As Int32, _
                                                      ByVal where2 As Int32) _
                                                  As IQueryable(Of ProjectedEntity)
    Return From t1 In dc.Table1
           Group Join t2 In dc.Table2 _
                On t1.Table1Id Equals t2.Table1Id _
                Into t2g1 = Group _
           From t2gx In t2g1.DefaultIfEmpty(Nothing)
           Where (t2gx.Table2Where1 = where1 Or t2gx.Table2Where1 = Nothing) _
                And (t2gx.Table2Where2 = where2 Or t2gx.Table2Where2 = Nothing)
           Order By t1.SortOrder
           Select New ProjectedEntity With {
               .Table1Id = t1.Table1Id, _
               .FieldDescription = t1.FieldDescription, _
               .FieldValue = If(t2gx Is Nothing, String.Empty, t2gx.FieldValue) _
           }
End Function

3 回复 | 直到 11 年前

KyleMit Steven Vachon 11 年前

去看看这些问题,告诉我它们是否适合你。我还没有设置数据来测试,但应该没问题。

请原谅我混用C#&VB.NET。我以前是一名VB.NET开发人员,但在过去的几年里,我主要是在C#工作,所以现在在那里我感觉更舒服了。

以下是我为之创建的类 Table1 &安培; Table2 :

public class Table1
{
    public int Table1Id { get; set; }
    public string FieldDescription { get; set; }
    public int OrderByColumn { get; set; }
}
public class Table2
{
    public int Table1Id { get; set; }
    public string FieldValue { get; set; }
    public int Table2WhereColumn1 { get; set; }
    public int Table2WhereColumn2 { get; set; }
}

现在C中的查询应该是:

var query =
    from t1 in Table1
    join t2 in Table2 on t1.Table1Id equals t2.Table1Id into _Table2
    from _t2 in _Table2.DefaultIfEmpty()
    where _t2 == null ? true :
        _t2.Table2WhereColumn1 == @someId
        && _t2.Table2WhereColumn2 == @someOtherId
    orderby t1.OrderByColumn
    select new
    {
        t1.Table1Id,
        t1.FieldDescription,
        FieldValue = _t2 == null ? "" : _t2.FieldValue,
    };

翻译成VB.NET:

Dim query = _
    From t1 In Table1 _
    Group Join t2 In Table2 On t1.Table1Id Equals t2.Table1Id Into _Table2 = Group _
    From _t2 In _Table2.DefaultIfEmpty() _
    Where If(_t2 Is Nothing, True, _t2.Table2WhereColumn1 = someId AndAlso  _
                                   _t2.Table2WhereColumn2 = someOtherId) _
    Order By t1.OrderByColumn _
    Select New With { _
            .Table1Id = t1.Table1Id, _
            .FieldDescription = t1.FieldDescription, _
            .FieldValue = If(_t2 Is Nothing, "", _t2.FieldValue) _
        }

KyleMit Steven Vachon 11 年前

就个人而言,如果左连接的右侧有条件,我通常更愿意将它们放入连接条件中

在这种情况下,SQL将如下所示:

SELECT t1.Table1Id,
       t1.FieldDescription, 
       t2.FieldValue
FROM Table1 t1 WITH (NOLOCK)
LEFT JOIN Table2 t2 WITH (NOLOCK) ON t1.Table1Id = t2.Table1Id 
                                  AND t2.Table2WhereColumn1 = @someId
                                  AND t2.Table2WhereColumn2 = @someOtherId
ORDER BY t1.OrderByColumn

这个的LINQ代码(在C中)看起来像:

var query =
    from t1 in Table1
    join t2 in Table2 on new{a = t1.Table1Id, b = someId, c = someotherId} 
                         equals new {a = t2.Table1Id b = t2.Table2WhereColumn1, c = Table2WhereColumn2} 
    into _Table2
    from _t2 in _Table2.DefaultIfEmpty()
    orderby t1.OrderByColumn
    select new
    {
        t1.Table1Id,
        t1.FieldDescription,
        FieldValue = _t2 == null ? "" : _t2.FieldValue,
    };

Kristopher 7 年前

我不相信这个答案,但它很漂亮: LINQ to SQL - Left Outer Join with multiple join conditions

实际上,在子查询上使用扩展方法where子句,但必须在 DefaultIfEmpty()

from p in context.Periods
join f in context.Facts on p.id equals f.periodid into fg
from fgi in fg.Where(f => f.otherid == 17).DefaultIfEmpty()
where p.companyid == 100
select f.value