Linq - 在多个 (OR) 条件下进行左连接

问题描述

我需要对多个条件进行左连接，其中条件是 ORs 而不是 ANDs.我发现了很多后者的样本，但我正在努力为我的场景找到正确的答案.

I need to do a left join on multiple conditions where the conditions are ORs rather than ANDs. I've found lots of samples of the latter but am struggling to get the right answer for my scenario.

from a in tablea
join b in tableb on new { a.col1, a.col2 } equals new { b.col1, b.col2 }
group a by a into g
select new () { col1 = a.col1, col2 = a.col2, count = g.Count() }

适用于所有条件都必须匹配的连接.我需要让连接匹配 on a.col1 = b.col1 OR a.col2 = b.col2.

works great for joins where all conditions must match. I need to get the join to match on a.col1 = b.col1 OR a.col2 = b.col2.

我知道这一定很容易，但我对此一无所知！

I know it must be easy but I've coming up blank on this!

为了提供更多信息，查询的目的是获取包含来自a"的所有字段以及b"中匹配记录的计数的投影.我已经修改了上面的示例以尝试说明我所追求的.当我使用 Jon Skeet 指出的方法运行上述方法时，我正在获取 a 中所有记录的计数，而不是 b 中相关记录的计数.

To give a little more info, the purpose of the query is to get a projection containing all of the fields from 'a' plus a count of the matching records in 'b'. I've amended the sample above to try and illustrate what I'm after. When I run with the above using the approach Jon Skeet has noted I'm getting a count of all records from a, not the count of the related records in b.

基本的左连接工作正常:

The basic left join works fine:

from a in tablea
from b in tableb
.Where( b => ( a.col1 == b.col1 || a.col2 == b.col2))
.DefaultIfEmpty()
select new { col1 = a.col1, col2 = a.col2 }

如果我修改它添加如下分组

If I revise it to add the grouping as below

from a in tablea
from b in tableb
.Where( b => ( a.col1 == b.col1 || a.col2 == b.col2))
.DefaultIfEmpty()
group a by a.col1 into g
select new { col1 = g.Key, count = g.Count() }

我得到的是从 a 返回的记录数 - 而不是 b 中匹配的记录数.

I'm getting the count of the records returned from a - not the count of records in matching in b.

我会回答 Jon - 我已经解决了我的计数问题 - 我没有意识到我可以使用 lamda 来过滤计数 (g.Count(x => x != null)).另外，我需要按 a 对 b 分组，而不是像上面那样按 a 分组.这给出了正确的结果，但 SQL 不如我手工编写的高效，因为它添加了相关的子查询 - 如果有人能建议更好的编写方式来模拟以下 SQL，我将不胜感激！

I'll give the answer to Jon - I've solved my count issue - I hadn't realized I could use a lamda to filter the count (g.Count(x => x != null)). Plus I need to group b by a rather than a by a as I had above. This gives the correct result but the SQL is not as efficient as I'd write it by hand as it adds a correlated sub query - if anyone can advise a better way of writing it to simulate the following SQL I'd appreciate it!

select a.col1, count(b.col1)
from tablea a
left join tableb b
on a.col1 = b.col1
or a.col2 = b.col2
group by a.col1

推荐答案

LINQ 仅直接支持 equijoins.如果你想做任何其他类型的连接，你基本上需要一个交叉连接和 where:

LINQ only directly supports equijoins. If you want to do any other kind of join, you basically need a cross-join and where:

from a in tablea
from b in tableb
where a.col1 == b.col1 || a.col2 == b.col2
select ...

可能值得检查生成的 SQL 是什么样的以及查询计划是什么样的.可能有更有效的方法，但这可能是最简单的方法.

It's probably worth checking what the generated SQL looks like and what the query plan is like. There may be more efficient ways of doing it, but this is probably the simplest approach.

这篇关于Linq - 在多个 (OR) 条件下进行左连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！