Entity Framework Eager Loading not working

c# eager-loading entity-framework entity-framework-6

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);

}                
1
1
8/23/2016 12:04:51 PM

Popular Answer

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.

2
8/24/2016 7:28:40 AM


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