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?

Why are you grouping by ActionId in the Linq when you aren't in the SQL? — juharr
– juharr, Commented Nov 25, 2015 at 16:00

Sergey Kalinichenko · Accepted Answer · 2015-11-25 16:02:11Z

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

Can you help me how to get Count on a datefield? I have some student data with their exam dates. I wanna count the dates where the count is greater than two. Date field doesnt have a date.Count()?

ocuenca · Accepted Answer · 2015-11-25 15:59:25Z

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();

Collectives™ on Stack Overflow

Linq query using group by and having

2 Answers 2

2 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Related