LINQ-Abfrage langsamer nach der Migration von .net 4.8 auf .net 6

Ich habe meine.net Framework 4.8 EF6 mvc-Web-App auf.net6 EF Core migriert. Überraschenderweise scheinen LINQ-Abfragen in der migrierten App langsamer ausgeführt zu werden. Ich habe auf meinem IIS-Server und auf meinem lokalen Computer getestet und im Debug-Modus einen Bremspunkt auf die Abfrage gesetzt. das ist die abfrage:

List<Lead> RelevantLeads = (from l in db.Leads.Include("Campaign")
   where l.InsertionDateTime > fromDateMonths
   && l.Campaign.Provider.Company.CompanyID == companyId
   && l.Status == null
   && (l.User == null || l.UserID == user.UserID)
   && l.isSentToDialer == false
   select l).OrderBy(l => l.InsertionDateTime).ToList();

Die Migration erforderte umfangreiche Arbeit, bei der auch einige Änderungen vorgenommen wurden, um das Modell der DB zuzuordnen. Wir setzen einige Attribute auf die Eigenschaften, um sie korrekt der entsprechenden Spalte in der DB zuzuordnen. Das Modell entspricht dem DB und gibt die korrekten Ergebnisse zurück.

Beispiel:

[Column("LeadType_LeadTypeID")]

Wenn Sie eine Idee haben, was zu solch seltsamem Verhalten führen könnte, bin ich für Ihren Beitrag dankbar!

Nach der Überwachung des SQL-Servers stellte ich fest, dass die generierten SQL-Abfragen unterschiedlich waren. obwohl beide das gleiche Ergebnis liefern - die, die aus der alten Version generiert wurde, dauerte 4 Sekunden, während die neuere 8 Sekunden dauerte.

Bitte sehen Sie sich die SQL-Abfragen an. Aus neuem Code:

