How make query for getting some objects from many-to-many model, using EF core?
.net-core c# entity-framework-core
Question
I try to do it like this:
var articles = db.Articles.Include(at => at.ArticleTags)
.ThenInclude(t => t.Tag)
.ToList();
var articlesTag = articles.Where(a => a.ArticleTags.Tag.TagValue == "news").ToList();
But it dont work. Visual Studio does not see this expression "a.ArticleTags.Tag.TagValue". How can I do it right?
Here is the implementation of the classes used:
public class Tag
{
public int Id { get; set; }
public string TagValue { get; set; }
public List<ArticleTag> ArticleTags { get; set; }
public List<UserTag> UserTags { get; set; }
public Tag()
{
ArticleTags = new List<ArticleTag>();
UserTags = new List<UserTag>();
}
}
public class Article
{
public int Id { get; set; }
public string Title { get; set; }
public string Summary { get; set; }
public List<ArticleTag> ArticleTags { get; set; }
public Article()
{
ArticleTags = new List<ArticleTag>();
}
}
public class ArticleTag
{
public int ArticleId { get; set; }
public Article Article { get; set; }
public int TagId { get; set; }
public Tag Tag { get; set; }
}
Accepted Answer
The relationship between the tables Role and Employee is represented as a navigation property - each Employees property in the Role entity will only contain the Employees that have this particular role.
Putting it the other way round - every Employee's Roles property only contains the roles that particular employee has.
Given a set of roles roleIds to look for you can use this to get the list of employees that have a role within that set:
public IQueryable<Employee> GetEmployeesForRoles(int[] roleIds)
{
var employees = _entities.Employees
.Where( x=> x.Roles.Any(r => roleIds.Contains(r.RoleID)))
return employees;
}
Edit:
The other way to get the employees is to attack the problem from the other side of the relationship (starting with the role, not the employee). This is most likely not as efficient as the first approach, since we have to de-duplicate employees (otherwise employees with i.e. two roles would show up twice):
public IQueryable<Employee> GetEmployeesForRoles(int[] roleIds)
{
var employees = _entities.Roles
.Where( r => roleIds.Contains(r.RoleID))
.SelectMany( x=> x.Employees)
.Distinct()
return employees;
}
Popular Answer
Maybe?
var results = from r in db.Roles
where roleIds.Contains(r.Id)
select r.Employees;
Related Questions
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow