CODEWITHSUNDEEP
linqef-core-2.0
Sylvain C.
Sylvain C.

Reputation: 1073

GroupBy OrderBy Linq - invalid column

I am trying to build a very simple LinQ query used to return a json to my client.

the following works:

_context.MyTable
.GroupBy(g => new { type= g.TypeId })
.Select(x => x.Count())

However, I would like to retun the count list ordered by type so I wrote the following but it didn't work:

_context.MyTable
.GroupBy(g => new { type= g.TypeId })
.OrderBy(o => o.Key.type)
.Select(x => x.Count())

It returns a 'invalid column name "type"'

I don't get it, the orderby is occuring after the groupBy, I am using the key properly. It looks like, the orderBy needs the criteria in the Slect statement to work properly. For example, the following works:

_context.MyTable
.GroupBy(g => new { type= g.TypeId })
.OrderBy(o => o.Key.type)
.Select(x => {x.Count(), x.Key.type})

But it does not return what i want, I just want to get the count values ordered by type.

Any directions why I get this error.

I am using EF Core if it can help.

S.

Upvotes: 2

Views: 190

Answers (1)

Ivan Stoev
Ivan Stoev

Reputation: 205589

Although EF Core 2.1 introduced improvements to LINQ GroupBy translation, the implementation is still far from perfect and produces exceptions in many scenarios due to invalid SQL generation. Your scenario is just one of them and is not working even with the recent at this time EF Core 2.1.1 bits.

The first thing you should do is to submit it to the EF Core issue tracker, so they know and fix it when possible.

The problem seems to be related to the key property aliasing (it also doesn't work if you use the "normal" .GroupBy(e => e.TypeId).OrderBy(g => g.Key)). The current workaround I've found is to use anonymous type key having the same property name(s) as the source(s):

_context.MyTable
    .GroupBy(e => new { e.TypeId })
    .OrderBy(g => g.Key.TypeId)
    .Select(g => g.Count())

Upvotes: 2

Related Questions