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, Aug 11 '11 at 18:38
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, Aug 12 '11 at 08:28

score 4 · Answer 1 · edited May 23 '17 at 12:04

4

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.

edited May 23 '17 at 12:04

Community

1
1

answered Aug 11 '11 at 20:08

Ladislav Mrnka

360,892
59
660
670

How rude, Why is it insane!?! Alot of them are defined list values or enums which aren't supported in EF 4.0. How would you go about displaying a summary page which requires all this information. Claims/Convictions/Medical Conditions are all reused objects but have to be included in the query to be shown on the summary screen. – Rick Blyth Aug 12 '11 at 08:25
Also, we're not using POCO FYI. – Rick Blyth Aug 12 '11 at 08:25
4

It is not rude. Simply turn on SQL Profiler and you will immediately see how wrong it is. If you are not able to get so far to execute SQL convert the resulting query to `ObjectQuery` and call `ToTraceString` to see the SQL. EntityObjects should have automatic navigation properties fixup. Load entities by separate queries and if you are lucky navigation properties on the main entity will be simply filled without need to manually assign them. – Ladislav Mrnka Aug 12 '11 at 08:35
Each `Include` represent several joins and sometimes also sub query. Such understanding should tell you that the query is insane. Even reporting applications doesn't use such queries. – Ladislav Mrnka Aug 12 '11 at 08:37

Entity Framework Large Query - split into compiled subqueries?

1 Answers1