Entity Framework Eager Loading not working

Question

I want to load a list of objects from db using Entity Framework 6 and Eager loading. But Entity Framework instead uses lazy loading. I've used SQL profiler and the queries are executed when a property referring to the child entities is accessed. By using the 'Include' you suppose to load related entities in one go but it's not happening. My Code is presented below:

    using (var flightsPricingRulesContext = new FlightsPricingRulesDbContext())
    {
       flightsPricingRulesContext.Configuration.ValidateOnSaveEnabled = false;
       flightsPricingRulesContext.Configuration.AutoDetectChangesEnabled = false;


   var filter = PredicateBuilder.True<FlightsPricingRulesDataAccess.Models.ServiceFee>();

    if (!String.IsNullOrEmpty(selectedMarketId))
    {
       var selectedMarket = Int32.Parse(selectedMarketId);
       filter = filter.And(sf => sf.MarketId == selectedMarket);
    }

    if (!String.IsNullOrEmpty(selectedApplicationTypeId))
    {
       var selectedAppplicationType = Int32.Parse(selectedApplicationTypeId);
       filter = filter.And(sf => sf.ApplicationType == selectedAppplicationType);
    }

    var Query = 
    from P in flightsPricingRulesContext.ServiceFee.AsExpandable().Where(filter)select P;

    switch (orderby)
    {
      case null:
      case "":
      case "Id":
      Query = String.IsNullOrEmpty(orderbydirection) || orderbydirection  == "ASC"
      ?Query.OrderBy(p => p.Id): Query.OrderByDescending(p => p.Id);
      break;

      case "market":
      Query = String.IsNullOrEmpty(orderbydirection) || orderbydirection == "ASC"
      ? Query.OrderBy(p => p.MarketId)
      : Query.OrderByDescending(p => p.MarketId);
      break;
    }

    var takeitems = 10 ;
    var skipitems = (Int32.Parse(page) - 1) * 10);

    //BY USING INCLUDE EAGER LOADING IS ENABLED
    Query = Query.Skip(skipitems).Take(takeitems).
    Include(sf => sf.ServiceFeeZone.Select(sfz => sfz.Zone)).
    Include(sf => sf.ServiceFeeCarrier).
    Include(sf => sf.ServiceFeeClassOfService).
    Include(sf => sf.ServiceFeeDate).
    Include(sf => sf.ServiceFeeMarkUpAssignment).
    Include(sf => sf.ServiceFeeAssignment);

    var results = Query.ToList();//HERE A COMPLETE QUERY SHOULD BE 
    //SENT TO THE DB FOR RETRIEVING ENTITES INCLUDING THEIR CHILDREN

    var totalresults = flightsPricingRulesContext.ServiceFee.AsExpandable().Count(filter);

    var pagedservicefees = new PagedServiceFee();
    pagedservicefees.totalitems = totalresults.ToString();
    pagedservicefees.servicefees = new List<FlightsPricingRules.Models.ServiceFee>();


    foreach (var servicefeedto in results)
    {
       var servicefee = new FlightsPricingRules.Models.ServiceFee();

       servicefee.id = servicefeedto.Id.ToString();
       servicefee.marketId = servicefeedto.MarketId.ToString();
        //.....
        //SOME MORE PROPERTIES
        //                      

    //CHILD ENTITIES

        //Zones
        servicefee.zones = new List<Zone>();
        //HERE AN   ADDITIONAL QUERY IS MADE TO LOAD THE CHILD ENTITIES-WHY?
        foreach (var zonedto in servicefeedto.ServiceFeeZone)
        {
           var zone = new Zone();
           zone.id = zonedto.ZoneId.ToString();
           zone.name = zonedto.Zone.Name;
           servicefee.zones.Add(zone);
         }

          //Carriers
          servicefee.carriers = new List<Carr>();
          //ALSO HERE AND ADDITIONAL QUERY IS MADE
          foreach (var carrierdto in servicefeedto.ServiceFeeCarrier)
          {
            var carrier = new Carr();
            carrier.id = carrierdto.AirlineId.ToString();
            servicefee.carriers.Add(carrier);
           }


      pagedservicefees.servicefees.Add(servicefee);
   }
   //.......
   //SOME MORE CHILD ENTITIES
   //


return Json(pagedservicefees, JsonRequestBehavior.DenyGet);

}

so exactly is happening? By using the 'Include' you suppose to load related entities in one go but it's not happening isn't very clear and descriptive to us. — Grizzly
– Grizzly, Commented Aug 23, 2016 at 12:33
@BviLLe_Kid It seems like the includes are ignored. The child entities are fetched from db with additional queries the time these are accessed as properties. I' ve used comments in my code to show what is happening. — fdhsdrdark
– fdhsdrdark, Commented Aug 23, 2016 at 13:10
Again, It seems like the includes are ignored, please ensure that this in fact is happening. I don't mean to be a stickler, but it will be easier to diagnose once some possibilities can be eliminated. Have you debugged? — Grizzly
– Grizzly, Commented Aug 23, 2016 at 13:17
@BviLLe_Kid Yes, I have debugged,I used the SQL profiler to see the generated SQL from EF. The Query.ToList() statement is not sending the full query to the db. It misses the joins. — fdhsdrdark
– fdhsdrdark, Commented Aug 23, 2016 at 13:25
hmm just as a test.. try doing the Include's before the Skip and Take — Grizzly
– Grizzly, Commented Aug 23, 2016 at 13:50

fdhsdrdark · Accepted Answer · 2016-08-24 07:28:40Z

2

OK, I figured it out. It turns out that it does matter where you place the Include statements. I placed the Include statements before the .AsExpandable() and after the parent entity and now eager loading is performed. I can also verify with SQL profiler, the query contains all the necessary joins and it's executed really fast. The correct query is now:

var Query = from P in flightsPricingRulesContext.ServiceFee
.Include(sf =>   sf.ServiceFeeCarrier)
.Include(sf=>sf.ServiceFeeAssignment)
.Include(sf => sf.ServiceFeeClassOfService)
.Include(sf => sf.ServiceFeeDate)
.Include(sf => sf.ServiceFeeMarkUpAssignment)
.Include(sf => sf.ServiceFeeZone.Select(zo => zo.Zone))
.AsExpandable().Where(filter) select P;

Posting the answer in case someone encounters a same scenario.

answered Aug 24, 2016 at 7:28

fdhsdrdark

1642 silver badges13 bronze badges

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

Collectives™ on Stack Overflow

Entity Framework Eager Loading not working

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related