LINQ to SQL-如何在左侧联接中添加where子句? [英] LINQ to SQL - How to add a where clause to a left join?

问题描述

此LINQ查询表达式发出左连接并起作用:

This LINQ query expression emits a left join and works:

from p in Prices
join ip in ItemPrices 
    on new { p.PriceId,  ItemId = 7 } equals 
       new { ip.PriceId, ip.ItemId }
into priceItemPrice
from pip in priceItemPrice.DefaultIfEmpty()
select new
{
    pricesPriceId = p.PriceId,
    z = (int?)pip.PriceId,
    p.Content,
    p.PriceMinQ
}

发出的SQL:

-- Region Parameters
DECLARE @p0 Int = 7
-- EndRegion
SELECT [t0].[priceId] AS [pricesPriceId], 
    [t1].[priceId] AS [z], 
    [t0].[price] AS [Content], 
    [t0].[priceMinQ] AS [PriceMinQ]
FROM [price] AS [t0]
LEFT OUTER JOIN [itemPrice] AS [t1] 
    ON ([t0].[priceId] = [t1].[priceId]) 
    AND (@p0 = [t1].[itemId])

如何获取下面的SQL? 只是在末尾加上了where子句. "from pip"下的where子句不被接受，而DefaultIfEmpty()之前的where lambda表达式不起作用.我知道我可以在选择中过滤掉它，但这不是我所需要的.

How can I get it to emit the SQL below? It just has the where clause tacked on the end. A where clause is not accepted under the "from pip" and a where lambda expression before DefaultIfEmpty() doesn't work. I know I can filter it out in the select, but that's not what I need.

SELECT [t0].[priceId] AS [pricesPriceId], 
    [t1].[priceId] AS [z], 
    [t0].[price] AS [Content], 
    [t0].[priceMinQ] AS [PriceMinQ]
FROM [price] AS [t0]
LEFT OUTER JOIN [itemPrice] AS [t1] 
    ON ([t0].[priceId] = [t1].[priceId]) 
    AND (@p0 = [t1].[itemId])
WHERE [t1].[priceId] is null

更新 Oy vey，我的错误，where子句起作用了-由于某些原因，VS2008行为不佳，使我感到悲伤，而且我的肚子在咆哮.我在LinqPad中进行了测试，where子句很好.因此，这一小小的添加确实起作用了:

Update Oy vey, my mistake, the where clause did work - for some reason VS2008 was not behaving and giving me grief and my stomach was growling. I tested back in LinqPad and the where clause was fine. So this little addition did work:

...
from pip in priceItemPrice.DefaultIfEmpty()
*** where pip.ItemId == null ***
select new
...

推荐答案

以下是

Here is a sample of how OneDotNetWay has done something similar. I've tried to take their example and match up your query.

var query =  p in Prices
   join ip in ItemPrices 
   on 
   new { p.PriceId,  ItemId = 7 } 
   equals 
   new { ip.PriceId, ip.ItemId }
   into priceItemPrice
   from pip in priceItemPrice.DefaultIfEmpty()
   select new
   {
      pricesPriceId = p.PriceId,
      z = (int?)pip.PriceId,
      p.Content,
      p.PriceMinQ
   }

这篇关于LINQ to SQL-如何在左侧联接中添加where子句?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！