c# - 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.)

Answer 1

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.