Linq to Entities和LEFT OUTER JOIN问题与MANY:1关系 [英] Linq to Entities and LEFT OUTER JOIN issue with MANY:1 relations

问题描述

有人可以告诉我，为什么Linq to Entities将多对1 关系转换为left outer join而不是inner join?由于数据库本身具有引用约束，因此可以确保在正确的表中有一条记录，因此应改为使用inner join(这样会更快地工作)

Can somebody tell me, why does Linq to Entities translate many to 1 relationships to left outer join instead of inner join? Because there's referential constraint on DB itself that ensures there's a record in the right table, so inner join should be used instead (and it would work much faster)

如果关系很多到0..1 left outer join是正确的.

是否可以以某种方式编写LINQ，以便将其转换为inner join而不是left outer join.它将大大加快查询的执行速度...我以前从未使用过eSQL，但是在这种情况下使用它会明智吗?它可以解决我的问题吗?

Is it possible to write LINQ in a way so it will translate to inner join rather than left outer join. It would speed query execution a lot... I haven't used eSQL before, but would it be wise to use it in this case? Would it solve my problem?

我更新了标签，以包括我在后台使用的技术:

I updated my tags to include technology I'm using in the background:

• 实体框架V1
• Devart dotConnect for Mysql
• MySql数据库

如果有人可以测试在Microsoft SQL服务器上是否也是如此，那么这是否也是Devart的问题还是L2EF的一般功能也可以给我一些启示.但是我怀疑EF是这里的罪魁祸首.

If someone could test if the same is true on Microsoft SQL server it would also give me some insight if this is Devart's issue or it's a general L2EF functionality... But I suspect EF is the culprit here.

推荐答案

我在实体框架提供程序上做了一些工作，并进行了研究.我相信提供商本身在这种情况下别无选择.命令树由实体框架创建，并将其提供给提供程序以构建SQL.这是一个完整的猜测，但是在这种情况下它生成LEFT OUTER联接的原因可能是因为实体框架并不真正知道数据库中存在引用约束.例如，在从数据库创建实体模型后，我可以仔细研究一下实体模型，并添加/更改对数据库正在执行的操作没有反映的约束.也许由于这个原因，设计师选择了安全起见，并以防万一"生产了LEFT OUTER接头.

I have worked a little bit on an entity framework provider and have looked at that. I believe that the provider itself has no choice in the situation. The command tree is created by the entity framework and gives it to the provider to build the SQL. This is a complete guess here, but maybe the reason it generates the LEFT OUTER join in that situation is because the entity framework does not truly know that the referential constraint exists in the database. For example, I can go in and muck with the entity model after it is created from the database and add/change constraints that have no reflection on what the database is doing. Maybe for this reason, the designers chose to play it safe and produce the LEFT OUTER join "just in case".

尽管如此，我相信你可以得到一个内在的连接.例如，以下原因使提供程序建立了LEFT OUTER连接:

Nonetheless, I believe you can get an inner join. For example, the following caused the provider to build a LEFT OUTER join:

var res2 = from a in ent.answers
           select new
           { a.Answer1, a.user.UserName };

但是，以下结果导致了内部联接:

However, the following results in an INNER join:

res2 = from a in ent.answers
       join u in ent.users
       on a.UserID equals u.PK
       select new { a.Answer1, u.UserName };

此外，以下实体SQL产生了内部联接:

Also, the following entity SQL produced an inner join:

ObjectQuery<DbDataRecord> dr = ent.CreateQuery<DbDataRecord>( 
         "SELECT a.answer1, u.username " +
         "FROM answers as a inner join users as u on a.userid = u.pk" );

这篇关于Linq to Entities和LEFT OUTER JOIN问题与MANY:1关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！