Translate an postgresql query to entityframework core in asp.net core

Question

I am trying to translate the following Postgresql query in a dotnet api application using entityframework core in a way to return a list of objects containing the results in the output of that query.

sql query to translate:

select speciality_type, speciality_priority , 
   count(speciality_type)
from specialties
group by speciality_priority, speciality_type

desired output:

knowing that the last column is the count column witch is not initially in the table and that I created in the query. I want to get all these columns from _DataAccessObject.SpecialitiesTable and create from its values another list of an object that I create object(string type , int priority , int count)

I tried a lot of stuff like _DataAccess.SpecialitiesTable.GroupBy(...).Select(...) most of them give me a error once function is called saying that the EF core statement could not be translated to sql.

Thank you in advance.

Answer 1

Try the following query:

var result = await _DataAccess.SpecialitiesTable
   .GroupBy(s => new { s.speciality_type, s.speciality_priority })
   .Select(g => new 
   {
      g.Key.speciality_type,
      g.Key.speciality_priority,
      count = g.Count()
   })
   .ToListAsync();