SELECT [l].[LeadID], [l].[Age], [l].[BecameClientDateTime], [l].[CRMLeadID], [l].[CampaignID], [l].[City], [l].[ClickId], [l].[Company_CompanyID], [l].[Country], [l].[Discriminator], [l].[Email], [l].[EmpResponseDifference], [l].[FirstName], [l].[ForwardToEmpDateTime], [l].[ForwardToEmpDifference], [l].[GeneralField1], [l].[GeneralField2], [l].[IdentificationNumber], [l].[InsertionDateTime], [l].[InterfacedSystemLeadPK], [l].[LastName], [l].[LastUpdateDateTime], [l].[LeadOpenedFirstTime], [l].[LeadTypeID], [l].[Location], [l].[MobilePhone], [l].[NotesFromLeadProvider], [l].[NotesToLeadProvider], [l].[Provider_ProviderID], [l].[ProviderLeadPK], [l].[SecondaryPhone], [l].[State], [l].[StatusID], [l].[StreetAddress], [l].[UserID], [l].[ZIPCode], [l].[isClient], [l].[isDNC], [l].[isMeetingHeld], [l].[isMeetingSet], [l].[isQuote], [l].[isQuoteViewed], [l].[isSentToCRM], [l].[isSentToDialer], [l].[isSuccessfullyInterfaced], [l].[utm_campaign], [l].[utm_content], [l].[utm_medium], [l].[utm_source], [l].[utm_term], [l].[AskingAmount], [l].[BusinessName], [l].[CreditCardSales], [l].[MonthlyGross], [l].[YearInBusiness], [l].[AgeOfYoungestDriver], [l].[Block], [l].[CarInsuranceType], [l].[CarRegistrationNumber], [l].[Experience], [l].[InjuryClaims], [l].[InsuranceStartDate], [l].[KmPerYear], [l].[LeviCode], [l].[LicenseRevocation], [l].[Manufacturer], [l].[Model], [l].[NumberOfClaims], [l].[Owner], [l].[PastInsurance], [l].[PermissionGroup], [l].[PolicyNumber], [l].[PropertyClaims], [l].[Riders], [l].[Usage], [l].[VehicleType], [l].[Year], [l].[lien], [l].[AppraisersName], [l].[ClaimNumber], [l].[DateOfIncident], [l].[GarageName], [l].[RegistrationNumber], [l].[ThirdPartyInsuranceCompany], [l].[Floor], [l].[HomeInsuranceType], [l].[SumContent], [l].[SumStructure], [l].[Sum], [l].[EmploymentStatus], [l].[Income], [l].[LoanPurpose], [l].[LoanRequestAmount], [l].[isChecksReturned], [l].[isCreditCard], [l].[LoanAmount], [l].[MortgageStartIn], [l].[Phase], [l].[CurrentInsuranceCompany], [l].[Floor1], [l].[MortgageInsuranceType], [l].[SquareMeters], [l].[StructureType], [c].[CampaignID], [c].[CampaignDescription], [c].[CampaignName], [c].[CostPerCampaign], [c].[CostPerLead], [c].[DID], [c].[DialerCampaign], [c].[DiallerRank], [c].[IsCheckInDNCListOnLeadInsertion], [c].[IsDeleteFromDNCListOnLeadInsertion], [c].[IsExtraDuplicateCheck], [c].[IsNewLeadDistribiuteFromDashboard], [c].[IsSpecificDuplicateCheck], [c].[IsSyncFromCRM], [c].[LeadType_LeadTypeID], [c].[NumOfDaysToCheckForDuplicates], [c].[ProviderID], [c].[ReferenceCampaignID], [c].[isAutoDialerOut], [c].[isTrigerInterfaceEnterence]
 FROM [Leads] AS [l]
 INNER JOIN [Campaigns] AS [c] ON [l].[CampaignID] = [c].[CampaignID]
 INNER JOIN [Providers] AS [p] ON [c].[ProviderID] = [p].[ProviderID]
 LEFT JOIN [Companies] AS [c0] ON [p].[Company_CompanyID] = [c0].[CompanyID]
 LEFT JOIN [Status] AS [s] ON [l].[StatusID] = [s].[StatusID]
 LEFT JOIN [Users] AS [u] ON [l].[UserID] = [u].[UserID]
 WHERE [l].[InsertionDateTime] > @__fromDateMonths_0 AND [c0].[CompanyID] = @__companyId_1 AND [s].[StatusID] IS NULL AND ([u].[UserID] IS NULL OR ([l].[UserID] = @__user_UserID_2)) AND [l].[isSentToDialer] = CAST(0 AS bit)
 ORDER BY [l].[InsertionDateTime]

Aus altem Code:

