如何使用 linq 扩展方法执行左外连接
假设我有这样的左外连接:
from f in Foo
join b in Bar on f.Foo_Id equals b.Foo_Id into g
from result in g.DefaultIfEmpty()
select new { Foo = f, Bar = result }
我如何使用扩展方法表达相同的任务? 例如
Foo.GroupJoin(Bar, f => f.Foo_Id, b => b.Foo_Id, (f,b) => ???)
.Select(???)
Assuming I have a left outer join as such:
from f in Foo
join b in Bar on f.Foo_Id equals b.Foo_Id into g
from result in g.DefaultIfEmpty()
select new { Foo = f, Bar = result }
How would I express the same task using extension methods? E.g.
Foo.GroupJoin(Bar, f => f.Foo_Id, b => b.Foo_Id, (f,b) => ???)
.Select(???)
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(10)
对于表
Bar
与表Foo
的(左外)连接,在Foo.Foo_Id = Bar.Foo_Id
上采用 lambda 表示法:For a (left outer) join of a table
Bar
with a tableFoo
onFoo.Foo_Id = Bar.Foo_Id
in lambda notation:由于这似乎是使用方法(扩展)语法的左外连接的事实上的SO问题,我想我会为当前选择的答案添加一个替代方案(至少根据我的经验),这是我更常见的答案要
使用简单的数据集显示差异(假设我们要连接值本身):
选项 2 符合典型的左外连接定义,但正如我之前提到的,根据数据集,通常会变得不必要的复杂。
Since this seems to be the de facto SO question for left outer joins using the method (extension) syntax, I thought I would add an alternative to the currently selected answer that (in my experience at least) has been more commonly what I'm after
To display the difference using a simple data set (assuming we're joining on the values themselves):
Option 2 is true to the typical left outer join definition, but as I mentioned earlier is often unnecessarily complex depending on the data set.
Group Join方法不需要实现两个数据集的连接。
内部联接:
对于左联接,只需添加 DefaultIfEmpty()
EF 和 LINQ to SQL 即可正确转换为 SQL。
对于 LINQ to Objects,最好使用 GroupJoin 进行加入,因为它内部使用 Lookup。 但是,如果您正在查询数据库,那么跳过 GroupJoin 据我所知是高性能的。
对我来说,Personlay 与 GroupJoin().SelectMany() 相比更具可读性
Group Join method is unnecessary to achieve joining of two data sets.
Inner Join:
For Left Join just add DefaultIfEmpty()
EF and LINQ to SQL correctly transform to SQL.
For LINQ to Objects it is beter to join using GroupJoin as it internally uses Lookup. But if you are querying DB then skipping of GroupJoin is AFAIK as performant.
Personlay for me this way is more readable compared to GroupJoin().SelectMany()
您可以创建扩展方法,例如:
You can create extension method like:
改进 Ocelot20 的答案,如果你有一个表,你只需要 0 或 1 行,但它可能有多个,你需要对你的连接表进行排序:
否则你会得到哪一行join 将是随机的(或者更具体地说,无论数据库首先找到哪个)。
Improving on Ocelot20's answer, if you have a table you're left outer joining with where you just want 0 or 1 rows out of it, but it could have multiple, you need to Order your joined table:
Otherwise which row you get in the join is going to be random (or more specifically, whichever the db happens to find first).
虽然接受的答案有效并且对 Linq to Objects 有好处,但令我烦恼的是 SQL 查询不仅仅是直接的左外连接。
以下代码依赖于 LinqKit 项目,它允许您传递表达式并将其调用到查询中。
可以如下使用
Whilst the accepted answer works and is good for Linq to Objects it bugged me that the SQL query isn't just a straight Left Outer Join.
The following code relies on the LinqKit Project that allows you to pass expressions and invoke them to your query.
It can be used as follows
我已将这个问题添加为书签,并且每年左右都需要参考它。 每次我重新审视这个,我发现我已经忘记了它是如何工作的。 这是对正在发生的事情的更详细解释。
GroupJoin
就像GroupBy
和Join
的混合。 GroupJoin 基本上通过连接键对外部集合进行分组,然后通过连接键将分组连接到内部集合。 假设我们有客户和订单。 如果您对相应 ID 进行GroupJoin
,则结果是{Customer, IGrouping}
的可枚举值。GroupJoin
有用的原因是,即使外部集合不包含匹配对象,所有内部对象也会被表示。 对于没有订单的客户,IGrouping
就是空的。 一旦我们有{ Customer, IGrouping; }
,我们可以按原样使用,过滤掉没有顺序的结果,或者使用SelectMany
进行扁平化以获得像传统 LINQJoin
一样的结果。如果有人想逐步使用调试器并了解其工作原理,这里有一个完整的示例:
I have this question bookmarked and need to reference it every year or so. Each time I revisit this, I find I have forgotten how it works. Here's a more detailed explanation of what's happening.
GroupJoin
is like a mix ofGroupBy
andJoin
.GroupJoin
basically groups the outer collection by the join key, then joins the groupings to the inner collection on the join key. Suppose we have customers and orders. If youGroupJoin
on the respective IDs, the result is an enumerable of{Customer, IGrouping<int, Order>}
. The reasonGroupJoin
is useful is because all inner objects are represented even if the outer collection contains no matching objects. For customers with no orders, theIGrouping<int, Order>
is simply empty. Once we have{ Customer, IGrouping<int, Order> }
, we can use as-is, filter out results that have no orders, or flatten withSelectMany
to get results like a traditional LINQJoin
.Here's a full example if anyone wants to step through with the debugger and see how this works:
将 Marc Gravell 的答案转变为扩展方法,我做了以下内容。
Turning Marc Gravell's answer into an extension method, I made the following.
Marc Gravell 的答案变成了支持
IQueryable
接口的扩展方法,在 这个答案 并添加了对 C# 8.0 NRT 的支持,内容如下:Marc Gravell's answer turn into an extension method that support the
IQueryable<T>
interface is given in this answer and with added support for C# 8.0 NRT reads as follows:对我来说更简单。
It's more simplified for me.