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.

I was trying stuff like this : _DataAccess.SpecialitiesTable.Select(...).GroupBy(...) — Hyroglatos_7700
– Hyroglatos_7700, Commented Aug 19, 2022 at 10:31
Show this query in question. It is really easy to search SO and find GroupBy and Count — Svyatoslav Danyliv
– Svyatoslav Danyliv, Commented Aug 19, 2022 at 10:33

Svyatoslav Danyliv · Accepted Answer · 2022-08-19 10:41:39Z

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

answered Aug 19, 2022 at 10:41

Svyatoslav Danyliv

28.2k4 gold badges24 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Hyroglatos_7700 Over a year ago

worked like a charm. basically i get confused on how to set the lambda experssions

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related