Question 88212 in ASP.NET Question posted in
The official documentation can be found here.

EF Core 5 check if all ids from filter exists in related entities – Asp.net

I have two models:

public class Employee
{
    public int Id { get; set; }
    public IList<Skill> { get; set; }
}

public class Skill
{
    public int Id { get; set; }
}

And I have filter with list of skill ids, that employee should contain:

public class Filter
{
    public IList<int> SkillIds { get; set; }
}

I want to write query to get all employees, that have all skills from filter.

I tried:

query.Where(e => filter.SkillIds.All(id => e.Skills.Any(skill => skill.Id == id)));

And:

query = query.Where(e => e.Skills
                .Select(x => x.Id)
                .Intersect(filter.SkillIds)
                .Count() == filter.SkillIds.Count);

But as a result I get exception says that query could not be translated.

Tags:

3

Answers


  1. Chosen as BEST ANSWER
    0

    This solution works:

    foreach (int skillId in filter.SkillIds)
{
    query = query.Where(e => e.Skills.Any(skill => skill.Id == skillId));
}

    I am not sure about it's perfomance, but works pretty fast with small amount of data.


  2. 0

    It is going to be a difficult, if not impossible task, to run a query like this on the sql server side.
    This is because to make this work on the SQL side, you would be grouping each set of employee skills into a single row which would need to have a new column for every skill listed in the skills table.
    SQL server wasn’t really made to handle grouping with an unknown set of columns passed into a query. Although this kind of query is technically possible, it’s probably not very easy to do through a model binding framework like ef core.

    It would be easier to do this on the .net side using something like:

    var employees = _context.Employees.Include(x=>x.Skill).ToList();
var filter = someFilter;
var result = employees.Where(emp => filter.All(skillID=> emp.skills.Any(skill=>skill.ID == skillID))).ToList()
    Login or Signup to reply.
  3. 0

    I’ve also encountered this issue several times now, this is the query I’ve come up with that I found works best and does not result in an exception.

    query.Where(e => e.Skills.Where(s => filter.SkillIds.Contains(s.Id)).Count() == filter.SkillIds.Count);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search