实体框架，如何使用LinQ手动生成内部联接以进行实体化 [英] Entity Framework, how to manually produce inner joins with LinQ to entitites

问题描述

假设我有一个名为Foo的表，该表具有主键FooID和一个整数非唯一列Bar.由于某种原因，在SQL查询中，我必须多次将表Foo与自身连接在一起，如下所示:

Let's imagine I have an table called Foo with a primary key FooID and an integer non-unique column Bar. For some reason in a SQL query I have to join table Foo with itself multiple times, like this:

SELECT * FROM Foo f1 INNER JOIN Foo f2 ON f2.Bar = f1.Bar INNER JOIN Foo f3 ON f3.Bar = f1.Bar...

我必须通过LINQ to Entities实现此目的.

I have to achieve this via LINQ to Entities.

做

ObjectContext.Foos.Join(ObjectContext.Foos, a => a.Bar, b => b.Bar, (a, b) => new {a, b})

在结果查询中向我提供了LEFT OUTER JOIN，并且我需要内部联接，这非常关键.

gives me LEFT OUTER JOIN in the resulting query and I need inner joins, this is very critical.

当然，如果在edmx中我根据需要添加了Foo与自身的关联数，然后在我的代码中使用它们，那么Entity Framework将为每个关联项替换正确的内部联接，那么我可能会成功.问题是在设计时我不知道我需要多少个联接.好的，一种解决方法是添加尽可能多的...

Of course, I might succeed if in edmx I added as many associations of Foo with itself as necessary and then used them in my code, Entity Framework would substitute correct inner join for each of the associations. The problem is that at design time I don't know how many joins I will need. OK, one workaround is to add as many of them as reasonable...

但是，从理论的角度来看，如果没有别的选择，是否有可能通过EF创建内部联接而无需显式定义关联?

But, if nothing else, from theoretical point of view, is it at all possible to create inner joins via EF without explicitly defining the associations?

在LINQ to SQL中，有一种(有点奇怪)通过GroupJoin进行此操作的方法，如下所示:

In LINQ to SQL there was a (somewhat bizarre) way to do this via GroupJoin, like this:

ObjectContext.Foos.GroupJoin(ObjectContext.Foos, a => a.Bar, b => b.Bar, (a, b) => new {a, b}).SelectMany(o = > o.b.DefaultIfEmpty(), (o, b) => new {o.a, b)

我刚刚在EF中尝试过，这种方法在那儿行不通.它仍然为我生成外部联接.

I've just tried it in EF, the trick does not work there. It still generates outer joins for me.

有什么想法吗?

推荐答案

在Linq to Entities中，以下是对同一表的多个实例进行内部联接的一种方法:

In Linq to Entities, below is one way to do an inner join on mutiple instances of the same table:

using (ObjectContext ctx = new ObjectContext())
{

    var result = from f1 in ctx.Foo
                 join f2 in ctx.Foo on f1.bar equals f2.bar
                 join f3 in ctx.Foo on f1.bar equals f3.bar
                 select ....;
}

这篇关于实体框架，如何使用LinQ手动生成内部联接以进行实体化的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！