Efcore: v3.0 Query Translation Error for SqlServer provider?

[SimonOrdo]

I have a fairly lengthy EF query that was previously working fine before the 3.0 update. It's essentially a query to load a user and all related rows.

Since 3.0, this query now returns ~50k duplicate rows and takes over 4 minutes to execute.

The .NET code is

    // Get the user and their life story.
    var userQuery =
        DataContext.Users
        .Include(x => x.LeProjectAsset)
        .Include(x => x.LeProjectCoursesEnrollmentStudent).ThenInclude(x => x.Course)
        .Include(x => x.LeProjectCoursesEnrollmentStudent).ThenInclude(x => x.LeProjectCoursesFootprints)
        .Include(x => x.LeProjectCorporateAccountLastEditByNavigation)
        .Include(x => x.LeProjectPaymentsOrdersUser).ThenInclude(y => y.LeProjectPaymentsOrderItem).ThenInclude(x => x.Item)
        .Include(x => x.LeProjectPaymentsOrdersUser).ThenInclude(y => y.ResultCodeNavigation)
        .Include(x => x.LeProjectPaymentsOrdersUser).ThenInclude(y => y.InverseSettlementOrder)
        .Include(x => x.LeProjectPaymentsOrdersUser).ThenInclude(y => y.LeProjectPaymentsOrderRefunds).ThenInclude(z => z.AuthorizedByNavigation)
        .Include(x => x.LeProjectPaymentsOrdersUser).ThenInclude(y => y.MerchantAccount)
        .Include(x => x.LeProjectPaymentsOrdersUser).ThenInclude(y => y.CreditCard).ThenInclude(x => x.CorporateAccount)
        .Include(x => x.LeProjectPaymentsOrdersUser).ThenInclude(y => y.BillingInfo).ThenInclude(x => x.CorporateAccount)
        .Include(x => x.LeProjectPaymentsBillingInfoCustomer)
        .Include(x => x.UserManagementGroupNavigation)
        .Include(x => x.LeProject_Registration_FieldValues).ThenInclude(y => y.Field)
        .Include(x => x.Roles)
        .Include(x => x.MyPortal).ThenInclude(z => z.LeProjectCorporateAccountPortalMap).ThenInclude(s => s.CorporateAccount)
        .Include(x => x.MyPortal).ThenInclude(z => z.LeProject_Portal_URL)                        
        .Include(x => x.MyPortal).ThenInclude(z => z.PasswordRegistrationField)
        .Include(x => x.MyPortal).ThenInclude(z => z.UsernameRegistrationField)
        .Include(x => x.MyPortal).ThenInclude(z => z.LeProject_Shop_MenuItem).ThenInclude(j => j.Package).ThenInclude(w => w.LeProject_Package_Courses).ThenInclude(s => s.Course)
        .Include(x => x.MyPortal).ThenInclude(z => z.LeProject_Shop_MenuItem).ThenInclude(h => h.LeProjectCorporateAccountOverridePricing)
        .Include(x => x.LeProject_Shop_MenuItem)
        .Where(x => x.Id == userId);

    ApplicationUser targetUser = userQuery.SingleOrDefault();

Which is translated into the following query:

