Here is my test case.
SQL Query (Northwind):
Select OrderId from Orders o
Right Join Customers c on o.CustomerID = c.CustomerID
Where c.City = 'Paris'
Which returns the following in SQL:
NULL
10738
10907
10964
11043
In Linq I have the following:
var q = from c in metaData.Customer
join o in metaData.Order
on c.CustomerId equals o.CustomerId into CustOrder
where c.City == "Paris"
from order in CustOrder.DefaultIfEmpty()
select new
{
OrderId = order.OrderId,
};
Which generates the following SQL:
Query: SELECT [LPA_L2].[OrderID] AS [OrderId] FROM ( [Northwind].[dbo].[Customers] [LPA_L1] LEFT JOIN [Northwind].[dbo].[Orders] [LPA_L2] ON [LPA_L1].[CustomerID] = [LPA_L2].[CustomerID]) WHERE ( ( ( ( [LPA_L1].[City] = @p1))))
Parameter: @p1 : String. Length: 15. Precision: 0. Scale: 0. Direction: Input. Value: "Paris".
And the following is the resultSet returned:
0
10738
10907
10964
11043
Also if you want to project it to OrderEntity, the following will work, and 0 will be returned too.
var q = from c in metaData.Customer
join o in metaData.Order
on c.CustomerId equals o.CustomerId into CustOrder
where c.City == "Paris"
from order in CustOrder.DefaultIfEmpty()
select new OrderEntity()
{
OrderId = order.OrderId,
};
As a variation, you can do the following check to be on the safe side:
var q = from c in metaData.Customer
join o in metaData.Order
on c.CustomerId equals o.CustomerId into CustOrder
where c.City == "Paris"
from order in CustOrder.DefaultIfEmpty()
select new
{
OrderId = order != null ? order.OrderId : 0,
};
Which results in the following SQL:
Query: SELECT CASE WHEN CASE WHEN ( ( [LPA_L2].[OrderID] IS NOT NULL)) THEN 1 ELSE 0 END=1 THEN [LPA_L2].[OrderID] ELSE @p2 END AS [OrderId] FROM ( [Northwind].[dbo].[Customers] [LPA_L1] LEFT JOIN [Northwind].[dbo].[Orders] [LPA_L2] ON [LPA_L1].[CustomerID] = [LPA_L2].[CustomerID]) WHERE ( ( ( ( [LPA_L1].[City] = @p3))))
Parameter: @p2 : Int32. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 0.
Parameter: @p3 : String. Length: 15. Precision: 0. Scale: 0. Direction: Input. Value: "Paris".
And yet the same resultSet is returned.
