Entity Framework Include With Where With 3 Tables

Question

I'm trying to include two tables off of one base table, and provide a "where" statement on the second table, but I'm getting a very confusing error (below). Any thoughts on the issue/solution?

ObjectQuery<STATE> productQuery = 
    LeadsContext.STATE.Include("REGION")
      .Where("it.REGION.BRAND.BRAND_ID = @brand", new ObjectParameter("brand", brand))
      .OrderBy("it.STATE_ABBV");

Basic table layout: STATE ------ REGION ------ BRAND

BRAND_ID is in BRAND

'BRAND' is not a member of 'Transient.collection[Citizens.Leads.Data.REGION(Nullable=True,DefaultValue=)]'. To extract properties out of collections, you must use a sub-query to iterate over the collection., near multipart identifier, line 8, column 1.

Answer 1

It sounds as if State.REGION is actually a collection of Region entities.

In which case you can't just access the BRAND navigation directly like that, because your statement tries to access the BRAND property of a Collection, rather than the BRAND property of an element in the collection.

If you were writing this query using LINQ to Entities rather than query builder methods you could do it like this:

var productQuery = from s in LeadsContext.State
                   from r in s.REGION
                   where r.Brand.Brand_ID == brand
                   orderby s.STATE_ABBR
                   select s;

Of course that wouldn't eagerly load REGION(s) so you might think you could write this:

var productQuery = from s in LeadsContext.State.Include("REGION")
                   from r in s.REGION
                   where r.Brand.Brand_ID == brand
                   orderby s.STATE_ABBR
                   select s;

But that won't work because your INCLUDE is lost when you do a Select Many (i.e. from y in z from x in y).

So you have to do the Include at the end like this:

var productQuery = (from s in LeadsContext.State
                   from r in s.REGION
                   where r.Brand.Brand_ID == brand
                   orderby s.STATE_ABBR
                   select s) as ObjectQuery<State>).Include("REGION");

See tip 22 for more on this workaround.

I'm not 100% sure that our query builder methods, i.e. Where(string), support sub-selects which is what is required.

So I'm not sure what the syntax would be in there.

Anyway I hope this helps

Alex