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; }
}
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;
}
Maybe?
var results = from r in db.Roles
where roleIds.Contains(r.Id)
select r.Employees;