Linq to Entities和LEFT OUTER JOIN问题与MANY：1关系

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)

有人可以告诉我,为什么Linq to Entities将多个关系转换为左外连接而不是内连接?因为对DB本身存在引用约束以确保在右表中有记录,所以应该使用内连接(并且它将更快地工作)

If relation was many to 0..1 left outer join would be correct.

如果关系是多到0..1左外连接将是正确的。

Question

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?

是否有可能以某种方式编写LINQ,因此它将转换为内连接而不是左外连接。它会大大加快查询执行速度......我以前没有使用过eSQL,但在这种情况下使用它会是明智的吗?它会解决我的问题吗?

Edit

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

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

Entity Framework V1

实体框架V1

Devart dotConnect for Mysql

Devart dotConnect for Mysql

MySql database

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.

如果有人可以测试在Microsoft SQL服务器上是否也是如此,如果这是Devart的问题或者它是一般的L2EF功能,它也会给我一些见解......但我怀疑EF是罪魁祸首。

1 个解决方案

#1

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".

我已经对实体框架提供程序进行了一些工作,并对此进行了研究。我相信提供商本身在这种情况下别无选择。命令树由实体框架创建,并将其提供给提供程序以构建SQL。这是一个完整的猜测,但也许它在这种情况下生成LEFT OUTER连接的原因是因为实体框架并不真正知道数据库中存在引用约束。例如,在从数据库创建实体模型并添加/更改对数据库正在执行的操作没有反映的约束之后,我可以继续使用实体模型。也许是因为这个原因,设计师选择安全地玩它并产生LEFT OUTER加入“以防万一”。

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

尽管如此,我相信你可以得到一个内心的联系。例如,以下内容导致提供程序构建LEFT OUTER连接:

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

However, the following results in an INNER join:

但是,以下结果是INNER连接:

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

Also, the following entity SQL produced an inner join:

此外,以下实体SQL生成了一个内部联接:

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" );

#1