Linq query using group by and having

Question

So, I've been 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 it should not be right because I am not selecting the actual count greater to 1 in my group clause.

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

Answer 1

LINQ makes no difference between Where and Having. An appropriate SQL query would be generated based on your placement of the Where clause.

Here is how I would translate your SQL query to 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

Answer 2

Change Key.ActionId for .Count() in the where after the 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();