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.
CodePudding user response:
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();