Nested Entities coming as null on reading using EF core 6 with Azure Cosmos DB

[smalviya01]

Ask a question

I have create a DB Context to map nested entities of an object to different containers, it works fine when writing to the database
it automatically writes nested entities to their respective containers from parent entity but when I am reading using
context.FindAsync(id) , nested entities are not retrieved and are null in the response. Below is the Db context I have created.
Note: All Nested entities have same Id and Same partition key as their Parent Entity.
Remember:

• Please make your question as clear and specific as possible.
• Please check that the documentation does not answer your question.
• Please search in both open and closed issues to check that your question has not already been answered.

Include your code

DbContext -

        public DbSet<ClaimDbModel> ClaimDbModel { get; set; }

        public DbSet<ClaimResolution> ClaimResolution { get; set; }

        public DbSet<ClaimOdarInfoAll> ClaimOdarInfoAll { get; set; }  
        
        public DbSet<ClaimLines> ClaimLines { get; set; }
        public DbSet<ClaimWebstratInfo> ClaimWebstratInfo { get; set; }
        public DbSet<ClaimOdarLongForm> ClaimOdarLongForm { get; set; }


        /// <summary>
        /// Configure the model that maps the domain to the backend.
        /// </summary>
        /// <param name="modelBuilder">The API for model configuration.</param>
        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<ClaimDbModel>()
                .HasNoDiscriminator()
                .ToContainer(nameof(ClaimDbModel))
                .HasPartitionKey(da => da.ClaimPartitionKey)
                .HasKey(da => new { da.id });

            var modelClaimResolution = modelBuilder.Entity<ClaimResolution>();
            modelClaimResolution.HasPartitionKey(da => da.ClaimResolutionPartitionKey)
                  .HasNoDiscriminator();
            modelClaimResolution.ToContainer(nameof(ClaimResolution))
                  .HasKey(da => new { da.id });
            modelBuilder.Entity<ClaimOdarInfoAll>()
                  .HasNoDiscriminator()
                  .ToContainer(nameof(ClaimOdarInfoAll))
                  .HasPartitionKey(da => da.ClaimOdarInfoPartitionKey)
                  .HasKey(da => new { da.id });
            modelBuilder.Entity<ClaimLines>()
                .HasNoDiscriminator()
                .ToContainer(nameof(ClaimLines))
                .HasPartitionKey(da => da.ClaimLinePartitionKey)
                .HasKey(da => new { da.id });
            modelBuilder.Entity<ClaimWebstratInfo>()
               .HasNoDiscriminator()
               .ToContainer(nameof(ClaimWebstratInfo))
               .HasPartitionKey(da => da.ClaimWebstratInfoPartitionKey)
               .HasKey(da => new { da.id });
            modelBuilder.Entity<ClaimOdarLongForm>()
               .HasNoDiscriminator()
               .ToContainer(nameof(ClaimOdarLongForm))
               .HasPartitionKey(da => da.ClaimOdarLongFormPartitionKey)
               .HasKey(da => new { da.id });

            base.OnModelCreating(modelBuilder);

Parent Entity Model-

    public class ClaimDbModel
    {
        [JsonPropertyName("id")]
        public string id { get; set; }
        public string ClaimPartitionKey { get; set; }
        public string ClaimId { get; set; }
        public string PatientSourceSystemId { get; set; }
        public byte BusinessSegmentId { get; set; }
        public byte PlatformId { get; set; }
        public byte ProductId { get; set; }
        public byte ClaimTypeId { get; set; }
        public string BillType { get; set; }
        public ClaimLines ClaimLines { get; set; }
        public ClaimOdarInfoAll ClaimOdarInfoAll { get; set; }
        public ClaimOdarLongForm ClaimOdarLongForm { get; set; }
        public ClaimResolution ClaimResolution { get; set; }
        public ClaimWebstratInfo ClaimWebstratInfo { get; set; }
    }
}

Child Entity Example -

public class ClaimResolution
{
    public string id { get; set; }
    public string ClaimResolutionPartitionKey { get; set; }
    public string Type { get; set; }
    public int Reason { get; set; }
    public decimal Amount { get; set; }
    public string SbmReferralId { get; set; }
    public DateTime PendUntilDate { get; set; }
    public string Fln { get; set; }
    public string ExternalNotes { get; set; }
    public string InternalNotes { get; set; }
    public string RobotNotes { get; set; }
    public DateTime ReviewDate { get; set; }
    public string ReviewBy { get; set; }
    public DateTime SubmitDate { get; set; }
    public string SubmitBy { get; set; }
    public int ProjectCodeId { get; set; }
    public DateTime ResolveDate { get; set; }
    public string ResolveBy { get; set; }
    public string OdarReferralCode { get; set; }

