LINQ to SQL的 - 有和GROUP BY [英] Linq To SQL - Having and Group By

问题描述

我有以下工作正常此查询。不过，我想使用LINQ来实现它。

I've this query below working fine. However I want to implement it using Linq.

select u.ID, u.NAME
from Task t 
join BuildingUser bu ON bu.ID_BUILDING = t.ID_BUILDING 
join [User] u ON u.ID = bu.ID_USER
where t.ID IN (2,9) AND u.ID != t.ID_USER
group by u.id, u.name
having count(bu.ID_BUILDING) = (SELECT COUNT(t2.ID_BUILDING) FROM Task t2 WHERE t2.ID IN (2,9))

我不知道该怎么集团在同一时间使用HAVING子句。

I don't know how to Group and use Having clause at the same time.

推荐答案

您可以尝试这样的事情：

You can try something like this:

var ids = new[] { 2, 9 };
var results =
    from t in db.Tasks
    join bu in db.BuildingUsers on t.ID_BUILDING equals bu.ID_BUILDING
    group bu by bu.ID_BUILDING into bg
    join u in db.Users on bg.Key equals u.ID
    where ids.Contains(t.ID) && u.ID != t.ID_USER
    group u by new { u.ID, u.NAME } into g
    where bg.Count() == db.Tasks.Count(t2 => ids.Contains(t2.ID))
    select g.Key;

或者，如果你有正确设置导航属性，你可以试试这个：

Or if you have navigation properties set up correctly, you can try this:

var ids = new[] { 2, 9 };
var results =
    from t in db.Tasks.Where(x => ids.Contains(x.ID))
    from u in t.BuildingUsers.SelectMany(bu => bu.Users)
                             .Where(x => x.ID != t.ID_USER)
    group u by new { u.ID, u.NAME } into g
    where t.BuildingUsers.Count() == db.Tasks.Count(x => ids.Contains(x.ID))
    select g.Key;

这篇关于LINQ to SQL的 - 有和GROUP BY的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！