I'm using the Northwind database and the self-servicing code.
When I use this this code to generate a query, it works perfectly.
LinqMetaData metaData = new LinqMetaData();
IQueryable<int> orderIds = metaData.Order.Where(o => o.ShipCountry == "USA").Select(o => o.OrderId);
orderIds = (from orders in orderIds
join filter in metaData.OrdersQry on orders equals filter.OrderId
select orders);
var result = (from orders in orderIds
join details in metaData.OrderDetailsExtended on orders equals details.OrderId
select details).ToList();
it generates this SQL
exec sp_executesql N'
SELECT DISTINCT
[LPA_L2].[Discount],
[LPA_L2].[ExtendedPrice],
[LPA_L2].[OrderID] AS [OrderId],
[LPA_L2].[ProductID] AS [ProductId],
[LPA_L2].[ProductName],
[LPA_L2].[Quantity],
[LPA_L2].[UnitPrice]
FROM ( (
SELECT [LPA_L3].[OrderId]
FROM ( (
SELECT [LPLA_1].[OrderID] AS [OrderId]
FROM [Northwind].[dbo].[Orders] [LPLA_1]
WHERE ( ( ( [LPLA_1].[ShipCountry] = @p1)))) [LPA_L3]
INNER JOIN [Northwind].[dbo].[Orders Qry] [LPA_L4]
ON [LPA_L3].[OrderId] = [LPA_L4].[OrderID])) [LPA_L1]
INNER JOIN [Northwind].[dbo].[Order Details Extended] [LPA_L2]
ON [LPA_L1].[OrderId] = [LPA_L2].[OrderID])',N'@p1 nvarchar(15)'
,@p1=N'USA'
However if I change add one line
LinqMetaData metaData = new LinqMetaData();
IQueryable<int> orderIds = metaData.Order.Where(o => o.ShipCountry == "USA").Select(o => o.OrderId);
orderIds = (from orders in orderIds
join filter in metaData.OrdersQry on orders equals filter.OrderId
[b] where filter.PostalCode == "83720"[/b]
select orders);
var result = (from orders in orderIds
join details in metaData.OrderDetailsExtended on orders equals details.OrderId
select details).ToList();
then it doesn't generate the correct sql and errors out
exec sp_executesql N'
SELECT DISTINCT
[LPA_L2].[Discount],
[LPA_L2].[ExtendedPrice],
[LPA_L2].[OrderID] AS [OrderId],
[LPA_L2].[ProductID] AS [ProductId],
[LPA_L2].[ProductName],
[LPA_L2].[Quantity],
[LPA_L2].[UnitPrice]
FROM ( (
[b] SELECT [LPA_L3].[orders] FROM ( ([/b]
SELECT [LPLA_1].[OrderID] AS [OrderId]
FROM [Northwind].[dbo].[Orders] [LPLA_1]
WHERE ( ( ( [LPLA_1].[ShipCountry] = @p1)))) [LPA_L3]
INNER JOIN [Northwind].[dbo].[Orders Qry] [LPA_L4]
ON [LPA_L3].[OrderId] = [LPA_L4].[OrderID])
WHERE ( ( ( [LPA_L4].[PostalCode] = @p2)))) [LPA_L1]
INNER JOIN [Northwind].[dbo].[Order Details Extended] [LPA_L2]
ON [LPA_L1].[orders] = [LPA_L2].[OrderID])',N'@p1 nvarchar(15),@p2 nvarchar(10)',@p1=N'USA',@p2=N'83720'
As you can see it tries to select orders from LPA_L3, which doesn't exist. If I change the "orders" variable in the following linq statement, to <variableName>, then it will subsequently generate sql that will try to select from LPA_L3.dbo.<variableName>
orderIds = (from orders in orderIds
join filter in metaData.OrdersQry on orders equals filter.OrderId
where filter.PostalCode == "83720"
select orders);
Also a corresponding question, why is the distinct being thrown into the generated SQL. That doesn't seem correct since there's no distinct/group by or anything else in the LINQ that would limit down duplicates.