    public List<ClaimResolutionHistory> ClaimResolutionHistory { get; set; }
}

Include stack traces

Include the full exception message and stack trace for any exception you encounter.

Use triple-tick fences for stack traces. For example:

Unhandled exception. System.NullReferenceException: Object reference not set to an instance of an object.
   at SixFour.Sub() in C:\Stuff\AllTogetherNow\SixFour\SixFour.cs:line 49
   at SixFour.Main() in C:\Stuff\AllTogetherNow\SixFour\SixFour.cs:line 54

Include verbose output

Please include verbose output when asking questions about the dotnet ef or Package Manager Console tools.

Use triple-tick fences for tool output. For example:

C:\Stuff\AllTogetherNow\FiveOh>dotnet ef dbcontext list --verbose
Using project 'C:\Stuff\AllTogetherNow\FiveOh\FiveOh.csproj'.
...
Finding DbContext classes in the project...
Found DbContext 'BlogContext'.
BlogContext

Include provider and version information

EF Core version: .nuget\packages\microsoft.entityframeworkcore.cosmos\6.0.10
Database provider: (e.g. Microsoft.EntityFrameworkCore.SqlServer)
Target framework: (e.g. .NET 6.0) : NET 6.0
Operating system: Windows
IDE: (e.g. Visual Studio 2022 17.4) : Visual Studio 2022

[ajcvickers]

@smalviya01 A couple of things. First, FindAsync does not automatically load related entities. Second, mapping the child entities to another container means that loading the parents and children requires joining across the two containers, which is generally a very bad idea with Cosmos DB.

What is the reason for mapping these things to separate containers? How do you conceptualize the documents in the database and how they will be used?

[smalviya01]

@smalviya01 A couple of things. First, FindAsync does not automatically load related entities. Second, mapping the child entities to another container means that loading the parents and children requires joining across the two containers, which is generally a very bad idea with Cosmos DB.

What is the reason for mapping these things to separate containers? How do you conceptualize the documents in the database and how they will be used?

Hi @ajcvickers , Our usecase is we have a very complex object which we store in Mysql DB by mapping its child entities to different tables then using EF core to do CRUD operations. Its a huge object with very high volume (we store ~12 million records every month , Size of one JSON is ~34KB). That is why we decided to move to COSMOS DB with Azure Databricks so that we process this data faster.
Now to your point our users don't do operation on the whole object at once, For Example from above they might be just Updating ClaimResolution and ClaimResolutionHistory and not touching other entities. So we have segregated the Entities to different containers based on operations that our users do, so that we can reduce RUs consumed and processing time of the query. Also it reduces the size of the Partition in one container as one Document would be around ~34KB.

Best way our partition key gets created is < Platform + ClaimTypeId + Product + BusinessSegmentId + providerMpin> and if we just keep one Document as you are saying then the month on month growth of 34KB sized JSONs becomes more than 20GB per partition. It would even go beyond ~1TB as we are migrating 3 years data.
Hence to support that we had to split because our effective partitioning strategy will still end up creating ~150K number of partitions per container. so we wanted to have best of both worlds where our number of partitions are still manageable per container and the effective size of each partition is within the managed allowed limit of 20GB per logical partition.

[ajcvickers]

@smalviya01 It may be that you can't get the best of both worlds here if you need to do cross-container queries. Can you post an example of what the JSON documents look like in each container?

[smalviya01]

@smalviya01 It may be that you can't get the best of both worlds here if you need to do cross-container queries. Can you post an example of what the JSON documents look like in each container?

Here is what my cosmos db looks like with related containers -

