Martin Martin - 4 years ago 127
C# Question

Linq count condition in query

I am stuck on this problem. I want to get users who are in the "Developer" role, who either are not assigned any tasks or are assigned 3 or fewer tasks which are marked "Active" or "Testing".

I don't know how to make a statement where I get all tasks with Status "Active, Testing" and sum them up for my count statement.

//here i specific role for users
var role = unitOfWork.RoleRepository._context.Roles.SingleOrDefault(m => m.Name == "Developer");

var query = (from u in users
where u.Roles.Any(r => r.RoleId == role.Id)
from t in u.ProjectTasks.Where(x => x.Users.Any(user => user.Id == u.Id)).DefaultIfEmpty()
//here I am able to get those users without assigned tasks but I dont know how to make the second condition with 3 or less tasks
where ((u.ProjectTasks.Count() == 0) || u.ProjectTasks.Any(z => z.Status == Status.Active || z.Status == Status.Testing))
select new { User = u } into Users
group Usersby Users.User).ToList();


Here is my database model:
enter image description here

Answer Source

Perhaps the LINQ Count function is called for:

var query = (from u in users
     where u.Roles.Any(r => r.RoleId == role.Id)
     from t in u.ProjectTasks.Where(x => x.Users.Any(user => user.Id == u.Id)).DefaultIfEmpty()
     where ((u.ProjectTasks.Count() == 0) || u.ProjectTasks.Count(z => z.Status == Status.Active || z.Status == Status.Testing) <= 3)
     select new { User = u } into Users
     group Usersby Users.User).ToList(); 

For examples of usages: http://www.csharp-examples.net/linq-count/

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download