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

Answer 2

Alex, you can achieve something similar in query builder by approaching the query differently. Query builder supports exists. The query will look something like this:

 ObjectQuery productQuery = 
        LeadsContext.STATE.Include("REGION")
          .Where("EXISTS(SELECT 1 FROM it.REGION.BRAND as b WHERE b.BRAND_ID
= @brand)", new ObjectParameter("brand", brand))
          .OrderBy("it.STATE_ABBV");

Answer 3

Can Someone help to interpret Linq to Entities Query to Entity SQL I got Customers and Orders entities. Need to extract Customers FirstName and their orders price Sum in one row.

heres linq to entities query which works fine: var query = from c in _context.Customers select new{ c.ID, c.Firstname, PriceSum=(c.Orders.Select(o => o.Price).Sum())//need to interpret this part. };

What it will look like in entity sql? I cant interpret it. plz someone write some example, and after I'll get clue and interpret other complex querys too

i just wrote like "SELECT VALUE Row (c.FirstName, sum(c.Orders.Price)) FROM Customers as c" but its not working obviously.