SELECT [t0].[Id], [t0].[AccessFailedCount], [t0].[Address], [t0].[City], [t0].[ConcurrencyStamp], [t0].[CreatedDate], [t0].[Email], [t0].[EmailConfirmed], [t0].[FirstName], [t0].[GrantUsermanagementAccess], [t0].[LastLogin], [t0].[LastName], [t0].[LegacyUserId], [t0].[LockoutEnabled], [t0].[LockoutEnd], [t0].[LockoutEndDateUtc], [t0].[LockoutEventDate], [t0].[MiddleName], [t0].[MyPortalID], [t0].[NormalizedEmail], [t0].[NormalizedUserName], [t0].[OIGExclusionCheckDate], [t0].[OIGExclusionCheckEnabled], [t0].[PasswordHash], [t0].[PermitSelfServePrepaidCodes], [t0].[PhoneNumber], [t0].[PhoneNumberConfirmed], [t0].[Position], [t0].[ProctorAuthorizeExamRelease], [t0].[ProctorAuthorizePurchase], [t0].[SecurityStamp], [t0].[SimpleId], [t0].[State], [t0].[Suite], [t0].[TwoFactorEnabled], [t0].[UserManagementGroup], [t0].[UserManagementId], [t0].[UserName], [t0].[Zip], [t0].[ID0], [t0].[Address0], [t0].[City0], [t0].[Email0], [t0].[GroupName], [t0].[GroupType], [t0].[IsDefault], [t0].[ParentGroupId], [t0].[PhoneNumber0], [t0].[PortalId], [t0].[Position0], [t0].[State0], [t0].[Suite0], [t0].[UserManagementId0], [t0].[Zip0], [t0].[ID1], [t0].[AllowCorpAccounts], [t0].[AllowCreditCards], [t0].[AllowPayPal], [t0].[AllowPrepaidCodes], [t0].[BillingMethodology], [t0].[CanPortalAdminsCreateUsers], [t0].[Category], [t0].[DeactivationPending], [t0].[DevUseOnly], [t0].[DisplayName], [t0].[HTMLFooterCSS], [t0].[HTMLFooterView], [t0].[HTMLHeaderCSS], [t0].[HTMLHeaderView], [t0].[IsActive], [t0].[LastEditBy], [t0].[LastEditOn], [t0].[Logo], [t0].[MerchantAccountId], [t0].[PasswordRegistrationFieldId], [t0].[ScriptInjection], [t0].[SupportEmail], [t0].[SupportPhone], [t0].[URLSupportPage], [t0].[UsernameRegistrationFieldId], [t0].[UsersSelfRegister], [t0].[ViewDirectory], [t0].[ID2], [t0].[CustomRegex], [t0].[DataTypeEditor], [t0].[DefaultValues], [t0].[FieldExplanation], [t0].[FieldName], [t0].[InternalNotes], [t0].[IsRequired], [t0].[MaxLength], [t0].[MaxValue], [t0].[MinLength], [t0].[MinValue], [t0].[SearchableInTechPage], [t0].[ShowInProfile], [t0].[SortOrder], [t0].[UseCustomRegex], [t0].[UserCanEdit], [t0].[UserPrompt], [t0].[ValidationMessage], [t0].[VisibleCharactersOnBlur], [t0].[ID3], [t0].[CustomRegex0], [t0].[DataTypeEditor0], [t0].[DefaultValues0], [t0].[FieldExplanation0], [t0].[FieldName0], [t0].[InternalNotes0], [t0].[IsRequired0], [t0].[MaxLength0], [t0].[MaxValue0], [t0].[MinLength0], [t0].[MinValue0], [t0].[SearchableInTechPage0], [t0].[ShowInProfile0], [t0].[SortOrder0], [t0].[UseCustomRegex0], [t0].[UserCanEdit0], [t0].[UserPrompt0], [t0].[ValidationMessage0], [t0].[VisibleCharactersOnBlur0], [i3].[ID], [i3].[Description], [i3].[Directory], [i3].[Filename], [i3].[IsMaterial], [i3].[SizeBytes], [i3].[Title], [i3].[UploadedBy], [i3].[UploadedOn], [i3].[URL], [t2].[ID], [t2].[AuthorizedBy], [t2].[CertificateData], [t2].[CertificateNumber], [t2].[CertificateScore], [t2].[CertificateTestPassed], [t2].[CourseId], [t2].[CourseVersion], [t2].[Disposition], [t2].[DispositionDate], [t2].[EnrolledOn], [t2].[FirstLaunchDate], [t2].[OrderId], [t2].[PortalId], [t2].[Reason], [t2].[StudentId], [t2].[Uid], [t2].[ViaPackageId], [t2].[ID0], [t2].[AdminMessage], [t2].[Certificate], [t2].[CertificateExpirationTicks], [t2].[CertificatePoolID], [t2].[DataSyncIntervalMinutes], [t2].[DaysAvailableAfterCompletionTicks], [t2].[Deleted], [t2].[Description], [t2].[DevUseOnly], [t2].[Duration], [t2].[Filename], [t2].[GradingFormula], [t2].[HTMLData], [t2].[IsScorm], [t2].[LastEditBy], [t2].[LastEditOn], [t2].[LegacyCourseId], [t2].[LiveVersion], [t2].[MaxDurationSecs], [t2].[MinDurationSecs], [t2].[MinPassingScore], [t2].[Name], [t2].[RemoteSyncState], [t2].[ScormCloudPolling], [t2].[ScormLaunchFile], [t2].[ScormSurveySlides], [t2].[ScormType], [t2].[ShortUIName], [t2].[SyncId], [t2].[TotalScormVersions], [t2].[Uploaded], [t2].[UploadedBy], [t2].[VersionId], [t2].[VersioningBehavior], [t2].[ID1], [t2].[Actor], [t2].[CourseId0], [t2].[CourseVersion0], [t2].[Disposition0], [t2].[EnrollmentId], [t2].[ExecutionPlan], [t2].[Footprints], [t2].[GlobalDat], [t2].[LastSceneDisplayed], [t2].[LastUpdate], [t2].[RecordFinalized], [i7].[ID], [i7].[AccountNumber], [i7].[AccountType], [i7].[City], [i7].[CompanyName], [i7].[CreatedBy], [i7].[CreatedOn], [i7].[DeactivatedBy], [i7].[DeactivatedOn], [i7].[EmailAddress], [i7].[IsActive], [i7].[LastEditBy], [i7].[LastEditOn], [i7].[NumberOfUses], [i7].[PhoneNumber], [i7].[PrimaryContact], [i7].[State], [i7].[Street], [i7].[UnlimitedUses], [i7].[Zip], [t7].[ID], [t7].[AuthorizationNumber], [t7].[AuthorizedBy], [t7].[BillingInfoId], [t7].[CaptureReconcileId], [t7].[CheckoutBillingMethod], [t7].[CreditCardId], [t7].[Hide], [t7].[InternalAuthorizationNumber], [t7].[IsNoOpOrder], [t7].[LegacyFailAuthId], [t7].[LegacyOrderId], [t7].[MerchantAccountId], [t7].[MerchantOrderID], [t7].[Message], [t7].[PortalId], [t7].[ResultCode], [t7].[SettlementOrderId], [t7].[Shipping], [t7].[SubTotal], [t7].[Tax], [t7].[TimeStamp], [t7].[Total], [t7].[TransactionNumber], [t7].[UserId], [t7].[ReasonCode], [t7].[CyberSourceDescription], [t7].[FriendlyDescription], [t7].[ID0], [t7].[Notes], [t7].[SCMPReplyFlag], [t7].[ID1], [t7].[City], [t7].[Description], [t7].[DisplayName], [t7].[LegacyId], [t7].[MerchantId], [t7].[ParentCompany], [t7].[Phone], [t7].[State], [t7].[StreetAddress], [t7].[SuiteNum], [t7].[Zip], [t7].[ID2], [t7].[AccountNumber], [t7].[AccountType], [t7].[BillingInfoId0], [t7].[CorporateAccountId], [t7].[CvNumber], [t7].[ExpirationMonth], [t7].[ExpirationYear], [t7].[LegacyCCId], [t7].[PortalId0], [t7].[Token], [t7].[UserId0], [t7].[ID3], [t7].[AccountNumber0], [t7].[AccountType0], [t7].[City0], [t7].[CompanyName], [t7].[CreatedBy], [t7].[CreatedOn], [t7].[DeactivatedBy], [t7].[DeactivatedOn], [t7].[EmailAddress], [t7].[IsActive], [t7].[LastEditBy], [t7].[LastEditOn], [t7].[NumberOfUses], [t7].[PhoneNumber], [t7].[PrimaryContact], [t7].[State0], [t7].[Street], [t7].[UnlimitedUses], [t7].[Zip0], [t7].[ID4], [t7].[BillingEmail], [t7].[BillingMethod], [t7].[City1], [t7].[CorporateAccountId0], [t7].[CustomerFirstName], [t7].[CustomerId], [t7].[CustomerLastName], [t7].[DriversLicense], [t7].[IsActive0], [t7].[LastEditedBy], [t7].[LastEditedOn], [t7].[LegacyBillingId], [t7].[Notes0], [t7].[OverrideCreditCard], [t7].[OverrideCreditCardAdmin], [t7].[Phone0], [t7].[PortalId1], [t7].[State1], [t7].[StreetName], [t7].[StreetNumber], [t7].[SuiteNumber], [t7].[Zip1], [t7].[ID5], [t7].[AccountNumber1], [t7].[AccountType1], [t7].[City2], [t7].[CompanyName0], [t7].[CreatedBy0], [t7].[CreatedOn0], [t7].[DeactivatedBy0], [t7].[DeactivatedOn0], [t7].[EmailAddress0], [t7].[IsActive1], [t7].[LastEditBy0], [t7].[LastEditOn0], [t7].[NumberOfUses0], [t7].[PhoneNumber0], [t7].[PrimaryContact0], [t7].[State2], [t7].[Street0], [t7].[UnlimitedUses0], [t7].[Zip2], [t7].[ID6], [t7].[ItemId], [t7].[ItemType], [t7].[MenuItem], [t7].[MenuPrice], [t7].[Notes1], [t7].[OrderId], [t7].[Quantity], [t7].[UnitPrice], [t7].[WasPriceHidden], [t7].[ID00], [t7].[Active], [t7].[CreatedBy1], [t7].[CreatedOn1], [t7].[EstimatedDuration], [t7].[IsDeleted], [t7].[LastEditBy1], [t7].[LastEditOn1], [t7].[LongDescription], [t7].[MaxBuysAllowedPerUser], [t7].[MinTimeBetweenBuys], [t7].[PortalAssetId], [t7].[ProctorRequired], [t7].[ShortDescription], [t7].[ID7], [t7].[AuthorizationNumber0], [t7].[AuthorizedBy0], [t7].[BillingInfoId1], [t7].[CaptureReconcileId0], [t7].[CheckoutBillingMethod0], [t7].[CreditCardId0], [t7].[Hide0], [t7].[InternalAuthorizationNumber0], [t7].[IsNoOpOrder0], [t7].[LegacyFailAuthId0], [t7].[LegacyOrderId0], [t7].[MerchantAccountId0], [t7].[MerchantOrderID0], [t7].[Message0], [t7].[PortalId2], [t7].[ResultCode0], [t7].[SettlementOrderId0], [t7].[Shipping0], [t7].[SubTotal0], [t7].[Tax0], [t7].[TimeStamp0], [t7].[Total0], [t7].[TransactionNumber0], [t7].[UserId1], [t7].[ID8], [t7].[AuthorizedBy1], [t7].[CaptureRequestId], [t7].[Comments], [t7].[IsCorpOrderVoid], [t7].[MerchantReferenceID], [t7].[OrderId0], [t7].[RefundAmount], [t7].[ResultCode1], [t7].[TimeStamp1], [t7].[Id01], [t7].[AccessFailedCount], [t7].[Address], [t7].[City3], [t7].[ConcurrencyStamp], [t7].[CreatedDate], [t7].[Email], [t7].[EmailConfirmed], [t7].[FirstName], [t7].[GrantUsermanagementAccess], [t7].[LastLogin], [t7].[LastName], [t7].[LegacyUserId], [t7].[LockoutEnabled], [t7].[LockoutEnd], [t7].[LockoutEndDateUtc], [t7].[LockoutEventDate], [t7].[MiddleName], [t7].[MyPortalID], [t7].[NormalizedEmail], [t7].[NormalizedUserName], [t7].[OIGExclusionCheckDate], [t7].[OIGExclusionCheckEnabled], [t7].[PasswordHash], [t7].[PermitSelfServePrepaidCodes], [t7].[PhoneNumber1], [t7].[PhoneNumberConfirmed], [t7].[Position], [t7].[ProctorAuthorizeExamRelease], [t7].[ProctorAuthorizePurchase], [t7].[SecurityStamp], [t7].[SimpleId], [t7].[State3], [t7].[Suite], [t7].[TwoFactorEnabled], [t7].[UserManagementGroup], [t7].[UserManagementId], [t7].[UserName], [t7].[Zip3], [i19].[ID], [i19].[BillingEmail], [i19].[BillingMethod], [i19].[City], [i19].[CorporateAccountId], [i19].[CustomerFirstName], [i19].[CustomerId], [i19].[CustomerLastName], [i19].[DriversLicense], [i19].[IsActive], [i19].[LastEditedBy], [i19].[LastEditedOn], [i19].[LegacyBillingId], [i19].[Notes], [i19].[OverrideCreditCard], [i19].[OverrideCreditCardAdmin], [i19].[Phone], [i19].[PortalId], [i19].[State], [i19].[StreetName], [i19].[StreetNumber], [i19].[SuiteNumber], [i19].[Zip], [t8].[ID], [t8].[FieldId], [t8].[FieldValue], [t8].[UserRecordId], [t8].[ID0], [t8].[CustomRegex], [t8].[DataTypeEditor], [t8].[DefaultValues], [t8].[FieldExplanation], [t8].[FieldName], [t8].[InternalNotes], [t8].[IsRequired], [t8].[MaxLength], [t8].[MaxValue], [t8].[MinLength], [t8].[MinValue], [t8].[SearchableInTechPage], [t8].[ShowInProfile], [t8].[SortOrder], [t8].[UseCustomRegex], [t8].[UserCanEdit], [t8].[UserPrompt], [t8].[ValidationMessage], [t8].[VisibleCharactersOnBlur], [a1].[UserId], [a1].[RoleId], [t9].[ID], [t9].[CorporateAccountID], [t9].[IsDefaultForPortal], [t9].[PortalID], [t9].[ID0], [t9].[AccountNumber], [t9].[AccountType], [t9].[City], [t9].[CompanyName], [t9].[CreatedBy], [t9].[CreatedOn], [t9].[DeactivatedBy], [t9].[DeactivatedOn], [t9].[EmailAddress], [t9].[IsActive], [t9].[LastEditBy], [t9].[LastEditOn], [t9].[NumberOfUses], [t9].[PhoneNumber], [t9].[PrimaryContact], [t9].[State], [t9].[Street], [t9].[UnlimitedUses], [t9].[Zip], [i24].[ID], [i24].[PortalID], [i24].[URL], [t13].[ID], [t13].[CategoryID], [t13].[EndDate], [t13].[HidePrice], [t13].[IsDeleted], [t13].[LastEditBy], [t13].[LastEditOn], [t13].[PackageID], [t13].[PortalID], [t13].[Price], [t13].[SortPriority], [t13].[StartDate], [t13].[ID0], [t13].[Active], [t13].[CreatedBy], [t13].[CreatedOn], [t13].[EstimatedDuration], [t13].[IsDeleted0], [t13].[LastEditBy0], [t13].[LastEditOn0], [t13].[LongDescription], [t13].[MaxBuysAllowedPerUser], [t13].[MinTimeBetweenBuys], [t13].[PortalAssetId], [t13].[ProctorRequired], [t13].[ShortDescription], [t13].[ID1], [t13].[CourseId], [t13].[PackageId0], [t13].[ID00], [t13].[AdminMessage], [t13].[Certificate], [t13].[CertificateExpirationTicks], [t13].[CertificatePoolID], [t13].[DataSyncIntervalMinutes], [t13].[DaysAvailableAfterCompletionTicks], [t13].[Deleted], [t13].[Description], [t13].[DevUseOnly], [t13].[Duration], [t13].[Filename], [t13].[GradingFormula], [t13].[HTMLData], [t13].[IsScorm], [t13].[LastEditBy1], [t13].[LastEditOn1], [t13].[LegacyCourseId], [t13].[LiveVersion], [t13].[MaxDurationSecs], [t13].[MinDurationSecs], [t13].[MinPassingScore], [t13].[Name], [t13].[RemoteSyncState], [t13].[ScormCloudPolling], [t13].[ScormLaunchFile], [t13].[ScormSurveySlides], [t13].[ScormType], [t13].[ShortUIName], [t13].[SyncId], [t13].[TotalScormVersions], [t13].[Uploaded], [t13].[UploadedBy], [t13].[VersionId], [t13].[VersioningBehavior], [t13].[ID2], [t13].[CorporateAccountID], [t13].[LastEditBy2], [t13].[LastEditOn2], [t13].[Notes], [t13].[OverridePrice], [t13].[PortalMenuItemID], [t14].[ID], [t14].[CategoryID], [t14].[EndDate], [t14].[HidePrice], [t14].[IsDeleted], [t14].[LastEditBy], [t14].[LastEditOn], [t14].[PackageID], [t14].[PortalID], [t14].[Price], [t14].[SortPriority], [t14].[StartDate]
FROM (
    SELECT TOP(2) [a].[Id], [a].[AccessFailedCount], [a].[Address], [a].[City], [a].[ConcurrencyStamp], [a].[CreatedDate], [a].[Email], [a].[EmailConfirmed], [a].[FirstName], [a].[GrantUsermanagementAccess], [a].[LastLogin], [a].[LastName], [a].[LegacyUserId], [a].[LockoutEnabled], [a].[LockoutEnd], [a].[LockoutEndDateUtc], [a].[LockoutEventDate], [a].[MiddleName], [a].[MyPortalID], [a].[NormalizedEmail], [a].[NormalizedUserName], [a].[OIGExclusionCheckDate], [a].[OIGExclusionCheckEnabled], [a].[PasswordHash], [a].[PermitSelfServePrepaidCodes], [a].[PhoneNumber], [a].[PhoneNumberConfirmed], [a].[Position], [a].[ProctorAuthorizeExamRelease], [a].[ProctorAuthorizePurchase], [a].[SecurityStamp], [a].[SimpleId], [a].[State], [a].[Suite], [a].[TwoFactorEnabled], [a].[UserManagementGroup], [a].[UserManagementId], [a].[UserName], [a].[Zip], [i].[ID] AS [ID0], [i].[Address] AS [Address0], [i].[City] AS [City0], [i].[Email] AS [Email0], [i].[GroupName], [i].[GroupType], [i].[IsDefault], [i].[ParentGroupId], [i].[PhoneNumber] AS [PhoneNumber0], [i].[PortalId], [i].[Position] AS [Position0], [i].[State] AS [State0], [i].[Suite] AS [Suite0], [i].[UserManagementId] AS [UserManagementId0], [i].[Zip] AS [Zip0], [t].[ID] AS [ID1], [t].[AllowCorpAccounts], [t].[AllowCreditCards], [t].[AllowPayPal], [t].[AllowPrepaidCodes], [t].[BillingMethodology], [t].[CanPortalAdminsCreateUsers], [t].[Category], [t].[DeactivationPending], [t].[DevUseOnly], [t].[DisplayName], [t].[HTMLFooterCSS], [t].[HTMLFooterView], [t].[HTMLHeaderCSS], [t].[HTMLHeaderView], [t].[IsActive], [t].[LastEditBy], [t].[LastEditOn], [t].[Logo], [t].[MerchantAccountId], [t].[PasswordRegistrationFieldId], [t].[ScriptInjection], [t].[SupportEmail], [t].[SupportPhone], [t].[URLSupportPage], [t].[UsernameRegistrationFieldId], [t].[UsersSelfRegister], [t].[ViewDirectory], [i1].[ID] AS [ID2], [i1].[CustomRegex], [i1].[DataTypeEditor], [i1].[DefaultValues], [i1].[FieldExplanation], [i1].[FieldName], [i1].[InternalNotes], [i1].[IsRequired], [i1].[MaxLength], [i1].[MaxValue], [i1].[MinLength], [i1].[MinValue], [i1].[SearchableInTechPage], [i1].[ShowInProfile], [i1].[SortOrder], [i1].[UseCustomRegex], [i1].[UserCanEdit], [i1].[UserPrompt], [i1].[ValidationMessage], [i1].[VisibleCharactersOnBlur], [i2].[ID] AS [ID3], [i2].[CustomRegex] AS [CustomRegex0], [i2].[DataTypeEditor] AS [DataTypeEditor0], [i2].[DefaultValues] AS [DefaultValues0], [i2].[FieldExplanation] AS [FieldExplanation0], [i2].[FieldName] AS [FieldName0], [i2].[InternalNotes] AS [InternalNotes0], [i2].[IsRequired] AS [IsRequired0], [i2].[MaxLength] AS [MaxLength0], [i2].[MaxValue] AS [MaxValue0], [i2].[MinLength] AS [MinLength0], [i2].[MinValue] AS [MinValue0], [i2].[SearchableInTechPage] AS [SearchableInTechPage0], [i2].[ShowInProfile] AS [ShowInProfile0], [i2].[SortOrder] AS [SortOrder0], [i2].[UseCustomRegex] AS [UseCustomRegex0], [i2].[UserCanEdit] AS [UserCanEdit0], [i2].[UserPrompt] AS [UserPrompt0], [i2].[ValidationMessage] AS [ValidationMessage0], [i2].[VisibleCharactersOnBlur] AS [VisibleCharactersOnBlur0]
    FROM [AspNetUsers] AS [a]
    INNER JOIN [LeProject_UserManagement_GroupDef] AS [i] ON [a].[UserManagementGroup] = [i].[ID]
    INNER JOIN (
        SELECT [i0].[ID], [i0].[AllowCorpAccounts], [i0].[AllowCreditCards], [i0].[AllowPayPal], [i0].[AllowPrepaidCodes], [i0].[BillingMethodology], [i0].[CanPortalAdminsCreateUsers], [i0].[Category], [i0].[DeactivationPending], [i0].[DevUseOnly], [i0].[DisplayName], [i0].[HTMLFooterCSS], [i0].[HTMLFooterView], [i0].[HTMLHeaderCSS], [i0].[HTMLHeaderView], [i0].[IsActive], [i0].[LastEditBy], [i0].[LastEditOn], [i0].[Logo], [i0].[MerchantAccountId], [i0].[PasswordRegistrationFieldId], [i0].[ScriptInjection], [i0].[SupportEmail], [i0].[SupportPhone], [i0].[URLSupportPage], [i0].[UsernameRegistrationFieldId], [i0].[UsersSelfRegister], [i0].[ViewDirectory]
        FROM [LeProject_Portal_Config] AS [i0]
        WHERE [i0].[DevUseOnly] = CAST(0 AS bit)
    ) AS [t] ON [a].[MyPortalID] = [t].[ID]
    LEFT JOIN [LeProject_Registration_FieldDef] AS [i1] ON [t].[PasswordRegistrationFieldId] = [i1].[ID]
    LEFT JOIN [LeProject_Registration_FieldDef] AS [i2] ON [t].[UsernameRegistrationFieldId] = [i2].[ID]
    WHERE ([a].[Id] = @__userId_0) AND @__userId_0 IS NOT NULL
) AS [t0]
LEFT JOIN [LeProject_Asset] AS [i3] ON [t0].[Id] = [i3].[UploadedBy]
LEFT JOIN (
    SELECT [i4].[ID], [i4].[AuthorizedBy], [i4].[CertificateData], [i4].[CertificateNumber], [i4].[CertificateScore], [i4].[CertificateTestPassed], [i4].[CourseId], [i4].[CourseVersion], [i4].[Disposition], [i4].[DispositionDate], [i4].[EnrolledOn], [i4].[FirstLaunchDate], [i4].[OrderId], [i4].[PortalId], [i4].[Reason], [i4].[StudentId], [i4].[Uid], [i4].[ViaPackageId], [t1].[ID] AS [ID0], [t1].[AdminMessage], [t1].[Certificate], [t1].[CertificateExpirationTicks], [t1].[CertificatePoolID], [t1].[DataSyncIntervalMinutes], [t1].[DaysAvailableAfterCompletionTicks], [t1].[Deleted], [t1].[Description], [t1].[DevUseOnly], [t1].[Duration], [t1].[Filename], [t1].[GradingFormula], [t1].[HTMLData], [t1].[IsScorm], [t1].[LastEditBy], [t1].[LastEditOn], [t1].[LegacyCourseId], [t1].[LiveVersion], [t1].[MaxDurationSecs], [t1].[MinDurationSecs], [t1].[MinPassingScore], [t1].[Name], [t1].[RemoteSyncState], [t1].[ScormCloudPolling], [t1].[ScormLaunchFile], [t1].[ScormSurveySlides], [t1].[ScormType], [t1].[ShortUIName], [t1].[SyncId], [t1].[TotalScormVersions], [t1].[Uploaded], [t1].[UploadedBy], [t1].[VersionId], [t1].[VersioningBehavior], [i6].[ID] AS [ID1], [i6].[Actor], [i6].[CourseId] AS [CourseId0], [i6].[CourseVersion] AS [CourseVersion0], [i6].[Disposition] AS [Disposition0], [i6].[EnrollmentId], [i6].[ExecutionPlan], [i6].[Footprints], [i6].[GlobalDat], [i6].[LastSceneDisplayed], [i6].[LastUpdate], [i6].[RecordFinalized]
    FROM [LeProject_CoursesEnrollment] AS [i4]
    INNER JOIN (
        SELECT [i5].[ID], [i5].[AdminMessage], [i5].[Certificate], [i5].[CertificateExpirationTicks], [i5].[CertificatePoolID], [i5].[DataSyncIntervalMinutes], [i5].[DaysAvailableAfterCompletionTicks], [i5].[Deleted], [i5].[Description], [i5].[DevUseOnly], [i5].[Duration], [i5].[Filename], [i5].[GradingFormula], [i5].[HTMLData], [i5].[IsScorm], [i5].[LastEditBy], [i5].[LastEditOn], [i5].[LegacyCourseId], [i5].[LiveVersion], [i5].[MaxDurationSecs], [i5].[MinDurationSecs], [i5].[MinPassingScore], [i5].[Name], [i5].[RemoteSyncState], [i5].[ScormCloudPolling], [i5].[ScormLaunchFile], [i5].[ScormSurveySlides], [i5].[ScormType], [i5].[ShortUIName], [i5].[SyncId], [i5].[TotalScormVersions], [i5].[Uploaded], [i5].[UploadedBy], [i5].[VersionId], [i5].[VersioningBehavior]
        FROM [LeProject_Courses] AS [i5]
        WHERE [i5].[Deleted] = CAST(0 AS bit)
    ) AS [t1] ON [i4].[CourseId] = [t1].[ID]
    LEFT JOIN [LeProject_CoursesFootprints] AS [i6] ON [i4].[ID] = [i6].[EnrollmentId]
) AS [t2] ON [t0].[Id] = [t2].[StudentId]
LEFT JOIN [LeProject_CorporateAccount] AS [i7] ON [t0].[Id] = [i7].[LastEditBy]
LEFT JOIN (
    SELECT [i8].[ID], [i8].[AuthorizationNumber], [i8].[AuthorizedBy], [i8].[BillingInfoId], [i8].[CaptureReconcileId], [i8].[CheckoutBillingMethod], [i8].[CreditCardId], [i8].[Hide], [i8].[InternalAuthorizationNumber], [i8].[IsNoOpOrder], [i8].[LegacyFailAuthId], [i8].[LegacyOrderId], [i8].[MerchantAccountId], [i8].[MerchantOrderID], [i8].[Message], [i8].[PortalId], [i8].[ResultCode], [i8].[SettlementOrderId], [i8].[Shipping], [i8].[SubTotal], [i8].[Tax], [i8].[TimeStamp], [i8].[Total], [i8].[TransactionNumber], [i8].[UserId], [i9].[ReasonCode], [i9].[CyberSourceDescription], [i9].[FriendlyDescription], [i9].[ID] AS [ID0], [i9].[Notes], [i9].[SCMPReplyFlag], [i10].[ID] AS [ID1], [i10].[City], [i10].[Description], [i10].[DisplayName], [i10].[LegacyId], [i10].[MerchantId], [i10].[ParentCompany], [i10].[Phone], [i10].[State], [i10].[StreetAddress], [i10].[SuiteNum], [i10].[Zip], [i11].[ID] AS [ID2], [i11].[AccountNumber], [i11].[AccountType], [i11].[BillingInfoId] AS [BillingInfoId0], [i11].[CorporateAccountId], [i11].[CvNumber], [i11].[ExpirationMonth], [i11].[ExpirationYear], [i11].[LegacyCCId], [i11].[PortalId] AS [PortalId0], [i11].[Token], [i11].[UserId] AS [UserId0], [i12].[ID] AS [ID3], [i12].[AccountNumber] AS [AccountNumber0], [i12].[AccountType] AS [AccountType0], [i12].[City] AS [City0], [i12].[CompanyName], [i12].[CreatedBy], [i12].[CreatedOn], [i12].[DeactivatedBy], [i12].[DeactivatedOn], [i12].[EmailAddress], [i12].[IsActive], [i12].[LastEditBy], [i12].[LastEditOn], [i12].[NumberOfUses], [i12].[PhoneNumber], [i12].[PrimaryContact], [i12].[State] AS [State0], [i12].[Street], [i12].[UnlimitedUses], [i12].[Zip] AS [Zip0], [i13].[ID] AS [ID4], [i13].[BillingEmail], [i13].[BillingMethod], [i13].[City] AS [City1], [i13].[CorporateAccountId] AS [CorporateAccountId0], [i13].[CustomerFirstName], [i13].[CustomerId], [i13].[CustomerLastName], [i13].[DriversLicense], [i13].[IsActive] AS [IsActive0], [i13].[LastEditedBy], [i13].[LastEditedOn], [i13].[LegacyBillingId], [i13].[Notes] AS [Notes0], [i13].[OverrideCreditCard], [i13].[OverrideCreditCardAdmin], [i13].[Phone] AS [Phone0], [i13].[PortalId] AS [PortalId1], [i13].[State] AS [State1], [i13].[StreetName], [i13].[StreetNumber], [i13].[SuiteNumber], [i13].[Zip] AS [Zip1], [i14].[ID] AS [ID5], [i14].[AccountNumber] AS [AccountNumber1], [i14].[AccountType] AS [AccountType1], [i14].[City] AS [City2], [i14].[CompanyName] AS [CompanyName0], [i14].[CreatedBy] AS [CreatedBy0], [i14].[CreatedOn] AS [CreatedOn0], [i14].[DeactivatedBy] AS [DeactivatedBy0], [i14].[DeactivatedOn] AS [DeactivatedOn0], [i14].[EmailAddress] AS [EmailAddress0], [i14].[IsActive] AS [IsActive1], [i14].[LastEditBy] AS [LastEditBy0], [i14].[LastEditOn] AS [LastEditOn0], [i14].[NumberOfUses] AS [NumberOfUses0], [i14].[PhoneNumber] AS [PhoneNumber0], [i14].[PrimaryContact] AS [PrimaryContact0], [i14].[State] AS [State2], [i14].[Street] AS [Street0], [i14].[UnlimitedUses] AS [UnlimitedUses0], [i14].[Zip] AS [Zip2], [t4].[ID] AS [ID6], [t4].[ItemId], [t4].[ItemType], [t4].[MenuItem], [t4].[MenuPrice], [t4].[Notes] AS [Notes1], [t4].[OrderId], [t4].[Quantity], [t4].[UnitPrice], [t4].[WasPriceHidden], [t4].[ID0] AS [ID00], [t4].[Active], [t4].[CreatedBy] AS [CreatedBy1], [t4].[CreatedOn] AS [CreatedOn1], [t4].[EstimatedDuration], [t4].[IsDeleted], [t4].[LastEditBy] AS [LastEditBy1], [t4].[LastEditOn] AS [LastEditOn1], [t4].[LongDescription], [t4].[MaxBuysAllowedPerUser], [t4].[MinTimeBetweenBuys], [t4].[PortalAssetId], [t4].[ProctorRequired], [t4].[ShortDescription], [t5].[ID] AS [ID7], [t5].[AuthorizationNumber] AS [AuthorizationNumber0], [t5].[AuthorizedBy] AS [AuthorizedBy0], [t5].[BillingInfoId] AS [BillingInfoId1], [t5].[CaptureReconcileId] AS [CaptureReconcileId0], [t5].[CheckoutBillingMethod] AS [CheckoutBillingMethod0], [t5].[CreditCardId] AS [CreditCardId0], [t5].[Hide] AS [Hide0], [t5].[InternalAuthorizationNumber] AS [InternalAuthorizationNumber0], [t5].[IsNoOpOrder] AS [IsNoOpOrder0], [t5].[LegacyFailAuthId] AS [LegacyFailAuthId0], [t5].[LegacyOrderId] AS [LegacyOrderId0], [t5].[MerchantAccountId] AS [MerchantAccountId0], [t5].[MerchantOrderID] AS [MerchantOrderID0], [t5].[Message] AS [Message0], [t5].[PortalId] AS [PortalId2], [t5].[ResultCode] AS [ResultCode0], [t5].[SettlementOrderId] AS [SettlementOrderId0], [t5].[Shipping] AS [Shipping0], [t5].[SubTotal] AS [SubTotal0], [t5].[Tax] AS [Tax0], [t5].[TimeStamp] AS [TimeStamp0], [t5].[Total] AS [Total0], [t5].[TransactionNumber] AS [TransactionNumber0], [t5].[UserId] AS [UserId1], [t6].[ID] AS [ID8], [t6].[AuthorizedBy] AS [AuthorizedBy1], [t6].[CaptureRequestId], [t6].[Comments], [t6].[IsCorpOrderVoid], [t6].[MerchantReferenceID], [t6].[OrderId] AS [OrderId0], [t6].[RefundAmount], [t6].[ResultCode] AS [ResultCode1], [t6].[TimeStamp] AS [TimeStamp1], [t6].[Id0] AS [Id01], [t6].[AccessFailedCount], [t6].[Address], [t6].[City] AS [City3], [t6].[ConcurrencyStamp], [t6].[CreatedDate], [t6].[Email], [t6].[EmailConfirmed], [t6].[FirstName], [t6].[GrantUsermanagementAccess], [t6].[LastLogin], [t6].[LastName], [t6].[LegacyUserId], [t6].[LockoutEnabled], [t6].[LockoutEnd], [t6].[LockoutEndDateUtc], [t6].[LockoutEventDate], [t6].[MiddleName], [t6].[MyPortalID], [t6].[NormalizedEmail], [t6].[NormalizedUserName], [t6].[OIGExclusionCheckDate], [t6].[OIGExclusionCheckEnabled], [t6].[PasswordHash], [t6].[PermitSelfServePrepaidCodes], [t6].[PhoneNumber] AS [PhoneNumber1], [t6].[PhoneNumberConfirmed], [t6].[Position], [t6].[ProctorAuthorizeExamRelease], [t6].[ProctorAuthorizePurchase], [t6].[SecurityStamp], [t6].[SimpleId], [t6].[State] AS [State3], [t6].[Suite], [t6].[TwoFactorEnabled], [t6].[UserManagementGroup], [t6].[UserManagementId], [t6].[UserName], [t6].[Zip] AS [Zip3]
    FROM [LeProject_Payments_Orders] AS [i8]
    INNER JOIN [LeProject_Payments_ReasonCodes] AS [i9] ON [i8].[ResultCode] = [i9].[ReasonCode]
    INNER JOIN [LeProject_Payments_MerchantAccounts] AS [i10] ON [i8].[MerchantAccountId] = [i10].[ID]
    LEFT JOIN [LeProject_Payments_CreditCards] AS [i11] ON [i8].[CreditCardId] = [i11].[ID]
    LEFT JOIN [LeProject_CorporateAccount] AS [i12] ON [i11].[CorporateAccountId] = [i12].[ID]
    LEFT JOIN [LeProject_Payments_BillingInfo] AS [i13] ON [i8].[BillingInfoId] = [i13].[ID]
    LEFT JOIN [LeProject_CorporateAccount] AS [i14] ON [i13].[CorporateAccountId] = [i14].[ID]
    LEFT JOIN (
        SELECT [i15].[ID], [i15].[ItemId], [i15].[ItemType], [i15].[MenuItem], [i15].[MenuPrice], [i15].[Notes], [i15].[OrderId], [i15].[Quantity], [i15].[UnitPrice], [i15].[WasPriceHidden], [t3].[ID] AS [ID0], [t3].[Active], [t3].[CreatedBy], [t3].[CreatedOn], [t3].[EstimatedDuration], [t3].[IsDeleted], [t3].[LastEditBy], [t3].[LastEditOn], [t3].[LongDescription], [t3].[MaxBuysAllowedPerUser], [t3].[MinTimeBetweenBuys], [t3].[PortalAssetId], [t3].[ProctorRequired], [t3].[ShortDescription]
        FROM [LeProject_Payments_OrderItem] AS [i15]
        INNER JOIN (
            SELECT [i16].[ID], [i16].[Active], [i16].[CreatedBy], [i16].[CreatedOn], [i16].[EstimatedDuration], [i16].[IsDeleted], [i16].[LastEditBy], [i16].[LastEditOn], [i16].[LongDescription], [i16].[MaxBuysAllowedPerUser], [i16].[MinTimeBetweenBuys], [i16].[PortalAssetId], [i16].[ProctorRequired], [i16].[ShortDescription]
            FROM [LeProject_Package_Config] AS [i16]
            WHERE ((([i16].[IsDeleted] = CAST(0 AS bit)) AND ([i16].[ID] <> CAST(11 AS bigint))) AND ([i16].[ID] <> CAST(15 AS bigint))) AND ([i16].[ID] <> CAST(16 AS bigint))
        ) AS [t3] ON [i15].[ItemId] = [t3].[ID]
    ) AS [t4] ON [i8].[ID] = [t4].[OrderId]
    LEFT JOIN (
        SELECT [i17].[ID], [i17].[AuthorizationNumber], [i17].[AuthorizedBy], [i17].[BillingInfoId], [i17].[CaptureReconcileId], [i17].[CheckoutBillingMethod], [i17].[CreditCardId], [i17].[Hide], [i17].[InternalAuthorizationNumber], [i17].[IsNoOpOrder], [i17].[LegacyFailAuthId], [i17].[LegacyOrderId], [i17].[MerchantAccountId], [i17].[MerchantOrderID], [i17].[Message], [i17].[PortalId], [i17].[ResultCode], [i17].[SettlementOrderId], [i17].[Shipping], [i17].[SubTotal], [i17].[Tax], [i17].[TimeStamp], [i17].[Total], [i17].[TransactionNumber], [i17].[UserId]
        FROM [LeProject_Payments_Orders] AS [i17]
        WHERE [i17].[Hide] = CAST(0 AS bit)
    ) AS [t5] ON [i8].[ID] = [t5].[SettlementOrderId]
    LEFT JOIN (
        SELECT [i18].[ID], [i18].[AuthorizedBy], [i18].[CaptureRequestId], [i18].[Comments], [i18].[IsCorpOrderVoid], [i18].[MerchantReferenceID], [i18].[OrderId], [i18].[RefundAmount], [i18].[ResultCode], [i18].[TimeStamp], [a0].[Id] AS [Id0], [a0].[AccessFailedCount], [a0].[Address], [a0].[City], [a0].[ConcurrencyStamp], [a0].[CreatedDate], [a0].[Email], [a0].[EmailConfirmed], [a0].[FirstName], [a0].[GrantUsermanagementAccess], [a0].[LastLogin], [a0].[LastName], [a0].[LegacyUserId], [a0].[LockoutEnabled], [a0].[LockoutEnd], [a0].[LockoutEndDateUtc], [a0].[LockoutEventDate], [a0].[MiddleName], [a0].[MyPortalID], [a0].[NormalizedEmail], [a0].[NormalizedUserName], [a0].[OIGExclusionCheckDate], [a0].[OIGExclusionCheckEnabled], [a0].[PasswordHash], [a0].[PermitSelfServePrepaidCodes], [a0].[PhoneNumber], [a0].[PhoneNumberConfirmed], [a0].[Position], [a0].[ProctorAuthorizeExamRelease], [a0].[ProctorAuthorizePurchase], [a0].[SecurityStamp], [a0].[SimpleId], [a0].[State], [a0].[Suite], [a0].[TwoFactorEnabled], [a0].[UserManagementGroup], [a0].[UserManagementId], [a0].[UserName], [a0].[Zip]
        FROM [LeProject_Payments_OrderRefunds] AS [i18]
        LEFT JOIN [AspNetUsers] AS [a0] ON [i18].[AuthorizedBy] = [a0].[Id]
    ) AS [t6] ON [i8].[ID] = [t6].[OrderId]
    WHERE [i8].[Hide] = CAST(0 AS bit)
) AS [t7] ON [t0].[Id] = [t7].[UserId]
LEFT JOIN [LeProject_Payments_BillingInfo] AS [i19] ON [t0].[Id] = [i19].[CustomerId]
LEFT JOIN (
    SELECT [i20].[ID], [i20].[FieldId], [i20].[FieldValue], [i20].[UserRecordId], [i21].[ID] AS [ID0], [i21].[CustomRegex], [i21].[DataTypeEditor], [i21].[DefaultValues], [i21].[FieldExplanation], [i21].[FieldName], [i21].[InternalNotes], [i21].[IsRequired], [i21].[MaxLength], [i21].[MaxValue], [i21].[MinLength], [i21].[MinValue], [i21].[SearchableInTechPage], [i21].[ShowInProfile], [i21].[SortOrder], [i21].[UseCustomRegex], [i21].[UserCanEdit], [i21].[UserPrompt], [i21].[ValidationMessage], [i21].[VisibleCharactersOnBlur]
    FROM [LeProject_Registration_FieldValues] AS [i20]
    INNER JOIN [LeProject_Registration_FieldDef] AS [i21] ON [i20].[FieldId] = [i21].[ID]
) AS [t8] ON [t0].[Id] = [t8].[UserRecordId]
LEFT JOIN [AspNetUserRoles] AS [a1] ON [t0].[Id] = [a1].[UserId]
LEFT JOIN (
    SELECT [i22].[ID], [i22].[CorporateAccountID], [i22].[IsDefaultForPortal], [i22].[PortalID], [i23].[ID] AS [ID0], [i23].[AccountNumber], [i23].[AccountType], [i23].[City], [i23].[CompanyName], [i23].[CreatedBy], [i23].[CreatedOn], [i23].[DeactivatedBy], [i23].[DeactivatedOn], [i23].[EmailAddress], [i23].[IsActive], [i23].[LastEditBy], [i23].[LastEditOn], [i23].[NumberOfUses], [i23].[PhoneNumber], [i23].[PrimaryContact], [i23].[State], [i23].[Street], [i23].[UnlimitedUses], [i23].[Zip]
    FROM [LeProject_CorporateAccount_PortalMap] AS [i22]
    LEFT JOIN [LeProject_CorporateAccount] AS [i23] ON [i22].[CorporateAccountID] = [i23].[ID]
) AS [t9] ON [t0].[ID1] = [t9].[PortalID]
LEFT JOIN [LeProject_Portal_URL] AS [i24] ON [t0].[ID1] = [i24].[PortalID]
LEFT JOIN (
    SELECT [i25].[ID], [i25].[CategoryID], [i25].[EndDate], [i25].[HidePrice], [i25].[IsDeleted], [i25].[LastEditBy], [i25].[LastEditOn], [i25].[PackageID], [i25].[PortalID], [i25].[Price], [i25].[SortPriority], [i25].[StartDate], [t10].[ID] AS [ID0], [t10].[Active], [t10].[CreatedBy], [t10].[CreatedOn], [t10].[EstimatedDuration], [t10].[IsDeleted] AS [IsDeleted0], [t10].[LastEditBy] AS [LastEditBy0], [t10].[LastEditOn] AS [LastEditOn0], [t10].[LongDescription], [t10].[MaxBuysAllowedPerUser], [t10].[MinTimeBetweenBuys], [t10].[PortalAssetId], [t10].[ProctorRequired], [t10].[ShortDescription], [t12].[ID] AS [ID1], [t12].[CourseId], [t12].[PackageId] AS [PackageId0], [t12].[ID0] AS [ID00], [t12].[AdminMessage], [t12].[Certificate], [t12].[CertificateExpirationTicks], [t12].[CertificatePoolID], [t12].[DataSyncIntervalMinutes], [t12].[DaysAvailableAfterCompletionTicks], [t12].[Deleted], [t12].[Description], [t12].[DevUseOnly], [t12].[Duration], [t12].[Filename], [t12].[GradingFormula], [t12].[HTMLData], [t12].[IsScorm], [t12].[LastEditBy] AS [LastEditBy1], [t12].[LastEditOn] AS [LastEditOn1], [t12].[LegacyCourseId], [t12].[LiveVersion], [t12].[MaxDurationSecs], [t12].[MinDurationSecs], [t12].[MinPassingScore], [t12].[Name], [t12].[RemoteSyncState], [t12].[ScormCloudPolling], [t12].[ScormLaunchFile], [t12].[ScormSurveySlides], [t12].[ScormType], [t12].[ShortUIName], [t12].[SyncId], [t12].[TotalScormVersions], [t12].[Uploaded], [t12].[UploadedBy], [t12].[VersionId], [t12].[VersioningBehavior], [i29].[ID] AS [ID2], [i29].[CorporateAccountID], [i29].[LastEditBy] AS [LastEditBy2], [i29].[LastEditOn] AS [LastEditOn2], [i29].[Notes], [i29].[OverridePrice], [i29].[PortalMenuItemID]
    FROM [LeProject_Shop_MenuItem] AS [i25]
    INNER JOIN (
        SELECT [i26].[ID], [i26].[Active], [i26].[CreatedBy], [i26].[CreatedOn], [i26].[EstimatedDuration], [i26].[IsDeleted], [i26].[LastEditBy], [i26].[LastEditOn], [i26].[LongDescription], [i26].[MaxBuysAllowedPerUser], [i26].[MinTimeBetweenBuys], [i26].[PortalAssetId], [i26].[ProctorRequired], [i26].[ShortDescription]
        FROM [LeProject_Package_Config] AS [i26]
        WHERE ((([i26].[IsDeleted] = CAST(0 AS bit)) AND ([i26].[ID] <> CAST(11 AS bigint))) AND ([i26].[ID] <> CAST(15 AS bigint))) AND ([i26].[ID] <> CAST(16 AS bigint))
    ) AS [t10] ON [i25].[PackageID] = [t10].[ID]
    LEFT JOIN (
        SELECT [i27].[ID], [i27].[CourseId], [i27].[PackageId], [t11].[ID] AS [ID0], [t11].[AdminMessage], [t11].[Certificate], [t11].[CertificateExpirationTicks], [t11].[CertificatePoolID], [t11].[DataSyncIntervalMinutes], [t11].[DaysAvailableAfterCompletionTicks], [t11].[Deleted], [t11].[Description], [t11].[DevUseOnly], [t11].[Duration], [t11].[Filename], [t11].[GradingFormula], [t11].[HTMLData], [t11].[IsScorm], [t11].[LastEditBy], [t11].[LastEditOn], [t11].[LegacyCourseId], [t11].[LiveVersion], [t11].[MaxDurationSecs], [t11].[MinDurationSecs], [t11].[MinPassingScore], [t11].[Name], [t11].[RemoteSyncState], [t11].[ScormCloudPolling], [t11].[ScormLaunchFile], [t11].[ScormSurveySlides], [t11].[ScormType], [t11].[ShortUIName], [t11].[SyncId], [t11].[TotalScormVersions], [t11].[Uploaded], [t11].[UploadedBy], [t11].[VersionId], [t11].[VersioningBehavior]
        FROM [LeProject_Package_Courses] AS [i27]
        INNER JOIN (
            SELECT [i28].[ID], [i28].[AdminMessage], [i28].[Certificate], [i28].[CertificateExpirationTicks], [i28].[CertificatePoolID], [i28].[DataSyncIntervalMinutes], [i28].[DaysAvailableAfterCompletionTicks], [i28].[Deleted], [i28].[Description], [i28].[DevUseOnly], [i28].[Duration], [i28].[Filename], [i28].[GradingFormula], [i28].[HTMLData], [i28].[IsScorm], [i28].[LastEditBy], [i28].[LastEditOn], [i28].[LegacyCourseId], [i28].[LiveVersion], [i28].[MaxDurationSecs], [i28].[MinDurationSecs], [i28].[MinPassingScore], [i28].[Name], [i28].[RemoteSyncState], [i28].[ScormCloudPolling], [i28].[ScormLaunchFile], [i28].[ScormSurveySlides], [i28].[ScormType], [i28].[ShortUIName], [i28].[SyncId], [i28].[TotalScormVersions], [i28].[Uploaded], [i28].[UploadedBy], [i28].[VersionId], [i28].[VersioningBehavior]
            FROM [LeProject_Courses] AS [i28]
            WHERE [i28].[Deleted] = CAST(0 AS bit)
        ) AS [t11] ON [i27].[CourseId] = [t11].[ID]
    ) AS [t12] ON [t10].[ID] = [t12].[PackageId]
    LEFT JOIN [LeProject_CorporateAccount_OverridePricing] AS [i29] ON [i25].[ID] = [i29].[PortalMenuItemID]
    WHERE [i25].[IsDeleted] = CAST(0 AS bit)
) AS [t13] ON [t0].[ID1] = [t13].[PortalID]
LEFT JOIN (
    SELECT [i30].[ID], [i30].[CategoryID], [i30].[EndDate], [i30].[HidePrice], [i30].[IsDeleted], [i30].[LastEditBy], [i30].[LastEditOn], [i30].[PackageID], [i30].[PortalID], [i30].[Price], [i30].[SortPriority], [i30].[StartDate]
    FROM [LeProject_Shop_MenuItem] AS [i30]
    WHERE [i30].[IsDeleted] = CAST(0 AS bit)
) AS [t14] ON [t0].[Id] = [t14].[LastEditBy]
where t0.Id = @__userId_0
ORDER BY [t0].[Id], [t0].[ID0], [t0].[ID1], [i3].[ID], [t2].[ID], [t2].[ID0], [t2].[ID1], [i7].[ID], [t7].[ID], [t7].[ReasonCode], [t7].[ID1], [t7].[ID6], [t7].[ID00], [t7].[ID7], [t7].[ID8], [i19].[ID], [t8].[ID], [t8].[ID0], [a1].[UserId], [a1].[RoleId], [t9].[ID], [i24].[ID], [t13].[ID], [t13].[ID0], [t13].[ID1], [t13].[ID00], [t13].[ID2], [t14].[ID]

