# LINQ to SQL-如何为左键添加where子句？[英] LINQ to SQL - How to add a where clause to a left join?

### 问题描述

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
}

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

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

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

## 推荐答案

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
}

### 问题描述

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 emitted:

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

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

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
}