c# - EF6 生成查询垃圾 : Eliminate superfluous null checks

Question

Note: this is built on [ MySQL ]

假设我有一个 master=>detail 结构，用以下 Linq 查询表示:

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
                   }

假设如果我直接对数据库执行以下查询，我的约 2M 记录集将在约 2 秒内开始返回

(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

假设我希望 Entity Framework 将 ^ 发送到数据库。如何防止它发送附加到我的内部连接末尾的垃圾:

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.

Answer 1

Solution with altered functionality

我为此设计的第一个可行的解决方案是消除查询中的连接条件并将过滤插入 where。这会产生以下 Linq 查询。

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

生成的 SQL 是

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`)