That query returns ~50k apparently duplicate rows.

Am I doing something wrong (other than asking for a lot of joins) or is there something wrong with the API?

Is there a more reasonable/performant manner in which to query all this related data under the new API?

Thanks in advance!

EF Core version: 3.0
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: e.g. .NET Core 3.0
Operating system: Win 10 Pro
IDE: Visual Studio 2019 Pro 16.3.2

[BartoszWiszniewski]

I have same problem and i dont like orderby on every field which kills query performence this version is not stable.

[smitpatel]

Duplicate of #18022

[SimonOrdo]

@smitpatel Is it though? My query should only return a single record, but returns over 50k duplicates.

[BartoszWiszniewski]

@smitpatel Is it though? My query should only return a single record, but returns over 50k duplicates.

are you sure? includes will result in duplicated results since you want to obrain data like courses, roles etc for each row you will get result
example https://www.w3schools.com/sql/trysql.asp?filename=trysql_select_join
remove orderby and run query you will see diffrence in execution time

[NetTecture]

@smitpatel Is it though? My query should only return a single record, but returns over 50k duplicates.

No, it will not. You start with a user, then go on including courses (more than one per user), and payments.

This gets translated into one query with significant fields repeated over and over - that is how joins work in SQL. There is no real way to do that efficiently and I SERIOUSLY doubt yo ureally want this mother off all queries to start with - looks like extremely broken design from a UI perspewctive.

