Linq to entities with conditional include?

conditional entity-framework include linq

Question

The following seems like it should be achievable. Simply put, I'm unsure of how to proceed.

I want to tailor my findings by defining how much of the object graph to traverse using the include method. The traversal should, however, be conditional, in my opinion.

something like...

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

I am aware that this is not appropriate linq, in fact, that it is hideously incorrect, but I'm basically searching for a method to construct an expression tree that will provide results that are shaped, similar 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'

focusing particularly on the join circumstances.

My desire would be to create expression trees as needed.

As usual, I appreciate any suggestions or direction.

1
21
7/22/2009 12:43:53 AM

Accepted Answer

This ought to be effective:

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
                );
        }
    }
}

With the help of this code, you can get a list of Dealerships, each with a filtered list of components. Every component mentions a Supplier. The challenging element is having to build the anonymous types in the select in the manner that is shown. If not, the Dealership objects' Part property will be blank.

Additionally, you must run the SQL query before choosing the dealers from the query. If not, the merchants' Part property will once again be unoccupied. For this reason, I added the ToArray() function to the line that follows:

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

However, I agree with Darren that it's possible that this isn't what your library's patrons are anticipating.

15
7/22/2009 9:45:16 AM

Popular Answer

Exactly what you want, are you certain? The only reason I ask is because your results are no longer dealerships after you apply the filter on Parts off of Dealerships. Generally speaking, the special objects you trade in are fairly similar to Dealerships (and have the same features), but the "Parts" property has a distinct definition. It's a filtered connection rather than one between Dealerships and Parts.

In other words, if I choose a dealership from your results and give it to a method I created, I would then call:

var count = dealership.Parts.Count();

I anticipate receiving the components, not the filtered ones, from Brazil for less than $100.

It becomes quite simple if you don't utilize the dealership object to send the filtered data. It then becomes as easy 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 needed to get the filtered sets, as you requested, I would probably use the method I previously indicated before using a program like Automapper to transfer the filtered outcomes from my anonymous class to the actual class. Although not very attractive, it ought to function.

Hope that was helpful. It was a fascinating issue.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow