Filter with ICollection List in Entity Model-CodePudding

I have this code.

Example value for request.ModelId = 2d3c306f-5904-4ca2-b17f-1ace38f2b53e

var query = _context.Agencies.Select(x => x);

if (request.ModelId != null && request.ModelId != Guid.Empty)
{
    query = query.Where(a => a.Recruiters.Any(r => r.ModelId == request.ModelId && r.RecruiterStatus!= null && r.RecruiterStatus.Name == "Active"));
}

int totalCount = query.Count();

return (
    await query
        .AsNoTracking()
        .ProjectTo<ModelsDto>(_mapper.ConfigurationProvider)
        .OrderByDescending(t => t.Submitted)
        .Skip(request.Start)
        .Take(request.Limit)
        .ToListAsync(cancellationToken),
    totalCount);

The code block above returns something like this (this is only an example)

id : "3c1a3b03-b48a-4051-9819-961c5a9b8ed2",
created : "2022-12-08T16:54:42.428233",
submitted : "2022-12-08T16:54:42.428233",
remarks : "2022-12-08T16:54:42.428233",
recruiters : 
     [
         0 : 
              { 
                id : "08daa761-3051-4119-84da-da7c9981a631",
                modelId : "2d3c306f-5904-4ca2-b17f-1ace38f2b53e",
                agencyId : "3c1a3b03-b48a-4051-9819-961c5a9b8ed2"
                recruiterStatus :
                        {
                          name : Active
                        }
                created "2022-12-08T16:54:42.540357"
              },
         1 : 
              {
                id : "5fc94679-2b7f-411c-ac77-84f2ab289744",
                modelId : "3e49fe2f-e2fd-4435-a7cd-b1b4b89b73c1",
                agencyId : "3c1a3b03-b48a-4051-9819-961c5a9b8ed2"
                recruiterStatus :
                        {
                          name : Pending
                        }
                created "2022-12-08T16:54:42.540357"
              }
          
     ]

I queried for ModelId = 2d3c306f-5904-4ca2-b17f-1ace38f2b53e AND RecruiterStatus.Name = "Active". I am expecting only 1 return, recruiters[0] in the JSON return but instead I got 2, recruiters[0] and recruiters[1].

Model Entity for Recruiter

public class Recruiter : IHasDomainEvent
{
    public Guid Id { get; set; }

    [ForeignKey("Model")]
    public Guid ModelId { get; set; }
    public Model Model { get; set; } = null!;

    [ForeignKey("Agency")]
    public Guid AgencyId { get; set; }
    public Agency Agency { get; set; } = null!;

    [ForeignKey("RecruiterStatus")]
    public Guid RecruiterStatusId { get; set; }
    public RecruiterStatus RecruiterStatus { get; set; } = null!;

    public DateTime? Created { get; set; }

    [NotMapped]
    public List<DomainEvent> DomainEvents { get; set; } = new List<DomainEvent>();
}

Model Entity for Agency

public class Agency: IHasDomainEvent
{
    public Guid Id { get; set; }

    public DateTime Submitted { get; set; }

    public string? Remarks { get; set; } = string.Empty;

    public virtual ICollection<Recruiter> Recruiters { get; set; } = new List<Recruiter>();

    [NotMapped]
    public List<DomainEvent> DomainEvents { get; set; } = new List<DomainEvent>();
}

Is there a way to filter (query) out the result to only return what I ask for the recruiters block?

CodePudding user response：

You are querying agencies and the agency with id "3c1a3b03-b48a-4051-9819-961c5a9b8ed2" has the both of the conditions you specified. So when it is returned it contains all of it fields including recruiters (The model has 2 recruiters). If you want to filter it again, you can do it in the memory (simple way). It means you put the results in a list and then for each agency omit items that do not satisfy the conditions.

To put it very simple just by using a loop (just snippet):

var listOfModelsDto= await query
        .AsNoTracking()
        .ProjectTo<ModelsDto>(_mapper.ConfigurationProvider)
        .OrderByDescending(t => t.Submitted)
        .Skip(request.Start)
        .Take(request.Limit)
        .ToListAsync(cancellationToken);

foreach (var item in listOfModelsDto){
  item.Recruiters=Recruiters.where(r => r.ModelId == request.ModelId && r.RecruiterStatus!= null && r.RecruiterStatus.Name == "Active").ToList();
}

return listOfModelsDto