Entity framework 5.0 First or Group By Issue- After upgrading from 2.2 to 5.0

Question

I have a table called Products and I need to find the products with unique title for a particular category. Earlier we used to do with this query in entity framework core 2.2 :

currentContext.Products
              .GroupBy(x => x.Title)
              .Select(x => x.FirstOrDefault()))
              .Select(x => new ProductViewModel
                 {
                     Id = x.Id,
                     Title = x.Title,
                     CategoryId= x.CategoryId
                 }).ToList();

But after upgrading to Entity Framework Core 5.0, we get an error for Groupby Shaker exception:

The LINQ expression 'GroupByShaperExpression:KeySelector: t.title, ElementSelector:EntityShaperExpression: EntityType: Project ValueBufferExpression: ProjectionBindingExpression: EmptyProjectionMember IsNullable: False .FirstOrDefault()' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'.

I know there are multiple way to client projection but I am searching for most efficient way to search.

Answer 1

Most likely that LINQ query couldn't be translated in EF Core 2.2 either, because of some limitations that the GroupBy operator has.

From the docs:

Since no database structure can represent an IGrouping, GroupBy operators have no translation in most cases. When an aggregate operator is applied to each group, which returns a scalar, it can be translated to SQL GROUP BY in relational databases. The SQL GROUP BY is restrictive too. It requires you to group only by scalar values. The projection can only contain grouping key columns or any aggregate applied over a column.

What happened in EF Core 2.x is that whenever it couldn't translate an expression, it would automatically switch to client evaluation and give just a warning.

This is listed as the breaking change with highest impact when migrating to EF Core >= 3.x :

Old behavior

Before 3.0, when EF Core couldn't convert an expression that was part of a query to either SQL or a parameter, it automatically evaluated the expression on the client. By default, client evaluation of potentially expensive expressions only triggered a warning.

New behavior

Starting with 3.0, EF Core only allows expressions in the top-level projection (the last Select() call in the query) to be evaluated on the client. When expressions in any other part of the query can't be converted to either SQL or a parameter, an exception is thrown.

So if the performance of that expression was good enough when using EF Core 2.x, it will be as good as before if you decide to explicitly switch to client evaluation when using EF Core 5.x. That's because both are client evaluated, before and now, with the only difference being that you have to be explicit about it now. So the easy way out, if the performance was acceptable previously, would be to just client evaluate the last part of the query using .AsEnumerable() or .ToList().

If client evaluation performance is not acceptable (which will imply that it wasn't before the migration either) then you have to rewrite the query. There are a couple of answers by Ivan Stoev that might get you inspired.

I am a little confused by the description of what you want to achieve: I need to find the products with unique title for a particular category and the code you posted, since I believe it's not doing what you explained. In any case, I will provide possible solutions for both interpretations.

This is my attempt of writing a query to find the products with unique title for a particular category.

var uniqueProductTitlesForCategoryQueryable = currentContext.Products
              .Where(x => x.CategoryId == categoryId)
              .GroupBy(x => x.Title)
              .Where(x => x.Count() == 1)
              .Select(x => x.Key); // Key being the title

var productsWithUniqueTitleForCategory = currentContext.Products
              .Where(x => x.CategoryId == categoryId)
              .Where(x => uniqueProductTitlesForCategoryQueryable .Contains(x.Title))
              .Select(x => new ProductViewModel
                 {
                     Id = x.Id,
                     Title = x.Title,
                     CategoryId= x.CategoryId
                 }).ToList();

And this is my attempt of rewriting the query you posted:

currentContext.Products
              .Select(product => product.Title)
              .Distinct()
              .SelectMany(uniqueTitle => currentContext.Products.Where(product => product.Title == uniqueTitle ).Take(1))
              .Select(product => new ProductViewModel
                 {
                     Id = product.Id,
                     Title = product.Title,
                     CategoryId= product.CategoryId
                 })
              .ToList();

I am getting the distinct titles in the Product table and per each distinct title I get the first Product that matches it (that should be equivalent as GroupBy(x => x.Title)+ FirstOrDefault AFAIK). You could add some sorting before the Take(1) if needed.