Entity Framework Large Query - split into compiled subqueries?

Question

I've got an ASP .NET web forms application that collects information to eventually give a calculated premium for a car insurance quote.

I've got about 10 screens in which we populate our root CarRisk object, this has properties such as CarRisk.Proposer, CarRisk.AdditionalDrivers & CarRisk.CarRiskVehicle.

Anyway, I've got a problem when I get to the quote summary screen (summarises the data entered in the previous pages). As each driver can have claims/convictions/medical conditions and each of these has another relationship to claimType/convictionType etc the query is very large.

I'm managing to eager load all the quote screens up to summary using CompiledQuery but when I get to the summary EF fails when trying to eager load the CarRisk as it has 53 Includes. If I try to use compiled query, the query won't even compile let alone run, it just seems to cause IIS to hang! I get the feeling that when I last did this with less includes (maybe 25) I get a SQL server error about too many tables being used in the query. I've tried combining the results of the compiled queries into one carRisk but I get an error when trying to set something like CarRisk.CarRiskVehicle = carRiskCarRiskVehicleCompiledQuery.CarRiskVehicle but I get the error "The EntityCollection has already been initialized. The InitializeRelatedCollection method should only be called to initialize a new EntityCollection during deserialization of an object graph.".

So I've reverted to lazy loading but it's alot slower and the client's unhappy about the performance hit. I've tried turning off ChangeTracking whilst LazyLoading but can't say it's a massive improvement.

Any suggestions/ideas on what I should do?

I'll show you the includes below so you can see

ent.CarRisks

                                        .Include("BusinessSource")          // Risk Includes
                                        .Include("PreviousInsuranceDetail")
                                        .Include("Quote.QuoteStatus")
                                        .Include("ClassOfUse")  // CarRisk Includes
                                        .Include("CoverType")
                                        .Include("ReferralSource")
                                        .Include("MainDriver")
                                        .Include("VoluntaryExcess")
                                        .Include("UserSpecifiedNumberOfDrivers")
                                        .Include("Proposer.Address")           // Proposer Includes
                                        .Include("Proposer.NumberOfOtherVehiclesAvailable")
                                        .Include("Proposer.OwnersClub")
                                        .Include("Proposer.BusinessCategory")         // CarDriver Includes
                                        .Include("Proposer.BusinessCategory2")
                                        .Include("Proposer.EmploymentStatus")
                                        .Include("Proposer.EmploymentStatus2")
                                        .Include("Proposer.Gender")
                                        .Include("Proposer.LicenceType")
                                        .Include("Proposer.MaritalStatus")
                                        .Include("Proposer.Occupation")
                                        .Include("Proposer.Occupation2")
                                        .Include("Proposer.Title")                                           
                                        .Include("Proposer.Claims.ClaimStatus")
                                        .Include("Proposer.Claims.ClaimType")
                                        .Include("Proposer.Convictions.ConvictionCode")
                                        .Include("Proposer.Convictions.ConvictionTestMethod")
                                        .Include("AdditionalDrivers.RelationshipToPolicyHolder")
                                        .Include("AdditionalDrivers.BusinessCategory")       // CarDriver Includes
                                        .Include("AdditionalDrivers.BusinessCategory2")
                                        .Include("AdditionalDrivers.EmploymentStatus")
                                        .Include("AdditionalDrivers.EmploymentStatus2")
                                        .Include("AdditionalDrivers.Gender")
                                        .Include("AdditionalDrivers.LicenceType")
                                        .Include("AdditionalDrivers.MaritalStatus")
                                        .Include("AdditionalDrivers.Occupation")
                                        .Include("AdditionalDrivers.Occupation2")
                                        .Include("AdditionalDrivers.Title")
                                        .Include("AdditionalDrivers.Claims.ClaimStatus")
                                        .Include("AdditionalDrivers.Claims.ClaimType")
                                        .Include("AdditionalDrivers.Convictions.ConvictionCode")
                                        .Include("AdditionalDrivers.Convictions.ConvictionTestMethod")
                                        .Include("CarRiskVehicle.Car")
                                        .Include("CarRiskVehicle.OvernightParkLocation")
                                        .Include("CarRiskVehicle.RegisteredKeeper")
                                        .Include("CarRiskVehicle.RegisteredOwner")
                                        .Include("CarRiskVehicle.Transmission")
                                        .Include("CarRiskVehicle.Modifications")
                                        .Include("CarRiskVehicle.CarRiskVehicleSecurityDevices")
                                        .Include("CarRiskVehicle.MotorHomeType")
                                        .Include("CarRiskVehicle.AlarmType")
                                        .Include("CarRiskVehicle.TrackerType")
                                        .Include("CarRiskVehicle.Address")

Do you need to show all properties of all the included entities on your summary screen? If not can't you use a projection which collects only the properties you need to view to possibly improve the performance? — Slauma
We don't need every property on all of them. Most of them are actually defined lists which we just need the name value for so in theory we could do this. The problem though is that the above query won't even load up! — Rick Blyth

Ladislav Mrnka Ladislav Mrnka · Accepted Answer · 2011-08-11T20:08:54

This is insane!!! Definitely return to application architecture and think again. Believe me: you don't need all these includes in single query. Divide the query or use projections.

The error you got happens if your entity is proxied for lazy loading (POCO) or if perhaps it is derived from EntityObject. Try to turn off lazy loading (objectContext.ContextOptions) before you load these entities.

Entity Framework Large Query - split into compiled subqueries?

1 Answers