LLBLgen 3.0 /SQL Server 2008
3 tables: OrderHeader, OrderDetail, Product
OrderHeader 1:m OrderDetail
Product 1:m OrderDetail
So an order header has details, and each detail refers to a product. I want to write a query to get the order headers, and their details but only those details which are for a product over a certain value, say 100.
so something like the following queries:
SELECT * from OrderHeader; // imagine this returns order_ids 1 and 2
SELECT d.* from OrderDetail d
JOIN Product p on d.product_id = p.product_id
WHERE p.value > 100
AND d.order_id IN (1,2);
How do I do that? Selecting the order header and the order detail in a prefetch using WithPath is easy, but how do I do the join to Product to restrict the order details?
I see there is a FilterOn but it wants bool function and I don't understand how to do it. I can do it the non-linq "old way" with AddRelation and PrefetchPaths but I need to do it using Linq as I need an IQueryable as the result.
The easy bit:
var q = (from o in meta.OrderHeader
select o).WithPath(op => op.Prefetch(oh => oh.OrderDetails))
Thanks,
Scott
