Has anyone noticed that EF Core 1.0 2015.1 made queries very inefficient

Question

After migrating to Asp.Net Core 2015.1, I noticed that many EF requests started to run much slower.

I did some research and found that many filter queries are now evaluated in code, rather than passing filters to SQL as part of the where clause to execute the query.

As a result, we had to rewrite some of our queries as stored procedures in order to return performance. Please note that they were effective before the release of version 2015.1. Something has obviously been changed, and many queries select all the queries in the table and then filter the data in the code. This approach is terrible for performance, for example. reading a table with a lot of rows to filter everything, but maybe 2 rows.

I have to ask what has changed, and does anyone see the same thing?

For example: I have a ForeignExchange table along with a ForeignExchangeRate table that are linked through ForeignExchangeid = ForeignExchangeRate.ForeignExchangeId

 await _context.ForeignExchanges .Include(x => x.ForeignExchangeRates) .Select(x => new ForeignExchangeViewModel { Id = x.Id, Code = x.Code, Name = x.Name, Symbol = x.Symbol, CountryId = x.CountryId, CurrentExchangeRate = x.ForeignExchangeRates .FirstOrDefault(y => (DateTime.Today >= y.ValidFrom) && (y.ValidTo == null || y.ValidTo >= DateTime.Today)).ExchangeRate.ToFxRate(), HistoricalExchangeRates = x.ForeignExchangeRates .OrderByDescending(y => y.ValidFrom) .Select(y => new FxRate { ValidFrom = y.ValidFrom, ValidTo = y.ValidTo, ExchangeRate = y.ExchangeRate.ToFxRate(), }).ToList() }) .FirstOrDefaultAsync(x => x.Id == id); 

And I use this to get exchange rate editing data

Thus, the generated SQL is not expected. It generates the following 2 SQL statements to retrieve data

 SELECT TOP(1) [x].[ForeignExchangeId], [x].[ForeignCurrencyCode], [x].[CurrencyName], [x].[CurrencySymbol], [x].[CountryId], ( SELECT TOP(1) [y].[ExchangeRate] FROM [ForeignExchangeRate] AS [y] WHERE ((@__Today_0 >= [y].[ValidFrom]) AND ([y].[ValidTo] IS NULL OR ([y]. [ValidTo] >= @__Today_1))) AND ([x].[ForeignExchangeId] = [y].[ForeignExchangeId]) )FROM [ForeignExchange] AS [x] WHERE [x].[ForeignExchangeId] = @__id_2 

and

 SELECT [y0].[ForeignExchangeId], [y0].[ValidFrom], [y0].[ValidTo], [y0].[ExchangeRate] FROM [ForeignExchangeRate] AS [y0] ORDER BY [y0].[ValidFrom] DESC 

The second request is the one that causes slowness. If the table has many rows, then it essentially gets the entire table and filters the data in the code

This has changed in the latest version as it was used to work in RC EF versions

Another query I used was the following

  return await _context.CatchPlans .Where(x => x.FishReceiverId == fishReceiverId && x.FisherId == fisherId && x.StockId == stockId && x.SeasonStartDate == seasonStartDate && x.EffectiveDate >= asAtDate && x.BudgetType < BudgetType.NonQuotaed) .OrderBy(x => x.Priority) .ThenBy(x => x.BudgetType) .ToListAsync(); 

and this query finished reading the table (the whole table, which was in tens of thousands of rows) to get a subset of the filter from 2 to 10 records. Very inefficient. This was one request that I had to replace with a stored procedure. Decreases from 1.5-3.0 seconds to milliseconds. And note that this was used to work efficiently before the update

Answer 1

This is a known issue in EF core 1.0 . The solution right now is to convert all your critical requests to sync one. The problem is Async requests right now. They will address this issue on EF core 1.1.0 version.But not yet released.

Here is a test performed by a member of the EF development team:

You can find more information here: EF Core 1.0 RC2: asynchronous requests are much slower than synchronization

Another suggestion I would like to make. Try your queries with .AsNoTracking() . It will also improve query performance.

.AsNoTracking ()

Sometimes you may need to return entities from a query, but not have these objects tracked by context. This can lead to better performance when querying a large number of objects in a read-only script.