none
Entity Framework query issue RRS feed

  • Question

  • Hi, currently I'm building an web app that uses ado.net entity framework 4.0 and I've come across the following issue.

     

    When executing the following query

     

     

    var users = Model.Get<SecurityUser>();

                var c1 = Model.Get<Country>();

                var cl = Model.Get<CountryLocalization>();

                var countries = from country in c1

                                join

                                    countryLocalization in cl on country.ID equals countryLocalization.MainID

                                where countryLocalization.CultureID == 1

                                select new { Country = country, Localization = countryLocalization };

                var r1 = Model.Get<Region>();

                var rl = Model.Get<RegionLocalization>();

     

                var regions = from region in r1

                              join regionLocalization in rl on region.ID equals regionLocalization.MainID

                              where regionLocalization.CultureID == 1

                              select new { Region = region, Localization = regionLocalization };

                var cu1 = Model.Get<Culture>();

                var cul = Model.Get<CultureLocalization>();

     

                var cultures = from culture in cu1

                               join cultureLocalization in cul on culture.ID equals cultureLocalization.MainID

                               where cultureLocalization.CultureID == 1

                               select new { Culture = culture, Localization = cultureLocalization };

                var lu = Model.Get<LookupOption>();

                var lul = Model.Get<LookupOptionLocalization>();

     

                var lookups = from lookup in lu

                              join lookupLoc in lul on lookup.ID equals lookupLoc.MainID

                              where lookupLoc.CultureID == 1

                              select new { LookupOption = lookup, LookupOptionLocalization = lookupLoc };

                var contacts = Model.Get<Contact>();

                var organizations = Model.Get<Organization>();

     

                var x =

                    from o in organizations

     

                    join c in contacts

                        on o.ReferenceContactID equals c.ID into nullableReferenceContacts

                    from c in nullableReferenceContacts.DefaultIfEmpty()

     

                    join lc1 in countries

                        on o.BillingCountryID equals lc1.Country.ID into nullableBillingCountries

                    from lc1 in nullableBillingCountries.DefaultIfEmpty()

     

                    join lc2 in countries

                        on o.ShippingCountryID equals lc2.Country.ID into nullableShippingCountries

                    from lc2 in nullableShippingCountries.DefaultIfEmpty()

     

                    join p in organizations

                        on o.ParentOrganizationID equals p.ID into nullableParents

                    from p in nullableParents.DefaultIfEmpty()

     

                    join lr1 in regions

                        on o.BillingCityID equals lr1.Region.ID into nullableBillingCities

                    from lr1 in nullableBillingCities.DefaultIfEmpty()

     

                    join lr2 in regions

                        on o.BillingStateID equals lr2.Region.ID into nullableBillingStates

                    from lr2 in nullableBillingStates.DefaultIfEmpty()

     

                    join lr3 in regions

                        on o.ShippingCityID equals lr3.Region.ID into nullableShippingCities

                    from lr3 in nullableShippingCities.DefaultIfEmpty()

     

                    join lr in regions

                        on o.ShippingStateID equals lr.Region.ID into nullableShippingStates

                    from lr in nullableShippingStates.DefaultIfEmpty()

     

                    join lc in cultures

                        on o.CultureID equals lc.Culture.ID into nullableCultures

                    from lc in nullableCultures.DefaultIfEmpty()

     

                    join llo1 in lookups

                        on o.CategoryID equals llo1.LookupOption.ID into nullableCategories

                    from llo1 in nullableCategories.DefaultIfEmpty()

     

                    join llo2 in lookups

                        on o.ClassID equals llo2.LookupOption.ID into nullableOrganizationClasses

                    from llo2 in nullableOrganizationClasses.DefaultIfEmpty()

     

                    join llo3 in lookups

                        on o.CustomerPriorityID equals llo3.LookupOption.ID into nullableCustomerPriorities

                    from llo3 in nullableCustomerPriorities.DefaultIfEmpty()

     

                    join llo4 in lookups

                        on o.CustomLookupID1 equals llo4.LookupOption.ID into nullableCustomLookupID1s

                    from llo4 in nullableCustomLookupID1s.DefaultIfEmpty()

     

                    join llo5 in lookups

                        on o.CustomLookupID2 equals llo5.LookupOption.ID into nullableCustomLookupID2s

                    from llo5 in nullableCustomLookupID2s.DefaultIfEmpty()

     

                    join llo6 in lookups

                        on o.CustomLookupID3 equals llo6.LookupOption.ID into nullableCustomLookupID3s

                    from llo6 in nullableCustomLookupID3s.DefaultIfEmpty()

     

                    join llo7 in lookups

                        on o.CustomLookupID4 equals llo7.LookupOption.ID into nullableCustomLookupID4s

                    from llo7 in nullableCustomLookupID4s.DefaultIfEmpty()

     

                    join llo8 in lookups

                        on o.CustomLookupID5 equals llo8.LookupOption.ID into nullableCustomLookupID5s

                    from llo8 in nullableCustomLookupID5s.DefaultIfEmpty()

     

                    join llo9 in lookups

                        on o.IndustryID equals llo9.LookupOption.ID into nullableIndustries

                    from llo9 in nullableIndustries.DefaultIfEmpty()

     

                    join llo10 in lookups

                        on o.OwnershipID equals llo10.LookupOption.ID into nullableOrganizationOwnerships

                    from llo10 in nullableOrganizationOwnerships.DefaultIfEmpty()

     

                    join llo11 in lookups

                        on o.PaymentTermsID equals llo11.LookupOption.ID into nullablePaymentTermsList

                    from llo11 in nullablePaymentTermsList.DefaultIfEmpty()

     

                    join llo12 in lookups

                        on o.RatingID equals llo12.LookupOption.ID into nullableOrganizationRatings

                    from llo12 in nullableOrganizationRatings.DefaultIfEmpty()

     

                    join llo13 in lookups

                        on o.ShippingMethodID equals llo13.LookupOption.ID into nullableShippingMethods

                    from llo13 in nullableShippingMethods.DefaultIfEmpty()

     

                    join llo14 in lookups

                        on o.TaxScheduleID equals llo14.LookupOption.ID into nullableTaxSchedules

                    from llo14 in nullableTaxSchedules.DefaultIfEmpty()

     

                    join llo15 in lookups

                        on o.TimeZoneID equals llo15.LookupOption.ID into nullableTimeZones

                    from llo15 in nullableTimeZones.DefaultIfEmpty()

     

                    join llo16 in lookups

                        on o.UpsellOpportunityID equals llo16.LookupOption.ID into nullableUpsellOpportunities

                    from llo16 in nullableUpsellOpportunities.DefaultIfEmpty()

     

                    join u1 in users

                        on o.CreatedByUserID equals u1.ID into nullableCreatedByUsers

                    from u1 in nullableCreatedByUsers.DefaultIfEmpty()

     

                    join u2 in users

                        on o.ModifiedByUserID equals u2.ID into nullableModifiedByUsers

                    from u2 in nullableModifiedByUsers.DefaultIfEmpty()

     

                    join u3 in users

                        on o.OwnerID equals u3.ID into nullableUsers

                    from u3 in nullableUsers.DefaultIfEmpty()

     

                    select

                       new LocalizedOrganization

                       {

                           Organization = o,

                           ReferenceContact = c,

                           BillingCountry = lc1.Localization,

                           ShippingCountry = lc2.Localization,

                           ParentOrganization = p,

                           BillingCity = lr1.Localization,

                           BillingState = lr2.Localization,

                           ShippingCity = lr3.Localization,

                           ShippingState = lr.Localization,

                           Culture = lc.Localization,

                           Category = llo1.LookupOptionLocalization,

                           Class = llo2.LookupOptionLocalization,

                           CustomerPriority = llo3.LookupOptionLocalization,

                           CustomLookup1 = llo4.LookupOptionLocalization,

                           CustomLookup2 = llo5.LookupOptionLocalization,

                           CustomLookup3 = llo6.LookupOptionLocalization,

                           CustomLookup4 = llo7.LookupOptionLocalization,

                           CustomLookup5 = llo8.LookupOptionLocalization,

                           Industry = llo9.LookupOptionLocalization,

                           Ownership = llo10.LookupOptionLocalization,

                           PaymentTerms = llo11.LookupOptionLocalization,

                           Rating = llo12.LookupOptionLocalization,

                           ShippingMethod = llo13.LookupOptionLocalization,

                           TaxSchedule = llo14.LookupOptionLocalization,

                           TimeZone = llo15.LookupOptionLocalization,

                           UpsellOpportunity = llo16.LookupOptionLocalization,

                           CreatedBy = u1,

                           ModifiedBy = u2,

                           Owner = u3

                       };

                var localizedOrganizations = x.Take(100).ToList();

     

     

    it takes about 2.8 minutes for it to execute 98% of which it generates the SQL query.

    The query caught via the SQL performance profiler is almost instant and 

    executing var traceString = ((ObjectQuery)x).ToTraceString();

    takes approximately the same time it would take to enumerate the query.

    Do you have any clue what might cause this huge execution time, maybe the way the query is written or something?

     

    Thanks !

    Tuesday, September 14, 2010 8:27 PM

