Linq to llblgen 4.2 ignores join
Joined: 18-Oct-2013
The object fetch in the first query (the select p), does a SELECT DISTINCT ... as it will fetch unique rows, it won't return duplicate objects, it will return unique objects, so to avoid duplicates it will issue a DISTINCT in the query, and this leads to 77 rows (as the rest are duplicates). Other ORMs, like NHibernate and EF, return all duplicate rows, causing problems as in some cases you can't specify 'distinct' and you actually want the unique objects, not the duplicates.
The count on the other hand isn't an entity fetch and this is a simple count on the set with all the duplicate rows (so not the product rows, but the joined rows, this is a different set).
The other queries too don't do an entity fetch, but actually a projection. The new {p} query is a little confusing perhaps, as it does return a set of elements which contain entities, but the query is a projection query, which uses a different pipeline and doesn't do the optimization of filtering out duplicates (as a projection requires duplicate filtering to be explicitly defined through .Distinct())
Hope this helps 
Joined: 18-Oct-2013
EF:
var ctx = new Northwind26DataContext();
var q = (from p in ctx.Products
join od in ctx.OrderDetails on p.ProductId equals od.ProductId
select p).Include(p=>p.OrderDetails).ToList();
Returns 2015 product entities. Each entity object has related OD entities. Many of them are duplicates (as there are just 2015 OD entities in the DB). The query generated by the linq query above: (can you guess how it looks like? No, no-one can)
SELECT [Project1].[OrderID] AS [OrderID],
[Project1].[ProductID1] AS [ProductID],
[Project1].[ProductID] AS [ProductID1],
[Project1].[Discontinued] AS [Discontinued],
[Project1].[ProductName] AS [ProductName],
[Project1].[QuantityPerUnit] AS [QuantityPerUnit],
[Project1].[ReorderLevel] AS [ReorderLevel],
[Project1].[UnitPrice] AS [UnitPrice],
[Project1].[UnitsInStock] AS [UnitsInStock],
[Project1].[UnitsOnOrder] AS [UnitsOnOrder],
[Project1].[CategoryID] AS [CategoryID],
[Project1].[SupplierID] AS [SupplierID],
[Project1].[C1] AS [C1],
[Project1].[Discount] AS [Discount],
[Project1].[OrderID1] AS [OrderID1],
[Project1].[ProductID2] AS [ProductID2],
[Project1].[Quantity] AS [Quantity],
[Project1].[UnitPrice1] AS [UnitPrice1]
FROM (SELECT [Extent1].[ProductID] AS [ProductID],
[Extent1].[ProductName] AS [ProductName],
[Extent1].[SupplierID] AS [SupplierID],
[Extent1].[CategoryID] AS [CategoryID],
[Extent1].[QuantityPerUnit] AS [QuantityPerUnit],
[Extent1].[UnitPrice] AS [UnitPrice],
[Extent1].[UnitsInStock] AS [UnitsInStock],
[Extent1].[UnitsOnOrder] AS [UnitsOnOrder],
[Extent1].[ReorderLevel] AS [ReorderLevel],
[Extent1].[Discontinued] AS [Discontinued],
[Extent2].[OrderID] AS [OrderID],
[Extent2].[ProductID] AS [ProductID1],
[Extent3].[OrderID] AS [OrderID1],
[Extent3].[ProductID] AS [ProductID2],
[Extent3].[UnitPrice] AS [UnitPrice1],
[Extent3].[Quantity] AS [Quantity],
[Extent3].[Discount] AS [Discount],
CASE
WHEN ([Extent3].[OrderID] IS NULL) THEN CAST(NULL AS int)
ELSE 1
END AS [C1]
FROM [dbo].[Products] AS [Extent1]
INNER JOIN [dbo].[Order Details] AS [Extent2]
ON [Extent1].[ProductID] = [Extent2].[ProductID]
LEFT OUTER JOIN [dbo].[Order Details] AS [Extent3]
ON [Extent1].[ProductID] = [Extent3].[ProductID]) AS [Project1]
ORDER BY [Project1].[OrderID] ASC,
[Project1].[ProductID1] ASC,
[Project1].[ProductID] ASC,
[Project1].[C1] ASC
Executing this monster returns 66967 rows. A tremendous amount of them are duplicates. The vast majority of them. The materialization of the entities takes 330ms(!) on a 4 core VM on a i7 3.4ghz to process. (that's not DB, query time and other overhead, just entity materialization) Not only that, but you would want all 66967 product entities in a set, right? As that's what the SQL dictates. But you got only 2015.
It also has a big problem: As there are product duplicates in the set, which ones are the unique ones and which ones are the duplicates? Do the duplicates refer to the duplicate order details or to the unique ones (where 'unique ones' are the ones you'd want to keep, e.g. for modification, and the duplicate ones are the ones to skip)? The graph returned is completely unusable.
At the same time, our system:
var q = (from p in metaData.Product
join od in metaData.OrderDetail on p.ProductId equals od.ProductId
select p).WithPath(path=>path.Prefetch(p => p.OrderDetails)).ToList();
results in 2 queries:
SELECT DISTINCT [LPA_L1].[CategoryID] AS [CategoryId],
[LPA_L1].[Discontinued],
[LPA_L1].[ProductID] AS [ProductId],
[LPA_L1].[ProductName],
[LPA_L1].[QuantityPerUnit],
[LPA_L1].[ReorderLevel],
[LPA_L1].[SupplierID] AS [SupplierId],
[LPA_L1].[UnitPrice],
[LPA_L1].[UnitsInStock],
[LPA_L1].[UnitsOnOrder]
FROM ( [Northwind].[dbo].[Products] [LPA_L1]
INNER JOIN [Northwind].[dbo].[Order Details] [LPA_L2]
ON [LPA_L1].[ProductID] = [LPA_L2].[ProductID])
and
SELECT [Northwind].[dbo].[Order Details].[Discount],
[Northwind].[dbo].[Order Details].[OrderID] AS [OrderId],
[Northwind].[dbo].[Order Details].[ProductID] AS [ProductId],
[Northwind].[dbo].[Order Details].[Quantity],
[Northwind].[dbo].[Order Details].[UnitPrice]
FROM [Northwind].[dbo].[Order Details]
WHERE ([Northwind].[dbo].[Order Details].[ProductID] IN
(SELECT [LPA_L1].[ProductID] AS [ProductId]
FROM ( [Northwind].[dbo].[Products] [LPA_L1]
INNER JOIN [Northwind].[dbo].[Order Details] [LPA_L2]
ON [LPA_L1].[ProductID] = [LPA_L2].[ProductID])))
The first returning 74 rows, the latter 2015. To materialize the entities they together (with merging!) take 19.5ms combined, that's 2 commands. No duplicates for you to filter out in all the product entities and no duplicates for you to filter out in the OrderDetail entities related to the products.
I have no idea why you'd want EF's system over ours. Just because they do something a certain way doesn't make it 'the' way to do it, on the contrary. Let us do our job, Bart, we know what we're doing so you can reap the benefits from that.