Linq lambda with group by having count

Question

I don't know how to use it in C#.

SELECT TOP 1 Categories.CategoryName  
FROM Headings JOIN Categories ON Categories.CategoryID = Headings.CategoryID 
group by Categories.CategoryName HAVING COUNT(Categories.CategoryID) > 1

welcome to stackoverflow mahmut! have you taken a [tour] and learn [ask]? it would be best if you could also provide [mcve] which include at least 1) the table structure; 2) sample input data; 3) expected output. — Bagus Tesa, Oct 02 '22 at 09:53
roughly from what i can see from your sql, you will definitely need [`GroupBy`](https://learn.microsoft.com/en-us/dotnet/api/system.linq.enumerable.groupby?view=net-7.0), [`Select`](https://learn.microsoft.com/en-us/dotnet/api/system.linq.enumerable.select?view=net-7.0), and [`Take`](https://learn.microsoft.com/en-us/dotnet/api/system.linq.enumerable.take?view=net-7.0). — Bagus Tesa, Oct 02 '22 at 09:55
Unfortunately someone linked your question to a GroupBy question and prevented details in an answer. You don't need GroupBy, Take or something like that. It is simply: var categoryName = db.Categories .FirstOrDefault(c => c.Headings.Count() > 1) ?.CategoryName; — Cetin Basoz, Oct 02 '22 at 10:05
The question was closed while only this SQL statement was in view. The LINQ statement added later shows that there's a navigation property `c.Headings` which can be used instead of grouping, as pointed out. Usually, when people wonder What the LINQ equivalent of `HAVING` is they need to see learn there is none and that a `Where()` on the count must be used. — Gert Arnold, Oct 02 '22 at 18:11
@GertArnold, only that SQL was enough to write that Linq which was the question itself. You shoouldn't close it adding an unrelated question about group by. As you saw, the Linq you deleted doesn't use GroupBy at all which is not needed anyway. Please don't close questions just because you can, and don't remove parts in edited question just because you can. It was the solution already. — Cetin Basoz, Oct 05 '22 at 12:41
@GertArnold, oh sorry it was edited by someone else who doesn't know what he is doing. I hate people doing this just because they can without any respect to posters. No explanation, nothing just delete what is there and correct. — Cetin Basoz, Oct 05 '22 at 12:46
@jmarkmurphy, Please reverse your edit. That was unrespectful. — Cetin Basoz, Oct 05 '22 at 12:47
@CetinBasoz Don't worry. Mahmut, please keep in mind that just a SQL statement is not enough to offer a C# solution. We have to assume too much to help you. In the first place there must be some ORM in play, we don't know which. In the second place you must have created a class model that's mapped to the database. We don't see that class model. As you've seen, it's important to know if you have navigation properties or grasp the concept of navigation properties. This is not nitpicking. Not all ORMs support what's suggested in the comments. — Gert Arnold, Oct 05 '22 at 13:08
As a matter of fact, the question would probably have been closed because of lacking information. I closed it as duplicate of something similar because I thought that was more helpful. I'll open it when the lacking info is supplied because the other close reason still stands. That's nothing but Stack Overflow consensus. — Gert Arnold, Oct 05 '22 at 13:13
@GertArnold, of course just an SQL statement is enough for a C# solution at times, as it was in this case. It is you unfortunate closing the question that created all the fuss. And another unfortunate edit wiped out the solution added to the question itself. — Cetin Basoz, Oct 05 '22 at 14:12
@CetinBasoz I put in a re-open vote so you can reopen and add your answer as an answer rather than an edit to the question. — jmarkmurphy, Oct 05 '22 at 18:49

score 0 · Answer 1 · answered Oct 05 '22 at 19:32

This would translate to:

var categoryName = db.Categories 
     .FirstOrDefault(c => c.Headings.Count() > 1) 
     ?.CategoryName;

Provided you have your relations defined in the db, generated model would have navigational properties and with Linq you seldom need a join.

Linq lambda with group by having count

1 Answers1