0

I'm porting an existing application that uses Entity Framework with a SQL Server database from .NET Framework to .NET Core. In EF6, you could do:

private async Task<IEnumerable<T>> GetByExpressionAsync(Expression<Func<T, bool>> where))
{
    return await _context.DbSet<T>().Where(where).ToListAsync();
}

Assuming that the where expression could be evaluated server-side, this would translate to a SQL query like SELECT columns FROM table WHERE Foo=1.

This is the same method in EF Core. Yes, I realize I'm not taking advantage of async streams, but remember this is an existing application and I don't have the time to really redesign it.

private async Task<IEnumerable<T>> GetByExpressionAsync(Expression<Func<T, bool>> where))
{
    return await _context.DbSet<T>().AsAsyncEnumerable().Where(where.Compile()).ToListAsync();
}

The table that I'm having the problem with has hundreds of thousands of records. If I use SQL Profiler, I can see that there is no WHERE clause, so SQL Server is returning the entire table, which is of course so not ideal. I didn't notice at first because my unit tests run against a much smaller table so there was no indication there would be a performance issue.

According to the Microsoft documentation, "The database provider identifies which parts of the query can be evaluated in the database" and "These parts of the query are translated to database-specific query language". So when I use simple [column] == [value] filters, this should work properly, and indeed, the synchronous works exactly as expected.

private IEnumerable<T> GetByExpression(Expression<Func<T, bool>> where))
{
    return _context.DbSet<T>().AsQueryable().Where(where).ToList();
}

What am I doing wrong? Thanks in advance.

howcheng
  • 2,211
  • 2
  • 17
  • 24
  • 2
    don't do `AsAsyncEnumerable` with a where - that will be super greedy – Daniel A. White Dec 27 '20 at 02:46
  • @DanielA.White Yep, that's what I observed, but what am I supposed to do? I can't use `await _context.DbSet().AsQueryable().Where(where).ToListAsync()` because I get "The source IQueryable doesn't implement IAsyncEnumerable" (see https://stackoverflow.com/questions/48743165/toarrayasync-throws-the-source-iqueryable-doesnt-implement-iasyncenumerable) – howcheng Dec 27 '20 at 03:11
  • Can you talk us through what happens if you use the EF6 code in EF Core? Does it work? If it does work, why are you unhappy with it? – mjwills Dec 27 '20 at 03:22
  • @mjwills The EF6 code flat-out doesn't work in EF Core. It won't compile because of an AmbiguousMatchException (see https://stackoverflow.com/questions/60347952/ambiguous-call-when-using-linq-extension-method-on-dbsett), so you have to add `.AsQueryable()` and `.AsAsyncEnumerable()`. – howcheng Dec 27 '20 at 08:29
  • 1
    @howcheng Then just add `AsQueryable()`after the `DbSet` accessor to resolve the ambiguous call, and keep the rest the same as in EF6, e.g. `return await _context.Set().AsQueryable().Where(where).ToListAsync();` – Ivan Stoev Dec 27 '20 at 09:56
  • @IvanStoev OMG I swear I tried that already and it did nothing, but now it seems to work. WTF. Well, thanks for the pointer. – howcheng Dec 27 '20 at 22:47

0 Answers0