Linq Left Join，其中right为空 [英] Linq Left Join where right is null

问题描述

对于linq来说相对较新，来自SQL.因此，我正在尝试找出以下各项的左联接:

relatively new to linq, coming from SQL. So, I'm trying to figure out left joins for the following:

SELECT * from MASTER m
LEFT JOIN CHILD C
    ON m.ID=C.MASTER_ID
WHERE C.MASTER_ID is null

因此，通常这将返回来自Master的所有没有孩子的记录.我发现了.DefualtIfEmpty()，但这并不能消除具有子项的主记录.

So, normally this would return all of the records from Master that do not have a child. I've discovered the .DefualtIfEmpty() but that doesn't eliminate the master records that have children.

我开始遵循以下原则:

var recs=from m in MASTER
         from c in child
             .where (mapping=>mapping.MasterId == m.Id)
             .DefaultIfEmpty()
         select new { MasterId = m.Id};

但那是我被困住的程度.我假设.DefaultIfEmpty()不是我要的东西. 注意:主表中有几百万行.孩子们的人数差不多.我只说这是因为回拉所有记录等效率不高.理想情况下，生成的SQL看起来像我发布的SQL.

But that's as far as I got and got stuck. I'm assuming the .DefaultIfEmpty() isn't what I'm looking for. Note: The master table has a few million rows in it. The children are close to the same count. I only mention because it won't be efficient to pull back all of the records, etc. Ideally, the SQL generated will look like the SQL I posted.

谢谢.

推荐答案

这将为您提供所有没有孩子的主记录.

This will give you all master records without children.

var recs = MASTER.Where(x => !child.Any(y => m.Id == c.MasterId));

!Any将生成一个not Exists SQL语句，该语句将在执行计划中转换为anti semi join，这对于此类检查是最好的.

the !Any will generate a not Exists SQL statement which will be translated into a anti semi join in the execution plan which is the best you can get for this type of check.

这篇关于Linq Left Join，其中right为空的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！