conditional include in linq to entities?

Question

I felt like the following should be possible I'm just not sure what approach to take.

What I'd like to do is use the include method to shape my results, ie define how far along the object graph to traverse. but... I'd like that traversal to be conditional.

something like...

dealerships
    .include( d => d.parts.where(p => p.price < 100.00))
    .include( d => d.parts.suppliers.where(s => s.country == "brazil"));

I understand that this is not valid linq, in fact, that it is horribly wrong, but essentially I'm looking for some way to build an expression tree that will return shaped results, equivalent to...

select *
from dealerships as d
outer join parts as p on d.dealerid = p.dealerid
    and p.price < 100.00
outer join suppliers as s on p.partid = s.partid
    and s.country = 'brazil'

with an emphasis on the join conditions.

I feel like this would be fairly straight forward with esql but my preference would be to build expression trees on the fly.

as always, grateful for any advice or guidance

Answer 1

Am I missing something, or aren't you just looking for the Any keyword?

var query = dealerships.Where(d => d.parts.Any(p => p.price < 100.00) || 
                              d.parts.suppliers.Any(s => s.country == "brazil"));

Answer 2

I know this can work with one single Include. Never test with two includes, but worth the try:

dealerships
    .Include( d => d.parts)
    .Include( d => d.parts.suppliers)
    .Where(d => d.parts.All(p => p.price < 100.00) && d.parts.suppliers.All(s => s.country == "brazil"))

Answer 3

This should do the trick:

using (TestEntities db = new TestEntities())
{
    var query = from d in db.Dealership
                select new
                {
                    Dealer = d,
                    Parts = d.Part.Where
                    (
                        p => p.Price < 100.0 
                             && p.Supplier.Country == "Brazil"
                    ),
                    Suppliers = d.Part.Select(p => p.Supplier)
                };

    var dealers = query.ToArray().Select(o => o.Dealer);
    foreach (var dealer in dealers)
    {
        Console.WriteLine(dealer.Name);
        foreach (var part in dealer.Part)
        {
            Console.WriteLine("  " + part.PartId + ", " + part.Price);
            Console.WriteLine
                (
                "  " 
                + part.Supplier.Name 
                + ", " 
                + part.Supplier.Country
                );
        }
    }
}

This code will give you a list of Dealerships each containing a filtered list of parts. Each part references a Supplier. The interesting part is that you have to create the anonymous types in the select in the way shown. Otherwise the Part property of the Dealership objects will be empty.

Also, you have to execute the SQL statement before selecting the dealers from the query. Otherwise the Part property of the dealers will again be empty. That is why I put the ToArray() call in the following line:

var dealers = query.ToArray().Select(o => o.Dealer);

But I agree with Darren that this may not be what the users of your library are expecting.

Answer 4

Yes that's what I wanted to do I think the next realease of Data Services will have the possiblity to do just that LINQ to REST queries that would be great in the mean time I just switched to load the inverse and Include the related entity that will be loaded multiple times but in theory it just have to load once in the first Include like in this code

return this.Context.SearchHistories.Include("Handle")
    .Where(sh => sh.SearchTerm.Contains(searchTerm) && sh.Timestamp > minDate && sh.Timestamp < maxDate);

before I tried to load for any Handle the searchHistories that matched the logic but don't know how using the Include logic you posted so in the mean time I think a reverse lookup would be a not so dirty solution

Answer 5

Are you sure this is what you want? The only reason I ask is, once you add the filter on Parts off of Dealerships, your results are no longer Dealerships. You're dealing in special objects that are, for the most part, very close to Dealerships (with the same properties), but the meaning of the "Parts" property is different. Instead of being a relationship between Dealerships and Parts, it's a filtered relationship.

Or to put it another way, if I pull a dealership out of your results and passed to a method I wrote, and then in my method I call:

var count = dealership.Parts.Count();

I'm expecting to get the parts, not the filtered parts from Brazil where the price is less than $100.

If you don't use the dealership object to pass the filtered data, it becomes very easy. It becomes as simple as:

    var query = from d in dealerships
               select new { DealershipName = d.Name, 
CheapBrazilProducts = dealership.Parts.Where(d => d.parts.Any(p => p.price < 100.00) || d.parts.suppliers.Any(s => s.country == "brazil")) };

If I just had to get the filtered sets like you asked, I'd probably use the technique I mentioned above, and then use a tool like Automapper to copy the filtered results from my anonymous class to the real class. It's not incredibly elegant, but it should work.

I hope that helps! It was an interesting problem.