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.

score 3 · Answer 1 · answered Jul 15 '11 at 08:23

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 '11 at 08:23

Ladislav Mrnka

360,892
59
660
670

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? – WriteEatSleepRepeat Jul 15 '11 at 08:55
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. – Ladislav Mrnka Jul 15 '11 at 09:17
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. – Andreas H. Jul 15 '11 at 15:32
@Andreas: I guess I understand [implication of many includes](http://stackoverflow.com/questions/5521749/how-many-include-i-can-use-on-objectset-in-entityframework-to-retain-performance/5522195#5522195). – Ladislav Mrnka Jul 15 '11 at 15:39

score 2 · Accepted Answer · answered Jul 14 '11 at 22:25

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.

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, Jul 15 '11 at 08:57

score 0 · Answer 3 · edited Dec 12 '16 at 12:46

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

score 0 · Answer 4 · answered Jul 15 '11 at 08:49

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

Entity Framework Include with condition

4 Answers4

Linked

Related