Answers

All replies

  • 1) Try pre-generating views: http://msdn.microsoft.com/en-us/library/bb896240.aspx

    2) Alternatively, a good portion of that query could be done as a new CSDL entity mapped to all the underlying SSDL entities. Not sure if that will help performance, but worth a try if (1) doesn't help.


     
       Cool tools for Linq-to-SQL and Entity Framework 4:
     huagati.com/dbmltools - Rule based class and property naming, Compare and Sync model <=> DB, Sync SSDL <=> CSDL (EF4)
     huagati.com/L2SProfiler - Query profiler for Linq-to-SQL and Entity Framework v4
    • Proposed as answer by KristoferA Monday, September 20, 2010 9:42 AM
    • Marked as answer by liurong luo Tuesday, September 21, 2010 8:08 AM
    Wednesday, September 15, 2010 2:03 AM
  • Thanks Kristofer.

     

    I've tried option 1 which didn't help.  I'll give a try to the second option. But the issue looks very bad since I can find no other problem than entity framework itself and it really scares me.

    Monday, September 20, 2010 9:04 PM
  • If the performance of the SQL query translation is the problem, maybe you can use execute the SQL directly against the source and translate the result back to EF.

    Here is how you can execute SQL using EF:

    http://blogs.msdn.com/b/alexj/archive/2009/11/07/tip-41-how-to-execute-t-sql-directly-against-the-database.aspx

    Then, you can translate the result back into EF by ObjectContext<T>.Translate method.
    Let me know how this works out.

    • Marked as answer by liurong luo Tuesday, September 21, 2010 8:08 AM
    Monday, September 20, 2010 9:22 PM
  • The official MS response to the issue was that there's a "known" problem of translating left joins from linq to sql.

    My advise is to be very careful when using EF since it's a buggy product.

    Wednesday, June 8, 2011 4:26 PM