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.

Ladislav Mrnka · Accepted Answer · 2011-07-15 08:23:37Z

3

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.

answered Jul 15, 2011 at 8:23

Ladislav Mrnka

365k60 gold badges666 silver badges673 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

WriteEatSleepRepeat Over a year ago

OK, thank you very much for the answer. Thanks for pointing out that includes are not necessary and taking into consideration Andreas's answer. What do you mean by "requires multiple database queries"? Are you suggesting it might not be efficient? Having this done by creating a projection in the way I was doing doesn't seem to me a good idea, it looks so hard to maintain and edit that. There must be a pattern how to do this in the best way, so I am wondering, what is it?

Ladislav Mrnka Over a year ago

Projection is the only way if you want to load so many relations within single query and database round trip. If you write multiple queries you will have multiple database round trips.

Andreas H. Over a year ago

You always have to find a balance between eager (Include) and explicit loading (multiple roundtrips). In practice Includes are often killing performance. Thatswhy I suggested to use Include only for that properties you need within the query, an load other related Properties explicitly. Eager loading produces complex queries that might take long time to build but as said, explicit loading needs multiple queries to sql server, there always is finetuning needed.

Ladislav Mrnka Over a year ago

@Andreas: I guess I understand implication of many includes.

Andreas H. · Accepted Answer · 2011-07-14 22:25:57Z

2

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.

answered Jul 14, 2011 at 22:25

Andreas H.

78611 silver badges18 bronze badges

1 Comment

WriteEatSleepRepeat Over a year ago

please read my answer to you post. I don't understand why I can't post comments in the same way I post answers. It's very limited in formatting and length.

WriteEatSleepRepeat · Accepted Answer · 2011-07-15 08:49:03Z

@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.

Eric · Accepted Answer · 2016-12-12 12:46:03Z

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

Collectives™ on Stack Overflow

Entity Framework Include with condition

4 Answers 4

4 Comments

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related