How to query related tables based on strict conditions

In ASP.NET Core-5, I have these two models.

public class Mandate
{
    [Key]
    [Required]
    public int Id { get; set; }

    public int MerchantId { get; set; }
    public string ReferenceNumber { get; set; }
    public decimal Amount { get; set; }

    public virtual Merchant Merchant { get; set; }
    public virtual ICollection<MandateDetail> MandateDetails { get; set; }
}

public class MandateDetail
{
    [Key]
    [Required]
    public int Id { get; set; }
    public int MandateId { get; set; }
    public int Status { get; set; }

    public virtual Mandate Mandate { get; set; }
}

One Mandate has many MandateDetail

var mandateQueryable = _context.mandates.AsQueryable().Where(m => m.MerchantId == merchantId);
var mandate = mandateQueryable.ToList();
var pagedMandates = await mandateQueryable.Include(l => l.MandateDetails)
                            .ThenInclude(m => m.Merchant)
                            .ToPagedListAsync();    

Status field in MandateDetail can be 0, 1 or 2

I want to select a query where Status = 2. And if any row MandateDetail of related Mandate is not equal to 2, it should not select that batch of record of related field.

For instance, from the table below it should not select the entire record where id = 1 in Mandate and MandateId = 1 for MandateDetail because one of the rows for Status in MandateDetail is not equal to 2

Mandate

id  |  MerchantId  | Amount
1   |     2        | 200000 
2   |     2        | 150000 

MandateDetail

id  |  MandateId   | Status
1   |     1        | 2
2   |     1        | 2 
3   |     1        | 1
4   |     1        | 2 
5   |     2        | 2
6   |     2        | 2 

How do I achieve this using var pagedMandates or var mandateQueryable in my query?

Solution 1:

You can put where condition of child table too like below:

var pagedMandates = await mandateQueryable.Include(l => l.MandateDetails)
                                        .ThenInclude(m => m.Merchant)
                                        .Where(l => l.MandateDetails.Any(x=>x.Status!=2))
                                        .ToPagedListAsync();

Solution 2:

You can write query as below. Update your Where and add additional condition to check !m.MandateDetails.Any(md => md.Status != 2).

var mandateQueryable = _context.mandates
                            .Include(l => l.MandateDetails)
                            .ThenInclude(m => m.Merchant)
                            .Where(m => m.MerchantId == merchantId && !m.MandateDetails.Any(md => md.Status != 2))
                            .ToPagedListAsync();