Entity Framework Eager Loading not working

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

Question

I want to use Entity Framework 6 and Eager loading to load a list of objects from a database. Entity Framework, however, makes advantage of lazy loading. The queries are run when a property referencing to the child entities is accessed, according to SQL profiler I used. When using the "Include" command, you should be able to load all associated entities at once, however this isn't occurring. The following describes my code:

    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 got it worked out. It seems that the placement of the Include statements does important. The Include statements came first in my order. After the parent entity, AsExpandable() is called, and eager loading is now being done. Using SQL Profiler, I can confirm that the query has all the required joins and executes quickly. The proper question 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 solution in case someone else runs into the same problem.

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