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

问题描述

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

I need to do a left join on vpn下载 multiple conditions where the conditions are ORs rather 免费vpn下载 than ANDs. I've found lots of samples of the latter but am struggling to get the right answer for my 免费vpn vpn下载 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, 免费vpn 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 免费vpn coming up blank on this!

为了提供更多信息，查询的目的是获取包含来自a"的所有字段以及b"中匹配记录的计数的投影.我已经修改了上面的示例以尝试说明我所追求的.当我使用 Jon Skeet 指出的方法运行上述方法时，我正在获取 a vpn free 中所有记录的计数，而不是 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 免费vpn and illustrate what I'm after. 免费vpn下载 When I run with the above using the approach Jon Skeet vpn下载 has noted I'm getting a count of all records from a, not 免费vpn the count of the related records vpn free in b.

基本的左连接工作正常:

The vpn下载 basic left join works fine:

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

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

If I revise it to add the grouping as vpn下载 below

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

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

I'm getting 免费vpn下载 the count vpn下载 of vpn下载 the records returned from a - vpn下载 免费vpn free vpn not the count of records in matching in b.

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

I'll give the answer to Jon - I've solved my count issue - I hadn't realized I could use free vpn 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. free vpn This gives 免费vpn下载 免费vpn 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 vpn free of writing 免费vpn it to simulate the following SQL I'd appreciate it!

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

推荐答案

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

LINQ only directly free vpn 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 免费vpn下载 b in tableb
where a.col1 == b.col1 || vpn下载 a.col2 == b.col2
select ...

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

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

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