Linq to Entities and LEFT OUTER JOIN problem with MANY: 1 relationship

Question

Can someone tell me why Linq to Entities translates a lot in 1 to left outer join instead of inner join ? Because there is a referential restriction for the database itself, which provides an entry in the right table there, so inner join should be used instead (and it will work much faster).

If there was a relationship , many of the 0. 0. left outer join would be correct.

Question

Is it possible to write LINQ in such a way that it translates to inner join , and not to left outer join . This will speed up query execution ... I have not used eSQL before, but would it be wise to use it in this case? Does my problem solve?

Edit

I updated my tags to include the technology that I use in the background:

• Entity Framework V1
• Devart dotConnect for Mysql
• MySql Database

If someone can check if the same thing is true on the Microsoft SQL server, it will also give me some idea of ​​whether this is a Devart problem or is this the general functionality of L2EF ... But I suspect that EF is the culprit.

Answer 1

I did a little work on the entity infrastructure provider and looked at that. I believe that the provider itself has no choice in the situation. The command tree is created by the entity infrastructure and provides the SQL assembly to the provider. This is a complete hunch here, but perhaps the reason it generates the LEFT OUTER join in this situation is because the entity infrastructure really does not know that the reference constraint exists in the database. For example, I can go in and drop the entity model after creating it from the database and add / change constraints that have no idea what the database does. Perhaps for this reason, the designers decided to play safely and create LEFT OUTER, "just in case."

However, I believe you can get an inner join. For example, the following led the provider to create a LEFT OUTER connection:

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

However, the following results are in the INNER compound:

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

In addition, the following SQL object created an internal 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" );