How to perform subquery,group by and aggregate together using lambda or LINQ in C#

Question

I'm trying to perform groupby with aggregate function inside a subquery using lambda expression .I've manage to write the query using SQL but failing to do the same using Lambda expression. How could we write the same query using Lambda or may be 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
    )

Below is the model representation

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; } 
}

Perhaps my [SQL to LINQ Recipe](https://stackoverflow.com/questions/49245160/sql-to-linq-with-multiple-join-count-and-left-join/49245786#49245786) might help you. — NetMage, Jan 26 '21 at 00:55
BTW, it is really helpful to tell what LINQ you are using: LINQ to SQL / EF 6.x / EF Core 2.0 / 2.1 / 3.x / 5.x and what database provider. — NetMage, Jan 26 '21 at 00:56
Are `table1` and `table2` actually the same table? If not, where is the model for both tables? — NetMage, Jan 26 '21 at 01:02

score 0 · Answer 1 · answered Jan 26 '21 at 01:08

Using my SQL to LINQ Recipe, here is my translation of your SQL query:

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
          };

How to perform subquery,group by and aggregate together using lambda or LINQ in C#

1 Answers1