如何在c#中使用lambda或LINQ执行子查询、group by和聚合

Question

我试图使用lambda表达式在子查询内执行聚合函数。我已经设法使用SQL编写查询，但未能使用lambda表达式做同样的事情。如何使用Lambda或LINQ

来编写相同的查询呢?

select 
[user_ID], FirstName, LastName, Phone, Fax, EmailAddress 
from table1  
where [user_id] in 
(select [user_id] 
from table2 group by [USER_ID]  
having (sum(case when isregistered is not null then 1 else 0 end)) = 0
)

下面是模型表示

public class AccountDetails
{
public string Username { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public string Phone { get; set; }
public string Fax { get; set; }
public string EmailAddress { get; set; }
public bool? IsRegistered { get; set; }
public string User_Id { get; set; } 
}

Answer 1

使用我的SQL到LINQ配方，这是我对您的SQL查询的翻译:

var subq = from a in table2
group a by a.User_Id into ag
where ag.Sum(a => a.IsRegistered != null ? 1 : 0) == 0
select ag.Key;
var ans = from a in table1
where subq.Contains(a.User_Id)
select new {
a.User_Id,
a.FirstName,
a.LastName,
a.Phone,
a.Fax,
a.EmailAddress
};