Linq to Entities and LEFT OUTER JOIN issue with MANY:1 relations

Question

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)

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

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?

Edit

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

• Entity Framework V1
• 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.

Disharmonious

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

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 };

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

Cabana