LINQ : Some more on foreign keys

Continuing the series of LINQ to SQL using the Northwind DB:

Note: Can only be done if table has "Entity Set" relationship with another table i.e. green left-pointing arrow in LINQPad

Let's start with a compiled query:

var products = CompiledQuery.Compile ((TypedDataContext dc, decimal minUnitPrice) =>
from prod in Products
where prod.OrderDetails.Any (c => c.UnitPrice > minUnitPrice)
select prod
);

products (this, 20).Dump ("Unit price > $20");

OK - let's try that as a simple lambda without the compiled query:

var products1 =
from prod in Products
where prod.OrderDetails.Any (c => c.UnitPrice > 20)
select prod;

products1.Dump();

OK - let's try writing that again the "SQL" way with foreign key x = foreign key y:

var products2 =
(from prod in Products
from od in OrderDetails
where od.UnitPrice > 20 && prod.ProductID == od.ProductID
select prod).Distinct();

products2.Dump();

Hang on! There's far too many rows. That's because the relationship is duplicated so we need to add the "Distinct" keyword. You'd think that the construct would be:

select prod.Distinct();

but that doesn't work. If you look closely at the code snippet above, you'll see that the "Distinct" is applied to the whole clause (look at the brackets) rather than just the "Select" element.

Enjoy!