vb.net Linq-to-Entities：LEFT OUTER JOIN 与 WHERE 子句和投影
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3959969/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Linq-to-Entities: LEFT OUTER JOIN with WHERE clause and projection
提问by AJ.
I'm having a heckuva time figuring out how to translate a simple SQL LEFT OUTER JOIN with a two condition where clause into a working Linq-to-Entities query. There are only two tables. I need values for all rows from Table1, regardless of matches in Table2, but the WHERE clause uses fields from Table2. In SQL, the two parameters would be Table2WhereColumn1 and Table2WhereColumn2, and the query (which works) looks like this:
我正在忙着弄清楚如何将带有两个条件 where 子句的简单 SQL LEFT OUTER JOIN 转换为有效的 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
I've tried using Group Joinwith DefaultIfEmpty(), as well as an implicit join (without the actual Joinkeyword), and I only get rows for items that have values in Table2. I'm sure this won't help, but here's an example of the Linq I've been trying that doesn't work:
我已经尝试使用Group JoinwithDefaultIfEmpty()以及隐式连接（没有实际的Join关键字），并且我只获取在 Table2 中有值的项目的行。我确定这无济于事，但这是我一直在尝试但不起作用的 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
回答by Enigmativity
Have a go at these queries and tell me if they work for you. I haven't set up the data to test, but they should be fine.
试试这些问题，告诉我它们是否适合你。我还没有设置要测试的数据，但它们应该没问题。
Please excuse my mix of C# & VB.NET. I used to be a VB.NET developer, but in the last couple of years I've mostly worked in C#, so I now feel more comfortable there.
请原谅我混合使用 C# 和 VB.NET。我曾经是一名 VB.NET 开发人员，但在过去的几年里，我主要使用 C#，所以我现在在那里感觉更舒服。
Here are the classes I created for Table1& Table2:
这是我为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; }
}
Now the query in C# should be:
现在 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,
};
And the translation into VB.NET:
并翻译成 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) _
}
Let me know if they work. Fingers crossed. :-)
让我知道它们是否有效。手指交叉。:-)
回答by James S
Personally if there are where conditions for the right hand side of a left join I generally prefer to put them into the join criteria
就个人而言，如果左连接的右侧有 where 条件，我通常更喜欢将它们放入连接标准中
In this case the SQL would look like:
在这种情况下，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
The LINQ code for this (in C#) would look like:
用于此的 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,
};
not tested it - but should work
没有测试过 - 但应该可以工作
回答by Kristopher
I won't take credit for this answer but it's gorgeous: LINQ to SQL - Left Outer Join with multiple join conditions
我不会相信这个答案，但它很漂亮：LINQ to SQL - Left Outer Join with multiple join conditions
Essentially, use extension method where clause on the subquery but you must use it before DefaultIfEmpty():
本质上，在子查询上使用扩展方法 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
