linq query with ICollection codition

Good morning dotnet team,

please i need your help, since two days i am trying to solve a link query, first bellow is my db set classes:

public class Payement{
    [Key]
    public int ID,
    public string a,
    public string b,
    public int status_request {get;set;}
    public ICollection<beneficiaires> Beneficiaires { get; set; } = new List<beneficiaires>(); }

beneficiaires class:

public class beneficiaires
{
    [Key]
    public int ID { get; set; }
    public string a {get;set;}
    public string code {get;set;}
    [ForeignKey("ID")]
    public Payement Payements { get; set; } = null;
    public int ID { get; set;}
}

From the above classe a payement can have many beneficiaires, what i wants is to select only Payements that has a specific code from beneficiaires, bellow is my query:

List<Payement> payments = await _DbContext.Payements.Include(b => b.beneficiaries.Where(c=>c.code.Equals('08'))).Where(p => (p.status_request == 1)).Take(5).ToListAsync();

but this query is taking all payements with the status_request 1, but i wants payements with status 1 but only for beneficiaires with code 08.

Can someone help me pls?
Regards…

>Solution :

The where condition must be

.Where(p => (p.status_request == 1) && p.beneficiaries.Any(c=>c.code.Equals('08')))

The condition that you include in the Include function instruct EF to load only items in beneficiaries with the condition. Remove this condition from the Include if you want all beneficiaries for each payment.

Where condition in Include

This code return filtered payments, and each payment has the beneficiaries collection filled with all data:

List<Payement> payments = await _DbContext.Payements
  .Include(b => b.beneficiaries) // don't add condition here
  .Where(p => 
      p.status_request == 1 &&    
      p.beneficiaries.Any(c=>c.code.Equals('08'))
  .Take(5).ToListAsync();

This code return the same filtered payments, but for each of them only beneficiaries with code equal to 08 are loaded:

List<Payement> payments = await _DbContext.Payements
  .Include(b => b.beneficiaries.Where(c=>c.code.Equals('08'))
  .Where(p => 
      p.status_request == 1 &&    
      p.beneficiaries.Any(c=>c.code.Equals('08'))
  .Take(5).ToListAsync();