Here is the example of Documents -
ClaimDBModel (parent Entity)-
{
"id": "74d29207-b8f3-44a9-87ed-e24b81baebcb117384065CSPVAMDN",
"AddDateTime": "2023-01-11T20:32:07.5975584+05:30",
"AdddBy": "999999",
"Adjuster": null,
"AdmissionDateTime": "0001-01-01T00:00:00",
"AdmitDiagnosis": null,
"AgagId": null,
"Allowed": 6247.29,
"AuditStatusId": 0,
"BillType": "11m",
"BilledAmount": 51643,
"BusinessSegment": "C&S",
"BusinessSegmentId": 2,
"C_BilledAmount": 51643,
"C_CcatPaid": 0,
"C_CcatPercentPaid": 0,
"C_ClaimPaid": 0,
"C_ClaimPaidDiff": 0,
"C_LineCount": 0,
"C_Overpaid": 0,
"C_WebstratPaid": 0,
"CcatAllowedAmount": 0,
"ChangeBy": "999999",
"ChangeDateTime": "2023-01-11T20:32:07.5975751+05:30",
"CheckDateTime": "2022-08-05T05:00:00",
"CirrusClaimId": null,
"ClaimCategoryId": 0,
"ClaimId": "74d29207-b8f3-44a9-87ed-e24b81baebcb",
"ClaimLinesid": "74d29207-b8f3-44a9-87ed-e24b81baebcb117384065CSPVAMDN",
"ClaimOdarInfoAllid": "74d29207-b8f3-44a9-87ed-e24b81baebcb117384065CSPVAMDN",
"ClaimOdarLongFormid": "74d29207-b8f3-44a9-87ed-e24b81baebcb117384065CSPVAMDN",
"ClaimPartitionKey": "CSP1Medicaid22110845",
"ClaimReprocessedDateTime": "0001-01-01T00:00:00",
"ClaimResolutionid": "74d29207-b8f3-44a9-87ed-e24b81baebcb117384065CSPVAMDN",
"ClaimStatusOverpaidDateTime": "0001-01-01T00:00:00",
"ClaimTypeId": 1,
"ClaimUnresolvedDateTime": "0001-01-01T00:00:00",
"ClaimWebstratInfoid": "74d29207-b8f3-44a9-87ed-e24b81baebcb117384065CSPVAMDN",
"Classification": null,
"ClckIntAmt": 0,
"Coinsurance": 0,
"ComplianceExpDateTime": "0001-01-01T00:00:00",
"ComplianceExpDays": 0,
"ConnectedClaimId": null,
"ContractId": 0,
"Copay": 0,
"CustomerSegmentNumber": "VAMDN",
"Deductible": 0,
"DischargeDateTime": "0001-01-01T00:00:00",
"DischargeStatus": null,
"Drg": "816",
"EngineCode": null,
"FacilityType": null,
"Fln": null,
"GeoLocation": null,
"IcdCode": null,
"IcdCode2": null,
"IcdCode3": null,
"IcdCode4": null,
"IcdCode5": null,
"IcdCode6": null,
"IcdCode7": null,
"InitialResolutionType": 0,
"IsMatched": false,
"LengthOfStay": 0,
"LogicalDelete": false,
"MarketType": null,
"MemberDob": "0001-01-01T00:00:00",
"MemberLiability": 0,
"MemberMarket": null,
"MemberNumber": null,
"MemberState": "VA",
"Month": 5,
"NetPaidAmount": 6247.29,
"OccurrenceCode": null,
"Org": null,
"Panel": 0,
"ParNonParIndicator": 0,
"PatientSourceSystemId": "1de824a2-6e5b-497e-9fca-0a8f8b868e4f",
"PayerPayment": 6247.29,
"PaymentExplanation": null,
"Platform": "CSP",
"PlatformId": 4,
"PostDateTime": "2022-08-05T00:00:00",
"PriorId": 0,
"ProcedureCode": null,
"ProcedureCode2": null,
"ProcedureCode3": null,
"ProcedureCode4": null,
"ProcedureCode5": null,
"ProcedureCode6": null,
"ProcedureCode7": null,
"Product": "Medicaid",
"ProductCode": null,
"ProductId": 85,
"ProviderCosmosId": null,
"ProviderId": "000009933",
"ProviderMedicareId": null,
"ProviderMpin": "211111",
"ProviderName": "CARILION Test HOSP",
"ProviderNpi": null,
"ProviderState": "VA",
"ProviderTaxId": "540555555",
"RateCode": null,
"RevenueCode": null,
"S_ClaimPercentPaid": 0,
"SequestrationAmount": 0,
"SharedArrangementCode": null,
"StartingServiceDateTime": "2022-05-27T00:00:00",
"SubAuditNumber": null,
"SubOrg": null,
"Subgroup": "VAMDVG",
"SubgroupId": 0,
"SystemNotes": null,
"TotalClaimPaid": 0,
"TotalMemberPaid": 0,
"Transcor": 0,
"ValueCode": null,
"WebstratDrg": null,
"Year": 2021,
"_rid": "1gA2AN63+6eBhB4AAAAAAA==",
"_self": "dbs/1gA2AA==/colls/1gA2AN63+6c=/docs/1gA2AN63+6eBhB4AAAAAAA==/",
"_etag": ""0c00f982-0000-0300-0000-63becf820000"",
"_attachments": "attachments/",
"_ts": 1673449346
}

