左联接转换为Linq中的内部联接 [英] Left Join Translate to Inner join in Linq

问题描述

我有两个表:

产品代码:

Code             Year             PageNo
-----------------------------------------

和费用:

Code             Year         City         QsNo            price
----------------------------------------------------------------

我想将所有代码保留在特定的 year-pageNo 中，然后显示特定的 year-city-Qsno (如果存在)的成本以及不存在的成本存在，我返回null.

I want To preserve All code in specific year-pageNo and then show costs for specific year-city-Qsno(If exists) and for costs that don't exist I return null.

我写了这个查询:

var Result = from code in ent.ProductCodes
                         join cost in ent.Costs
                           on new { a = code.Year, b = code.Code } equals new { a = cost.Year, b = cost.Code }
                           into AllCosts
                         from OutPut in AllCosts.DefaultIfEmpty()
                         where code.PageNo == PageNo && OutPut.Year == Year && OutPut.City == City && OutPut.QsNo == Qsno
                         select new
                         {
                             ProductCode = code.Code
                             Col6 = OutPut.Price
                         };

但是它会转换为 INNER JOIN .问题出在哪里?

but it convert to INNER JOIN.Where is the problem?

推荐答案

问题是您在加入后正在检查 OutPut 变量上的Year，City和QsNo ...但是如果OutPut为null(如果AllCosts中没有行，则会发生这种情况)，那么这些检查将始终为false，因此对(代码，OutPut)对将被where子句过滤掉.EF会检测到这一事实，并生成查询，而仅使用内部联接就可以提高查询效率.

The problem is that you're checking the Year, City, and QsNo on the OutPut variable after the join... but if OutPut is null (which would happen if there are no rows in AllCosts) then these checks will always be false, so the pair (code, OutPut) will be filtered out by the where clause. EF detects this fact and generates a query which is more efficient by just using an inner join.

您真正想做的是从成本"中筛选出候选行，而不是对(代码，成本)对进行筛选.为此，您可以向上移动过滤器，使其直接应用于费用"表:

What you really want to do is filter out candidate rows from Costs, rather than filtering on (code, cost) pairs. To do this you can move your filter up, so it applies directly to the Costs table:

var Result = from code in ent.ProductCodes
                     join cost 
                       in ent.Costs.Where(c => c.Year == Year && c.City == City && c.QsNo == Qsno)
                       on new { code.Year, code.Code } equals new { cost.Year, cost.Code }
                       into AllCosts
                     from OutPut in AllCosts.DefaultIfEmpty()
                     where code.PageNo == PageNo
                     select new
                     {
                         ProductCode = code.Code
                         Col6 = OutPut.Price
                     };

这篇关于左联接转换为Linq中的内部联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！