EF6 generated query garbage: Eliminate superfluous null checks

Question

Note: this is built on [ MySQL ]

Say I have a master=>detail structure, represented with the following Linq query:

IQueryable query = from master in myschema_master
                   join detail in myschema_detail
                   on detail.correlator equals request.id
                   select new
                   {
                      Id = master.id,
                      Attribute = detail.interestingcolumn
                   }

Say that if I execute the following query directly against the database, my ~2M record set starts returning in ~2 seconds

(Note: this is not the query that the EF Provider is sending to the database for the statement above)

select * 
from myschema_master 
inner join myschema_detail 
   on myschema_master.id = myschema_detail.correlator

Say that I want Entity Framework to send ^ to the database. How do I prevent it from sending this garbage tacked on to the end of my inner join:

OR ((`Extent1`.`correlator` IS  NULL) AND (`Extent2`.`id` IS  NULL))

For context, the full query that is being sent to the database by the provider is:

SELECT
      1 AS `C1`, 
      `Extent1`.`id`, 
      `Extent2`.`interestingattribute`
FROM `myschema_master` AS `Extent1`
INNER JOIN `myschema_detail` AS `Extent2` 
    ON ((`Extent1`.`id`) = `Extent2`.`correlator`) 
    OR (
           (`Extent1`.`id` IS  NULL) 
       AND (`Extent2`.`correlator` IS  NULL)
       )

Alsø wik: the id and correlator fields are nullable Int64 and cannot currently be modified, but I want to completely eliminate the null check that EF and the underlying provider are sending to the db. (master.id is null and detail.correlator is null) creates a worthless Cartesian product that just wastes processing time

Alsø alsø wik ...I tried executing with Configuration.UseDatabaseNullSemantics set to both true and false (...in separate round trips, wise guy) and neither removed the IS NULL equivalency check. It was a long shot, but I'd never needed to set the property explicitly so wasn't sure exactly what it would do.

This technically is a duplicate of [Entity Framework LINQ to Entities Join Query Timeout](http://stackoverflow.com/questions/42973300/entity-framework-linq-to-entities-join-query-timeout), although it's not so clear from the other post title. — Ivan Stoev, May 08 '17 at 23:38
I saw the linked post, but the solution there is insufficient. The resultant output was a where based equi-join rather than the proper query. This isn't a duplicate — K. Alan Bates, May 09 '17 at 20:31
You think `JOIN` on `1 = 1` is a "proper"? And is really a `join`? Come on :) `UseDatabaseNullSemantics = true` and `where` based join is the proper solution. Anyway, as you can see from my first comment, I'm not planning to close it as duplicate, so no worry - the comments are just for the potential future readers. — Ivan Stoev, May 09 '17 at 20:41
I never said that joining on `1=1` generates the SQL that I'm expecting. In fact, I explicitly mentioned that pushing the equijoin into the where clause creates a query that executes in a way that I do not desire (the set doesn't begin streaming until the entire set is ready) ...so there is still work to do to get the provider to crap out the query that it's **supposed** to instead of the garbage that it **wants** to. But to your point, *of course* `JOIN table on 1=1` is "proper." It generates an identity-based Cartesian product for run-times which require JOIN clauses on tables. — K. Alan Bates, May 09 '17 at 21:04
I have a feeling that we are totally on a different page. All I'm saying is that as I recall, simply changing `join` to `from` and removing `on 1 equals 1` from your LINQ query in combination with `UseDatabaseNullSemantics = true` will let EF generate the **exact** SQL you were after. No more, no less. I have no idea what *runtimes* and *identity-based Cartesian joins* are you talking about, so I think to stop here. Have a nice day. — Ivan Stoev, May 10 '17 at 00:11
@IvanStoev Changing `UseDatabaseNullSemantics = true` and switching the `join` to a `from` does not eliminate the `or (Extent1.a is null and Extent2.b is null)` nonsense. **I already mentioned that `UseDatabaseNullSemantics = true` did not change the behavior**. The only modification that was made by switching the `join`s to `from`s is that the SQL generated with `CROSS JOIN`s, which is not what I am looking for. — K. Alan Bates, May 10 '17 at 12:31

K. Alan Bates · Answer 1 · 2017-05-11T15:53:09.660

Solution with altered functionality

The first viable solution I could devise for this was to eliminate the join condition in the query and push the filtration into the where. This results in the following Linq query.

IQueryable query = from master in myschema_master
                   join detail in myschema_detail
                   on 1 equals 1
                   where detail.correlator == (Int64?)master.id
                   select new
                   {
                      Id = master.id,
                      Attribute = detail.interestingcolumn
                   };
//In LinqPad
(this as IObjectContextAdapter).ObjectContext.CommandTimeout = 120;
this.Configuration.UseDatabaseNullSemantics = true;
var set = query.Dump();

//Outside of LinqPad, just set ^ on Context and materialize the 
//IEnumerable as you normally would

The resultant SQL was

SELECT
1 AS `C1`, 
`Extent1`.`id`, 
`Extent2`.`interestingcolumn`    
FROM `my_schema_master` AS `Extent1` 
    INNER JOIN `myschema_detail` AS `Extent2` ON 1 = 1
 WHERE `Extent2`.`correlator` = (`Extent1`.`id`)

EF6 generated query garbage: Eliminate superfluous null checks

1 Answers1