Entity Framework Include with condition

Question

I need to filter a dealer based on id and the uncomplete checkins

Initially, it returned the dealer based only on id:

    // TODO: limit checkins to those that are not complete
    return this.ObjectContext.Dealers
        .Include("Groups")
        .Include("Groups.Items")
        .Include("Groups.Items.Observations")
        .Include("Groups.Items.Recommendations")
        .Include("Checkins")
        .Include("Checkins.Inspections")
        .Include("Checkins.Inspections.InspectionItems")
        .Where(d => d.DealerId == id)
        .FirstOrDefault();

As you can see the requirement is to limit the checkins. Here's what I did:

var query = from d in this.ObjectContext.Dealers
                            .Include("Groups")
                            .Include("Groups.Items")
                            .Include("Groups.Items.Observations")
                            .Include("Groups.Items.Recommendations")
                            .Include("Checkins.Inspections")
                            .Include("Checkins.Inspections.InspectionItems")
                            .Where(d => d.DealerId == id)
                        select new
                        {
                            Dealer = d,
                            Groups = from g in d.Groups
                                     select new
                                     {
                                         Items = from i in g.Items
                                                 select new
                                                 {
                                                     Group = i.Group,
                                                     Observations = i.Observations,
                                                     Recommendations = i.Recommendations
                                                 }
                                     },
                            Checkins = from c in d.Checkins
                                       where c.Complete == true
                                       select new
                                       {
                                           Inspections = from i in c.Inspections
                                                         select new
                                                         {
                                                             InspectionItems = i.InspectionItems
                                                         }
                                       }

                        };

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

            return dealer;

It works. However, I am not convinced I am doing the right thing.

What is the best way to accomplish what I did? A stored procedure maybe?

I am not sure I even have to use Include clause anymore

Thank you.

Answer 1

If you want to load filtered relations with single query you indeed have to execute such projection but you don't need those calls to Include. Once you are building projections includes are not use - you have returned data under your control.

Stored procedure will help you only if you fall back to plain ADO.NET because stored procedures executed through Entity framework are not able to fill related entities (only flattened structures).

Automatic fixupu mentioned by @Andreas requires multiple database queries and as I know it works only if lazy loading is disabled because proxied object somehow doesn't have information about fixup and it still has its internal flags for each relation as not loaded so when you access them for the first time they still execute additional query.

Answer 2

@Andreas H:

Awesome, thank you a lot.

I had to adjust your suggestion like this and it worked:

var dealer = this.ObjectContext.Dealers
                    .Include("Groups")
                    .Include("Groups.Items")
                    .Include("Groups.Items.Observations")
                    .Include("Groups.Items.Recommendations")
                    .Where(p => p.DealerId == id).
                    FirstOrDefault();
        if (dealer != null)
        {
            this.ObjectContext.Checkins
                    .Include("Inspections")
                    .Include("Inspections.InspectionItems")
                   .Where(c => !c.Complete && c.Dealer.DealerId == dealer.DealerId)
                   .ToList();
        }
        return dealer;

I still have to use the Include otherwise it won't return the referenced entities.

Note also that Dealer.Groups are unrelated to the Dealer.Checkins.

So if there's no checkins satisfying the condition, Groups still need to be returned.

It's interesting to note that at first, I put the two include for checkins to the dealer

var dealer = this.ObjectContext.Dealers
                        .Include("Groups")
                        .Include("Groups.Items")
                        .Include("Groups.Items.Observations")
                        .Include("Groups.Items.Recommendations")
                        .Include("Checkins.Inspections")
                        .Include("Checkins.Inspections.InspectionItems")
                        .Where(p => p.DealerId == id).
                        FirstOrDefault();
            if (dealer != null)
            {
                this.ObjectContext.Checkins
                        .Where(c => c.Complete && c.DealerId == id)
                       .ToList();
            }
            return dealer;

but it returned all the Checkins including those which are not complete.

I don't understand exactly why the latter doesn't work but the former does, how are the entities are resolved. I somehow can intuit that the former returns all data.

Answer 3

Your accepted solution will generate multiple database queries. As Ladislav Mrnka said a projection is the only way to pull your result with one query. The maintance of your code indeed hard. Maybe you could use an IQueryable-Extension that builds the projection dynamically and keep your code clean:

var query = this.ObjectContext.Dealers.SelectIncluding( new List<Expression<Func<T,object>>>>(){

    x => x.Groups,
    x => x.Groups.Select(y => y.Items),
    x => x.Groups.Select(y => y.Items.Select(z => z.Observations)),
    x => x.Groups.Select(y => y.Items.Select(z => z.Recommendations)),
    x => x.Checkins.Where(y => y.Complete==true),
    x => x.Checkins.Select(y => y.Inspections),
    x => x.Checkins.Select(y => y.Inspections.Select(z => z.InspectionItems))

});
var dealer = query.First();
return dealer;

You can find the extension at thiscode/DynamicSelectExtensions on github

Answer 4

Maybe you can make use of the relation fixup mechanism in the EF ObjectContexts. When you do multiple queries in the same context for entities, that are related by associations, these are resolved. Assuming your association between Dealers and Checkins is 1:n with navigation properties on each side, you could do like:

var dealer = yourContext.Dealers
             .Where(p => p.DealerId == id)
             .FirstOrDefault();
if(dealer != null)
{
    yourContext.Checkins
           .Where(c => c.Complete && c.DealerId == dealer.DealerId)
           .ToList();

I have not tested this by now, but since EF recognises that the Checkins, it inserts into the context by the second query belong to the dealer from the first query, corresponding references are created.