Linq query using group and

Question

So, I am trying to replicate this SQL query:

 select COUNT(*) as Total_2_Review from ( select processed_image_id, count(*) as Total_Confirm from dbo.history where action_id=104 group by processed_image_id having count(*) > 1 ) as tbl 

with Linq as follows:

 var total2Review = (from h in secondDb.Histories.Where(i => i.ActionId == 104) group h by new { h.ActionId, h.ProcessedImageId } into g where g.Key.ActionId > 1 select g).Count(); 

However, I know that this should not be correct, because I do not select the actual quantity greater than 1 in the section of my group.

How can I execute this SQL query as a LINQ query?

Answer 1

Change Key.ActionId to .Count() in where after group by :

 var total2Review = (from h in secondDb.Histories.Where(i => i.ActionId == 104) group h by new { h.ActionId, h.ProcessedImageId } into g where g.Count()> 1 select g).Count(); 

Answer 2

LINQ makes no difference between Where and Having . The corresponding SQL query will be created based on your placement of the Where clause.

Here's how I would translate your SQL query into LINQ:

 var total2Review = secondDb.Histories .Where(i => i.ActionId == 104) // This "Where" remains a "where" .GroupBy(i => i.ProcessedImageId) .Where(g => g.Count() > 1) // This "Where" becomes "having" .Count(); // This gets the overall count