SELECT 
[Project1].[LeadID] AS [LeadID], 
[Project1].[Discriminator] AS [Discriminator], 
[Project1].[LeadTypeID] AS [LeadTypeID], 
[Project1].[CampaignID] AS [CampaignID], 
[Project1].[ProviderLeadPK] AS [ProviderLeadPK], 
[Project1].[InsertionDateTime] AS [InsertionDateTime], 
[Project1].[FirstName] AS [FirstName], 
[Project1].[LastName] AS [LastName], 
[Project1].[MobilePhone] AS [MobilePhone], 
...
[Project1].[Provider_ProviderID] AS [Provider_ProviderID], 
[Project1].[Company_CompanyID] AS [Company_CompanyID]
FROM ( SELECT 
 [Filter1].[LeadID] AS [LeadID], 
 [Filter1].[LeadTypeID] AS [LeadTypeID], 
 [Filter1].[CampaignID1] AS [CampaignID], 
... 
 [Filter1].[Company_CompanyID1] AS [Company_CompanyID], 
 [Extent4].[CampaignID] AS [CampaignID1], 
... 
 [Extent4].[ReferenceCampaignID] AS [ReferenceCampaignID], 
 [Extent4].[IsSyncFromCRM] AS [IsSyncFromCRM], 
 [Extent4].[LeadType_LeadTypeID] AS [LeadType_LeadTypeID]
 FROM (SELECT [Extent1].[LeadID] AS [LeadID], [Extent1].[LeadTypeID] AS [LeadTypeID], [Extent1].[CampaignID] AS [CampaignID1], [Extent1].[ProviderLeadPK] AS [ProviderLeadPK], [Extent1].[InsertionDateTime] AS [InsertionDateTime], [Extent1].[FirstName] AS [FirstName], [Extent1].[LastName] AS [LastName], [Extent1].[MobilePhone] AS [MobilePhone], [Extent1].[SecondaryPhone] AS [SecondaryPhone], [Extent1].[Email] AS [Email], [Extent1].[Location] AS [Location], [Extent1].[StreetAddress] AS [StreetAddress], [Extent1].[City] AS [City], [Extent1].[State] AS [State], [Extent1].[ZIPCode] AS [ZIPCode], [Extent1].[Country] AS [Country], [Extent1].[IdentificationNumber] AS [IdentificationNumber], [Extent1].[Age] AS [Age], [Extent1].[StatusID] AS [StatusID], [Extent1].[UserID] AS [UserID], [Extent1].[ForwardToEmpDateTime] AS [ForwardToEmpDateTime], [Extent1].[ForwardToEmpDifference] AS [ForwardToEmpDifference], [Extent1].[LeadOpenedFirstTime] AS [LeadOpenedFirstTime], [Extent1].[EmpResponseDifference] AS [EmpResponseDifference], [Extent1].[isClient] AS [isClient], [Extent1].[isMeetingSet] AS [isMeetingSet], [Extent1].[isMeetingHeld] AS [isMeetingHeld], [Extent1].[NotesToLeadProvider] AS [NotesToLeadProvider], [Extent1].[NotesFromLeadProvider] AS [NotesFromLeadProvider], [Extent1].[isSuccessfullyInterfaced] AS [isSuccessfullyInterfaced], [Extent1].[InterfacedSystemLeadPK] AS [InterfacedSystemLeadPK], [Extent1].[utm_source] AS [utm_source], [Extent1].[utm_medium] AS [utm_medium], [Extent1].[utm_term] AS [utm_term], [Extent1].[utm_content] AS [utm_content], [Extent1].[utm_campaign] AS [utm_campaign], [Extent1].[ClickId] AS [ClickId], [Extent1].[LastUpdateDateTime] AS [LastUpdateDateTime], [Extent1].[BecameClientDateTime] AS [BecameClientDateTime], [Extent1].[isQuote] AS [isQuote], [Extent1].[isDNC] AS [isDNC], [Extent1].[isSentToDialer] AS [isSentToDialer], [Extent1].[isSentToCRM] AS [isSentToCRM], [Extent1].[CRMLeadID] AS [CRMLeadID], [Extent1].[isQuoteViewed] AS [isQuoteViewed], [Extent1].[GeneralField1] AS [GeneralField1], [Extent1].[GeneralField2] AS [GeneralField2], [Extent1].[BusinessName] AS [BusinessName], [Extent1].[YearInBusiness] AS [YearInBusiness], [Extent1].[MonthlyGross] AS [MonthlyGross], [Extent1].[CreditCardSales] AS [CreditCardSales], [Extent1].[AskingAmount] AS [AskingAmount], [Extent1].[AgeOfYoungestDriver] AS [AgeOfYoungestDriver], [Extent1].[CarInsuranceType] AS [CarInsuranceType], [Extent1].[Manufacturer] AS [Manufacturer], [Extent1].[Model] AS [Model], [Extent1].[Year] AS [Year], [Extent1].[NumberOfClaims] AS [NumberOfClaims], [Extent1].[Experience] AS [Experience], [Extent1].[LicenseRevocation] AS [LicenseRevocation], [Extent1].[InsuranceStartDate] AS [InsuranceStartDate], [Extent1].[VehicleType] AS [VehicleType], [Extent1].[PolicyNumber] AS [PolicyNumber], [Extent1].[PermissionGroup] AS [PermissionGroup], [Extent1].[PastInsurance] AS [PastInsurance], [Extent1].[PropertyClaims] AS [PropertyClaims], [Extent1].[InjuryClaims] AS [InjuryClaims], [Extent1].[LeviCode] AS [LeviCode], [Extent1].[KmPerYear] AS [KmPerYear], [Extent1].[Block] AS [Block], [Extent1].[Owner] AS [Owner], [Extent1].[Usage] AS [Usage], [Extent1].[lien] AS [lien], [Extent1].[Riders] AS [Riders], [Extent1].[CarRegistrationNumber] AS [CarRegistrationNumber], [Extent1].[RegistrationNumber] AS [RegistrationNumber], [Extent1].[DateOfIncident] AS [DateOfIncident], [Extent1].[ClaimNumber] AS [ClaimNumber], [Extent1].[AppraisersName] AS [AppraisersName], [Extent1].[GarageName] AS [GarageName], [Extent1].[ThirdPartyInsuranceCompany] AS [ThirdPartyInsuranceCompany], [Extent1].[HomeInsuranceType] AS [HomeInsuranceType], [Extent1].[SumContent] AS [SumContent], [Extent1].[SumStructure] AS [SumStructure], [Extent1].[Floor] AS [Floor], [Extent1].[Sum] AS [Sum], [Extent1].[LoanRequestAmount] AS [LoanRequestAmount], [Extent1].[LoanPurpose] AS [LoanPurpose], [Extent1].[EmploymentStatus] AS [EmploymentStatus], [Extent1].[Income] AS [Income], [Extent1].[isCreditCard] AS [isCreditCard], [Extent1].[isChecksReturned] AS [isChecksReturned], [Extent1].[LoanAmount] AS [LoanAmount], [Extent1].[Phase] AS [Phase], [Extent1].[MortgageStartIn] AS [MortgageStartIn], [Extent1].[MortgageInsuranceType] AS [MortgageInsuranceType], [Extent1].[StructureType] AS [StructureType], [Extent1].[Floor1] AS [Floor1], [Extent1].[SquareMeters] AS [SquareMeters], [Extent1].[CurrentInsuranceCompany] AS [CurrentInsuranceCompany], [Extent1].[Discriminator] AS [Discriminator], [Extent1].[Provider_ProviderID] AS [Provider_ProviderID], [Extent1].[Company_CompanyID] AS [Company_CompanyID1], [Extent3].[Company_CompanyID] AS [Company_CompanyID2]
 FROM [dbo].[Leads] AS [Extent1]
 INNER JOIN [dbo].[Campaigns] AS [Extent2] ON [Extent1].[CampaignID] = [Extent2].[CampaignID]
 INNER JOIN [dbo].[Providers] AS [Extent3] ON [Extent2].[ProviderID] = [Extent3].[ProviderID]
 WHERE ([Extent1].[Discriminator] IN (N'BasicLead',N'BusinessLoanLead',N'CarInsuranceLead_He',N'ClaimLead_He',N'HomeInsuranceLead_He',N'InvestmentLead',N'LoanLead_He',N'MortgageLead_He',N'MortgageInsuranceLead_He',N'Lead')) AND (0 = [Extent1].[isSentToDialer]) AND ([Extent1].[StatusID] IS NULL) ) AS [Filter1]
 LEFT OUTER JOIN [dbo].[Campaigns] AS [Extent4] ON [Filter1].[CampaignID1] = [Extent4].[CampaignID]
 WHERE ([Filter1].[InsertionDateTime] > @p__linq__0) AND ([Filter1].[Company_CompanyID2] = @p__linq__1) AND (([Filter1].[UserID] IS NULL) OR ([Filter1].[UserID] = @p__linq__2) OR (([Filter1].[UserID] IS NULL) AND (@p__linq__2 IS NULL)))
) AS [Project1]
ORDER BY [Project1].[InsertionDateTime] ASC

Lösung des Problems

Ohne die Modelle zu sehen, ist es etwas schwierig zu helfen.

Es sieht jedoch so aus, als ob das Upgrade auf EF Core 6 zwei linke Verknüpfungen hinzugefügt hat: Status und Benutzer.

Möglicherweise können Sie sie vermeiden, indem Sie die Verweise auf l.Status und l.User in ihre jeweiligen IDs ändern. Beispiel:

List<Lead> RelevantLeads = (from l in db.Leads.Include("Campaign")
  where l.InsertionDateTime > fromDateMonths
  && l.Campaign.Provider.Company.CompanyID == companyId
  && l.StatusID == null // from l.Status
  && (l.UserID == null || l.UserID == user.UserID) // from l.User
  && l.isSentToDialer == false
  select l).OrderBy(l => l.InsertionDateTime).ToList();