Linq SelectMany in Entity Framework

Question

I'm using Entity Framework. My typical access functionality would be something like this:

    public IEnumerable<Product> Category(string category, int term)
    {
        using (var uow = new UnitOfWork(Connections.LoanComparision))
        {
            var r = new Repository<Product>(uow.Context);

            return r.Find(x => x.Category == category)
                .Include(x => x.ProductDetails)
                .Include(x => x.ProductRates)
                .Include(x => x.Provider)
                .ToList();
        }
    }

You will note from the code above 2 things.

• The query returns a list of products with a category including linked entities.
• There are a number of ProductDetail lines for each Product.

What I want to do (and haven't been able to work out) is to apply selection criteria on the ProductDetails table.

The stuff inside .Find (my function) can be replaced by a standard .Where clause, but I need to filter on MinTerm and MaxTerm (which is in ProductDetails) yet still return the complete Product dataset (including linked entities), not just ProductDetails.

Where(x => term >= x.MinTerm && term <= x.MaxTerm)

I can work out how to do it by referencing ProductDetails first and linking to other tables but can't in this configuration. Is it possible?

Answer 1

If the relationship between Product and ProductDetail is one to many you could do this:

var query= context.ProductDetails.Include(pd=>pd.Product.ProductRates)
                                 .Include(pd=>pd.Product.Provider)
                                 .Where(pd=> pd.Product.Category == category 
                                             && term >= pd.MinTerm && term <= pd.MaxTerm);

And if you want the list of products you could do the following:

var query1=query.Select(pd=>pd.Product).Distinct().ToList();