At minimum you can kill that into multiple separate queries for every part instead of querying all withotu really needing it.

[SimonOrdo]

You're right, after closer examination. That's my fault. I guess it's easy to jump to conclusions with this much stuff broken and out of whack. I need to take more time in evaluating stuff more closely before assuming "it's just another thing not working after the upgrade".

Although, in fairness... the 3.0 changes are SUPER disruptive this time around.

[NetTecture]

They are, but i would flag this query in code reviews with or without those changes.

See, you only pull data you need. If you need all that in different parts of the UI - i want to see that UI ;) That is a LOT of only so so correlated data for one screen. Mutliple tabs? Tabs can ask for data WHEN NEEDED.

[SimonOrdo]

They are, but i would flag this query in code reviews with or without those changes.

i want to see that UI ;)

Yeah, I hear you... but this is a very specific client request to have everything show up on one page in a particular way. I'll end up re-writing it somewhat to mitigate as much as possible, but some requirements can't be argued against when it comes to getting checks signed. ;-)

[NetTecture]

I hate to tell you, it is NOT a client request that all this shows up on a page, unless that page is like covering awhole wall. I can udnerstand asking for certain top lines, but then the data amount would be less and you would ask for only top x of any navigation property.
This query can totally be broken down to smaller amounts, with multiple requests running in parallel.

[BartoszWiszniewski]

They are, but i would flag this query in code reviews with or without those changes.
i want to see that UI ;)

Yeah, I hear you... but this is a very specific client request to have everything show up on one page in a particular way. I'll end up re-writing it somewhat to mitigate as much as possible, but some requirements can't be argued against when it comes to getting checks signed. ;-)

You can always load data asynchronously i bet its beter to load data overtime instead all with waiting multiple seconds to load page

[SimonOrdo]

Sure. I'm not confused about how to fix it, now that it needs fixing. But, the fact is (despite your objection), the client did (against all advice) very specifically ask for all data to be loaded on one giant wall of text , with as little white space as possible, and preferably above the fold, as fast as possible.

Some people are just that specific in what they want and how they want it to work based on their work-flow. So as to avoid premature optimization and unnecessary work, we started off with a naively written query, mostly to see if/how EF Core would handle it.

The results, in 2.x, were acceptable, even with the query written "badly". Sometimes purity loses out to pragmatism.

Clearly, at this point, we'll either re-write the query to load the data in multiple round-trips manually, or we'll have to move to chunked asynchronous loads and the client will have to live with the loading delays.