Include Entity Framework with a condition

conditional entity-framework include

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.

1
4
7/14/2011 9:47:28 PM

Accepted Answer

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.

2
7/14/2011 10:25:57 PM

Popular Answer

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.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow