CODEWITHSUNDEEP
c#linq
Jonno Lord
Jonno Lord

Reputation: 314

Filter on nested object in LINQ

I have the following query in LINQ to return a policy object with a list of its policy risks, and within the policy risk a list of its transaction movements.

corePolicy = _db.Policies
    .OrderByDescending(p => p.BatchId)
    .Include(policy => policy.PolicyRisks.Select(policyRisks =>
        policyRisks.TransactionMovements))
    .Include(policy => policy.PolicyRisks.Select(policyRisks =>
        policyRisks.PolicyRiskClaims.Select(prc =>
            prc.TransactionMovements)))
    .AsNoTracking()
    .First(p =>
        p.PolicyReference == policyFromStaging.PolicyReference &&
        p.PolicySourceSystemId == policyFromStaging.PolicySourceSystemId);

How do I apply a .Where() clause to the transaction movements, say TransactionReference == 'ABC'

Below is my data model

[Table("business.Policy")]
public class Policy
{
    [Required, StringLength(50)]
    public string PolicyReference { get; set; }

    public int PolicySourceSystemId { get; set; }
    public System PolicySourceSystem { get; set; }
}

[Table("business.PolicyRisk")]
public class PolicyRisk
{
    public ICollection<TransactionMovement> TransactionMovements { get; set; }
}

[Table("business.TransactionMovement")]
public class TransactionMovements
{
    public string TransactionReference { get; set; }
}

Upvotes: 0

Views: 669

Answers (1)

sam
sam

Reputation: 1985

Having Where statement in Include is not possible. You can have Any something like below:

corePolicy = _db.Policies
.OrderByDescending(p => p.BatchId)
.Include(policy => policy.PolicyRisks.Select(policyRisks =>
    policyRisks.TransactionMovements))
.Include(policy => policy.PolicyRisks.Select(policyRisks =>
    policyRisks.PolicyRiskClaims.Select(prc =>
        prc.TransactionMovements)))
.AsNoTracking()
.Where(p => p.PolicyRisks.Any(pr => pr.PolicyRiskClaims.Any(prc => prc.TransactionMovements.Any(tm => tm.TransactionReference == 'ABC'))))
.First(p =>
    p.PolicyReference == policyFromStaging.PolicyReference &&
    p.PolicySourceSystemId == policyFromStaging.PolicySourceSystemId);

This will include policies if any of the transaction reference is ABC (on its child collections). So, again you have to have a looping mechanism in c# side to get the policy which have this TransactionReference.

If you want to avoid having loop, then I would suggest writing your linq from other end meaning, start from TransactionMovement and go back to Policy.

You can have WHERE clause in Include filter with some third party library as mentioned here.

Upvotes: 1

Related Questions