Entity Framework Core don't generate Group By SQL instruction?

c# entity-framework-core linq npgsql

Question

I have the following table:

enter image description here

I need to get 3 lists:

  • all advertisers (distinct);
  • all categories (distinct);
  • all package ids (distinct).

I have the following LINQ:

var filtersData = from push in _pushRepository.Pushes.Select(x =>  new
                {
                    x.Advertiser,
                    x.Category,
                    x.PackageId
                })
                group push by new
                {
                    push.Advertiser,
                    push.Category,
                    push.PackageId

                }
                into gr
                select  new
                {
                    advertisers = gr.Select(x => x.Advertiser).Distinct(),
                    categories = gr.Select(x => x.Category).Distinct(),                    
                    appIds = gr.Select(x => x.PackageId).Distinct()
                };

It's pretty close to this SQL query:

        SELECT x."Advertiser", x."Category", x."PackageId"
FROM "Pushes" AS x
group by  x."Advertiser", x."Category", x."PackageId"

But without distinct.

And I have switched on query logging for npgsql. It was surprise for me, but the LINQ instruction don't generate Group by. Please, see console debug output:

enter image description here

Is it correct behavior and EF Core will make grouping with the help of inner mechanisms or there is my mistake in the LINQ?

P.S. I use .NET Core 2.2

1
0
12/4/2019 10:48:12 AM

Popular Answer

As @gert-arnold wrote in the comment, your usage of GroupBy cannot be translated to SQL - an aggregate operator must be applied for that. Read the documentation to better understand which cases can be translated by EF Core.

You can still perform the GroupBy in the client side by inserting an AsEnumerable before the GroupBy operator.

1
12/4/2019 1:40:38 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow