Click here to Skip to main content
15,908,254 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
For below query, resulting sql query has some joins missing:
var query = this.practicesDbContext.ServiceTransactionToClaims
        .Include(x => x.ClaimEntity)
            .ThenInclude(x => x.Carrier)
        .Include(x => x.ServiceTransaction)
            .ThenInclude(x => x.PersonAccountMember)
            .ThenInclude(x => x.PatientProfile)
            .ThenInclude(x => x.Location)
        .Include(x => x.ServiceTransaction)
            .ThenInclude(x => x.PersonAccountMember)
            .ThenInclude(x => x.PatientProfile)
            .ThenInclude(x => x.PatientBenefitPlans.Where(y => y.IsDeleted == false))
            .ThenInclude(x => x.BenefitPlan)
        .Include(x => x.ServiceTransaction)
            .ThenInclude(x => x.CreditTransactionDetails)
            .ThenInclude(x => x.CreditTransaction)

SQL query:
FROM [ServiceTransactionToClaim] AS [s]
INNER JOIN [ClaimEntity] AS [c] ON [s].[ClaimId] = [c].[EntityId]
INNER JOIN [ServiceTransaction] AS [s0] ON [s].[ServiceTransactionId] = [s0].[EntityId]
INNER JOIN [PersonAccountMember] AS [p] ON [s0].[AccountMemberId] = [p].[EntityId]
INNER JOIN [PatientProfile] AS [p0] ON [p].[PersonId] = [p0].[EntityId]
LEFT JOIN [Carrier] AS [c1] ON [c].[CarrierId] = [c1].[EntityId]
LEFT JOIN [Location] AS [l] ON [p0].[PreferredLocation] = [l].[EntityId]

I am new to this. Any suggestions.Thanks in advance.

What I have tried:

I checked DB entities to confirm if mapping is correctly done. Didnt find any issue
public class PatientProfile : BaseEntity<guid>
    public List<patientbenefitplan> PatientBenefitPlans { get; set; }
    public List<patientgroup> PatientGroups { get; set; }
    public List<patientphoneinfo> PatientPhoneInfos { get; set; }
    public List<personaccountmember> PersonAccountMembers { get; set; }
    public List<personaccountmember> ResponsiblePersonAccountMembers { get; set; }

public class PatientBenefitPlan : BaseEntity<guid>
    public Guid BenefitPlanId { get; set; }
    public Guid BenefitPlanDto { get; set; }
    public BenefitPlan BenefitPlan { get; set; }
    public bool DependentChildOnly { get; set; }
    public DateTime? EffectiveDate { get; set; }
    public bool? EligibleEPSDTTitleXIX { get; set; }
    public decimal IndividualDeductibleUsed { get; set; }
    public decimal IndividualMaxUsed { get; set; }
    public bool IsDeleted { get; set; }
    public Guid PatientId { get; set; }
    public Guid PolicyHolderId { get; set; }
    public Guid PolicyHolderBenefitPlanId { get; set; }
    public string PolicyHolderStringId { get; set; }
    public int Priority { get; set; }
    public PatientProfile PatientProfile { get; set; }
    public string RelationshipToPolicyHolder { get; set; }

Primary key are there in baseentityclass and relationships are defined in entityconfiguration class.
Updated 15-Feb-23 22:46pm
Richard Deeming 16-Feb-23 4:51am    
Have you tried removing the filter from the PatientBenefitPlans include?

What does the end of the query look like? Are you just returning the list of entities, or do you have a .Select(...) projection?

NB: Given the number of entities involved, you may experience a Cartesian Explosion - the number of records returned by your query will be the product of the number of records loaded for each set. If you're using EF Core 5 or later, you may want to investigate "query splitting", which could increase the performance.
Single vs. Split Queries - EF Core | Microsoft Learn[^]

1 solution

Ignore me as I need to brush up on my EF Core.
Share this answer
Richard Deeming 16-Feb-23 4:44am    
ThenInclude includes a navigation property from the previous entity; your code would only work if Location was related to the PatientBenefitPlan rather than the PatientProfile, and if CreditTransactionDetails was related to the BenefitPlan rather than the ServiceTransaction.

In order to ThenInclude multiple paths, you unfortunately need to Include the same property multiple times.
Dave Kreskowiak 16-Feb-23 7:46am    
You gotta be kidding me? :sigh:

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900