Converting EF Core queries from 2.2 to 3.0 - async await

Question

In EF Core 2.2 I had:

      var data = await _ArticleTranslationRepository.DbSet
        .Include(arttrans => arttrans.Article)
        .ThenInclude(art => art.Category)
        .Where(trans => trans.Article != null && trans.Article.Category != null && trans.Article.Category.Id == categoryId.Value)
        .GroupBy(trans => trans.ArticleId)
        .Select(g => new { ArticleId = g.Key, TransInPreferredLang = g.OrderByDescending(trans => trans.LanguageId == lang).ThenByDescending(trans => trans.LanguageId == defaultSiteLanguage).ThenBy(trans => trans.LanguageId).FirstOrDefault() })
        .Select(at => at.TransInPreferredLang)
        .OrderBy(at => at.Article.SortIndex)
        .ToListAsync();

Now with EF Core 3.0 I had to write:

      var data = _ArticleTranslationRepository.DbSet
  .Include(arttrans => arttrans.Article)
  .ThenInclude(art => art.Category)
  .Where(trans => trans.Article != null && trans.Article.Category != null && trans.Article.Category.Id == categoryId.Value)
    .AsEnumerable() // client side groupby is not supported (.net core 3.0 (18 nov. 2019)
  .GroupBy(trans => trans.ArticleId)
  .Select(g => new { ArticleId = g.Key, TransInPreferredLang = g.OrderByDescending(trans => trans.LanguageId == lang).ThenByDescending(trans => trans.LanguageId == defaultSiteLanguage).ThenBy(trans => trans.LanguageId).FirstOrDefault() })
  .Select(at => at.TransInPreferredLang)
  .OrderBy(at => at.Article.SortIndex)
  .ToList();

My asp.net core mvc actionmethod is async (public virtual async Task<ICollection<…>>…) Because I used .AsEnumerable to force client side evaluation I also had to change .ToListAsync() to .ToList() and remove the await operator.

The query is working but produces a warning: This async method lacs 'await' operators and will run synchronously. Consider using the 'await operator ….

How can this EF Core 3.0 query be rewritten so that it uses async / await. I can't figure out how to include the AsAsyncEnumerable() in a single query/linq expression.

(I know that I can split it up in a 'server' part and a 'client-side' part, but I would like to see it in a single async linq expression as I had before in EF Core 2.2.)

Why did you need to force client-side evaluation? Instead of `AsEnumerable` use `ToListAsync` and store the result of that in a variable, then do the rest of the query on that. — DavidG, Nov 18 '19 at 14:28
because of " client side groupby is not supported" when using .GroupBy — juFo, Nov 18 '19 at 14:33
[LINQ queries are no longer evaluated on the client](https://learn.microsoft.com/en-us/ef/core/what-is-new/ef-core-3.0/breaking-changes#linq-queries-are-no-longer-evaluated-on-the-client) : _"Mitigations If a query can't be fully translated, then either rewrite the query in a form that can be translated, or use AsEnumerable(), ToList(), or similar to explicitly bring data back to the client where it can then be further processed using LINQ-to-Objects."_ — Fildor, Nov 18 '19 at 14:34
@Fildor: I have rewritten it, it works I'm using Asenumerable(). but it doesn't use async await anymore now. I can split it up indeed in a server side part and client side part but I'm wondering if I can write it async in a single linq "expression" using for example AsAsyncEnumerable. — juFo, Nov 18 '19 at 14:36
Ah, I missed the point. Yes, I agree, I don't see how that could be done without tearing it into two pieces. — Fildor, Nov 18 '19 at 14:41
because I can't to `await ....ToListAsync().GroupBy(…` I know I have to separate it in two blocks. It is just some "syntactical sugar" I'm searching for. — juFo, Nov 18 '19 at 14:46
You can do `(await /* ... */.ToListAsync()).GroupBy( /* ... */` - ie wrap the database-executed half in parentheses, awaiting it and then continue the chain on the result of the await - to do it in a single statement. — Jesper, Nov 18 '19 at 14:49
@Jesper I would rather do it on 2 lines for clarity. Using parens to do the await looks horribly messy, and hides the fact it's 2 distinct blocks unless you look carefully. — DavidG, Nov 18 '19 at 15:28
That's fair - you said you were looking for syntactical sugar, so I thought this was what you meant. — Jesper, Nov 18 '19 at 15:53

score 8 · Accepted Answer · answered Nov 18 '19 at 18:24

The idea seems to be combining the AsAsyncEnumerable() with System.Linq.Async package which provides equivalent LINQ (IEnumerable<T>) extension methods for IAsyncEnumerable<T>.

So by idea if you install (or package reference) that package, inserting .AsAsyncEnumerable() before .GroupBy, the original query in question should work.

There is an annoying issue though with EF Core 3.0 DbSet<T> class. Since it implements both IQueryable<T> and IAsyncEnumerable<T> interfaces, and none of them is "better" (closer) match, many queries using standard LINQ operators on DbSet<T> will simply break at compile time with CS0121 (ambiguous call) and will require adding .AsQueryable(). Queries which use EF Core specific extensions like Include / ThenInclude will work because they already resolve to IQueryable<T>.

As some people mentioned in the comments, it's possible to use (await [serverPart...].ToListAsync())[clientPart...] which eliminates the need of System.Linq.Async and associated compile time method ambiguity, but it has the same drawback as using ToList() instead of AsEnumerable() in the synchronous scenario by creating an unnecessary in-memory list.

Of course the best would be to avoid client evaluation at all by finding equivalent, but fully translatable LINQ construct. Which currently with GroupBy is hard, and sometimes even impossible. And even it is possible, it requires rewriting the previous query by eliminating the GroupBy. For instance, instead of starting the query from ArticleTranslation and grouping by ArticleId, you might start the query from Article and use the Translations collection navigation property with OrderByDescending()...FirstOrDefault() which is supported. Repeat the procedure for each failing query. The benefit will be that now your queries will execute server side as they should in the first place.

Why not using right tool for the job, EF Core LINQ translated to sql for simple queries and raw sql for `GroupBy` queries ;) — Fabio, Nov 19 '19 at 12:30

score 0 · Answer 2 · answered Feb 09 '22 at 05:07

I had the same problem with dotnet core 5.0 and 6.0 and I solved it like this:

Install the nuget package System.Linq.Async depending of you dotnet version https://www.nuget.org/packages/System.Linq.Async

and then in your query you would be able to add the .ToAsyncEnumerable()

This a example:

        public async Task<IEnumerable<ProductTableView>> GetProduct(Pagination pagination, string identifier)
    {

        var ListProductsTable = await _context.ModelView
                                       .FromSqlRaw("GetProductByIdentifier {0}", identifier)
                                       .ToAsyncEnumerable()
                                       .Select(a => new ProductTableView
                                       {
                                           ID = a.ID,
                                           Guid = a.Guid,
                                           Guid_Product_Category = a.Guid_Product_Category,
                                           Guid_Currency = a.Guid_Currency,
                                           NameProduct = a.NameProduct,
                                       }).ToListAsync();

        return ListProductsTable;
    }

If you are using EF Core, then `ToAsyncEnumerable()` is already in the package: `Microsoft.EntityFrameworkCore`. You don't need to *separately* install: `System.Linq.Async`. — JohnB, Apr 20 '22 at 01:46

Converting EF Core queries from 2.2 to 3.0 - async await

2 Answers2

Linked