none
How do I filter on a child navigation property using EF4.1 and not L2E, but still keep the parent? RRS feed

  • Question

  • Hi

    I apologies if this has been answered elsewhere, but I cannot find a solution for the following. I am trying to use EF only and avoid L2E if possible. This is a personal project in ASP.NET MVC and trying to expand my knowledge on the Entity Framework and how to harness its full potential as well as using strongly-typed POCO classes.

    I have four tables with the following relationships:

    [PRODUCT] (1:M) [PRODUCT_SET] (1:M) [PRODUCT_BRIDGE] (M:1) [ITEM]

    1. PRODUCT table contains the descriptive fields for the Product.

    2. PRODUCT_SET table contains the Price, as well as the ValidFrom and ValidTo date range.

    3. PRODUCT_BRIDGE table contains the Item quantity.

    4. ITEM table contains the descriptive fields for the Item.

    What I want to achieve are:

    1. Display a list of Products and display the current Price (where the PRODUCT_SET ValidTo date is NULL). I want the EF to produce SQL equivalent to and do one database query to retrieve results. No lazy loading.

    (I apologies for the use of * here, simpler and shorter for this forum)

    select P.*, PS.* from Product as P inner join ProductSet as PS on P.ProductID = PS.ProductID where PS.ValidFrom == null;

    Eager loading will obviously not work for this as Include will load all related records and cannot filter on child collection records. I have tried Explicit loading with little success because filtering a collection on another collection doesn't appear to be supported, or at least that I can find a solution for. I have achieved this in L2E and Projection Query, but want to find a way in pure EF.

    2. Display a Product and its details

    The displayed product will be the latest from the ProductSet table, but may also need to be able to pass in the ProductSetID to retrieve a particular version of a product (products may/could change but need to keep historical records of this i.e. adding additional items, removing items and replacing with new ones where supplier issues, promotions etc)

    Similarly I would like EF to produce SQL equivalent to below:

    select P.*, PS.*, PB.*, I.* from Product as P inner join ProductSet as PS on P.ProductID = PS.ProductID inner join ProductBridge as PB on PS.ProductSetID = PB.ProductSetID inner join Item as I on PB.ItemID = I.ItemID where P.ProductID = 1 and PS.ValidTo is null

    Where supplied a ProductSetId

    select * from Product as P inner join ProductSet as PS on P.ProductID = PS.ProductID inner join ProductBridge as PB on PS.ProductSetID = PB.ProductSetID inner join Item as I on PB.ItemID = I.ItemID where P.ProductID = 1 and PS.ProductSetID = 200

    I don't mind that it will create many Extent graphs.

    Your help in this would be greatly appreciated. Please keep solutions to EF 4.1 only, no L2E, L2O, LinqToSql or other technologies please. Many thanks.

    Tuesday, February 19, 2013 1:00 PM

Answers

  •  Hi,

    Please check this: 

    var productList = db.ProductSets.Include(ps => ps.Product)
                          .Where(ps => ps.ValidTo == null)                      
                          .Select(ps => ps);
    • Marked as answer by GUMatrix Thursday, February 21, 2013 8:15 AM
    Thursday, February 21, 2013 4:22 AM

All replies

  • What I know will not work is: var product = db.Products .Include(ps => ps.ProductSets) .Where(p => p.ProductId == 1) The problem with Include is it does not support filtering on child records. Meaning all related records have to be retrieved when the data is requested. The Where clause will only filter on the return type, which in this case is Product. The following syntax would therefore be illegal: .Include(ps => ps.ProductSet.Where(ps => ps.ProductSetID == 200))
    • Edited by GUMatrix Wednesday, February 20, 2013 8:36 AM
    Wednesday, February 20, 2013 8:35 AM
  • Seen something like this "var productList = db.ProductSets.Where(ps => ps.ValidTo == null).SelectMany(p => p.Product)" and wondered if it would work? At least for a Product list.
    • Edited by GUMatrix Wednesday, February 20, 2013 6:11 PM
    Wednesday, February 20, 2013 6:11 PM
  • If it works it may also work for Product details page.

    var productDetail = db.ProductSets
    .Where(ps => ps.ProductSetID == 200)
    .Include(pb => pb.ProductBridge.Select(i => i.Item)
    .Select(p => p.Product)
    .Where(p => p.ProductID == 1)

    Will post back if it works, if it will help someone else having similar issues.

    • Edited by GUMatrix Thursday, February 21, 2013 1:15 AM
    Wednesday, February 20, 2013 6:33 PM
  • Neither of these solutions work in terms of the expected results I want. The Select part of the statement is returning a list of Products where there is a ValidTo on the ProductSet table that is NULL. However, the issue now is the related ProductSet records are not in memory because the Select statement is not aware that it should have loaded them. Adding an Include after the Select statement then loads all related records but invalidates the Where statement on the child entity.

    The solution that I know will work using LINQ is:

    var productList = from p in db.Products join ps in db.ProductSets on p.ProductID equals ps.ProductID where ps.ValidTo == null select new { p, ps };

    This solution returns two anonymous types, one for Product and the other for ProductSet, but I could easily convert them intto a single strongly-typed object. I guess this takes the benefits of Projection Query and the ability to return a result set in a single database trip.

    • Proposed as answer by Alexander Sun Thursday, February 21, 2013 2:47 AM
    Thursday, February 21, 2013 1:09 AM
  •  Hi,

    Please check this: 

    var productList = db.ProductSets.Include(ps => ps.Product)
                          .Where(ps => ps.ValidTo == null)                      
                          .Select(ps => ps);
    • Marked as answer by GUMatrix Thursday, February 21, 2013 8:15 AM
    Thursday, February 21, 2013 4:22 AM
  • So simple and worked like a charm, thank you.
    Thursday, February 21, 2013 8:15 AM