Here is ClaimResolutionModel (Child Entity)-
{
"id": "74d29207-b8f3-44a9-87ed-e24b81baebcb117384065CSPVAMDN",
"Amount": 0,
"ClaimResolutionPartitionKey": "CSP1Medicaid22110845",
"ExternalNotes": null,
"Fln": null,
"InternalNotes": null,
"OdarReferralCode": null,
"PendUntilDate": "0001-01-01T00:00:00",
"ProjectCodeId": 0,
"Reason": 0,
"ResolveBy": null,
"ResolveDate": "0001-01-01T00:00:00",
"ReviewBy": null,
"ReviewDate": "0001-01-01T00:00:00",
"RobotNotes": null,
"SbmReferralId": null,
"SubmitBy": null,
"SubmitDate": "0001-01-01T00:00:00",
"Type": null,
"ClaimResolutionHistory": [
{
"Amount": 0,
"CcatAllowed": 0,
"CcatPaid": 0,
"ContractId": 0,
"ExternalNotes": null,
"FlagBy": null,
"FlagDate": "0001-01-01T00:00:00",
"InternalNotes": null,
"OverpayPotential": 0,
"PendDate": "0001-01-01T00:00:00",
"ResolutionReasonId": 0,
"TypeId": 0,
"WebstratAllowed": 0
},
{
"Amount": 0,
"CcatAllowed": 0,
"CcatPaid": 0,
"ContractId": 0,
"ExternalNotes": null,
"FlagBy": null,
"FlagDate": "0001-01-01T00:00:00",
"InternalNotes": null,
"OverpayPotential": 0,
"PendDate": "0001-01-01T00:00:00",
"ResolutionReasonId": 0,
"TypeId": 0,
"WebstratAllowed": 0
},
{
"Amount": 0,
"CcatAllowed": 0,
"CcatPaid": 0,
"ContractId": 0,
"ExternalNotes": null,
"FlagBy": null,
"FlagDate": "0001-01-01T00:00:00",
"InternalNotes": null,
"OverpayPotential": 0,
"PendDate": "0001-01-01T00:00:00",
"ResolutionReasonId": 0,
"TypeId": 0,
"WebstratAllowed": 0
},
{
"Amount": 0,
"CcatAllowed": 0,
"CcatPaid": 0,
"ContractId": 0,
"ExternalNotes": null,
"FlagBy": null,
"FlagDate": "0001-01-01T00:00:00",
"InternalNotes": null,
"OverpayPotential": 0,
"PendDate": "0001-01-01T00:00:00",
"ResolutionReasonId": 0,
"TypeId": 0,
"WebstratAllowed": 0
}
],
"_rid": "1gA2AOlpAUKBhB4AAAAAAA==",
"_self": "dbs/1gA2AA==/colls/1gA2AOlpAUI=/docs/1gA2AOlpAUKBhB4AAAAAAA==/",
"_etag": ""00003f08-0000-0300-0000-63becf870000"",
"_attachments": "attachments/",
"_ts": 1673449351
}

Just One question here @ajcvickers , If Cosmos DB EF provider can map a Parent object and its child entities to their respective containers by itself then why can't it read from the containers and return the consolidated object?

[smalviya01]

@ajcvickers any update on this?

[ajcvickers]

@smalviya01 This is currently by-design and not likely to change soon. However, we're still thinking about potential directions we could go in the future that might make this easier. /cc @roji

[smalviya01]

By any chance will this be released before end of this year? and will it be in EF core 6 ?

[ajcvickers]

No, and no.

[ajcvickers]

Note from triage: this is covered by #16920 (comment)