EF-Core Method Call in LINQ Performance

Question

this problem is not about async problems with entityframework itself as discussed here.

In the method CalculateSomething you can see two LINQ-Calls. The performance of the first LINQ-Call (initializing result) is absolutely okay.

However, the performance of the second LINQ-Call (initializing resultWithDate) Is way slower than the first one.

The first one takes 2 Seconds, The second one takes 15-20 Seconds.

dataBase is my DbContext class. Iam using Entity Framework Core.

  private async Task<long> CalculateSomething(string numberOne, MyStatus status)
  {
     var result = await this.dataBase.Something.CountAsync(item => item.NumberOne== numberOne && item.Status == (short)status);
     var resultWithDate = await this.dataBase.Something.CountAsync(item => item.NumberOne== numberOne && item.Status == (short)status && !this.IsOlderThan30Days(item.Date));

     return result;
  }

  private bool IsOlderThan30Days(DateTime? itemDate)
  {
     bool result = true;

     if (itemDate.HasValue) 
     {
        if ((DateTime.Now - itemDate.Value).TotalDays <= 30)
        {
           result = false;
        }
     }

     return result;
  }

The problem is not the method call IsOlderThan30Days, the problem is about CountAsync. I know this because I had something like this:

  private async Task<long> CalculateAmountOfOrders(string numberOne, MyStatus status)
  {
     var result = this.dataBase.Something.Where(item => item.NumberOne == numberOne && item.Status == (short)status);
     var resultWithDate = this.dataBase.Something.Where(item => item.NumberOne == numberOne && item.Status == (short)status && !this.IsOlderThan30Days(item.Date));

     var resultCount = await result.CountAsync();
     var resultWithDateCount = await resultWithDate.CountAsync();

     return resultCount;
  }

And the performance loss appeared at the two CountAsync() calls. CountAsync on resultWithDateCount took 15 seconds while CountAsync on resultCount only took 2 seconds. initializing result and resultWithDate was equally fast.

Am I doing something wrong?

Thank you

Can you try to dispose the context after each operation and redo the test ? Something like `using(var db = new Context()) { countOperation(); }` Something else: are you sure your queries are executed through EF? Because I don't think EF can handle the `IsOlderThan30Days` through LinqToEntities... — Atlasmaybe, Aug 01 '17 at 12:11
`initializing result and resultWithDate was equally fast.` That is because in your latter code sample initialising `result` and `resultWithDate` doesn't actually do very much. See https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/ef/language-reference/query-execution . — mjwills, Aug 01 '17 at 12:13
Since you are using a method (`IsOlderThan30Days()`) in the seocond query it is executed in memory (since that private method cannot be translated to / executed in SQL). Therefor EF has to load all items before filtering using your where expression, which is most likely the cause for the performance difference. (Can you check the types of `result` and `resultWithDate`? If I'm correct then the first query is an `IQueryable` while the other one is an `IEnumarable`) — bassfader, Aug 01 '17 at 12:15
The problem is of course the `IsOlderThan30Days` call which causes client evaluation of the query. In EF6 it would have been a simple exception, but in EF Core it "just works". — Ivan Stoev, Aug 01 '17 at 12:15
Wow thank you! Could you answer this question so I can mark it as correct? — Moritz Schmidt, Aug 01 '17 at 12:16
both are IQueryable. Ivan Stoev can you show me how to do it correct in an answer please? Iam using Entity Framework Core 1.0.3 — Moritz Schmidt, Aug 01 '17 at 12:22
The problem is that you are trying to make operations on items not yet loaded from the database. EF can translate the frist one into sql so it's working just fine. But in the second case, it's much more complexe and i guess that EF can't translate it into sql. I'm very close de believe that EF is loading your items from the DB to be able to run the IsOlderThan30Days method. Obviously making it much and much slower. — JBO, Aug 01 '17 at 12:36
@MoritzSchmidt You need to (A) replace the `IsOlderThan30Days` method call with compatible expression (condition) directly inside the query lambda. And (B) the expression should use only recognizable (translatable) by EF Core CLR methods/properties. For instance, the answer by mjwills works in 1.1.2 — Ivan Stoev, Aug 01 '17 at 12:54

mjwills · Accepted Answer · 2017-08-01T13:04:06.717

3

Try this:

var date = DateTime.Now.AddDays(-30);
var result = await this.dataBase.Something.CountAsync(item => item.NumberOne == numberOne && item.Status == (short)status);
var resultWithDate = itemDate.HasValue ? await this.dataBase.Orders.CountAsync(item => item.NumberOne == numberOne && item.Status == (short)status && 
        itemDate.Value < date) : 0;

Or alternatively:

var date = DateTime.Now.AddDays(-30);
var result = await this.dataBase.Something.CountAsync(item => item.NumberOne == numberOne && item.Status == (short)status);
var resultWithDate = await this.dataBase.Orders.CountAsync(item => item.NumberOne == numberOne && item.Status == (short)status && 
        itemDate < date);

The key is trying to do the 30 day date calculation outside of the LINQ.

edited Aug 01 '17 at 13:04

answered Aug 01 '17 at 12:38

mjwills

23,389
6
40
63

Perfect answer! Worked for me! Thank you very much for your effort! – Moritz Schmidt Aug 01 '17 at 12:58
Emphasize should be more on using an inline expression to apply date filter rather than using private method. The moment you introduce method call in expression, EF Core can no longer evaluate insides of the method call hence it has to load data from database to run the method on each result. – Smit Aug 03 '17 at 00:45
Also you can inline date calculation since it would also be translated to SqlServer. If you define a variable then EF will just evaluate it in memory and stick it inside the expression tree before translating it. – Smit Aug 03 '17 at 00:48
@mjwills - Your solution is correct. I cannot think of anything better from top of my head. Even if there is something better than that it would be very little value. I was just trying to indicate reasons why initial query was not working as expected and why this solution does. In a way, how to rewrite the query to arrive at above solution. :) – Smit Aug 03 '17 at 04:23

JBO · Answer 2 · 2017-08-01T12:55:11.760

1

Ok here is a code inspired from @mjwills :

private async Task<long> CalculateSomething(string numberOne, MyStatus status)
{
  var date = DateTime.Now.AddDays(-30);

  var result = await this.dataBase.Something.CountAsync(item => 
  item.NumberOne == numberOne && item.Status == (short)status);
  var resultWithDate = await this.dataBase
    .Something
    .CountAsync(item => item.NumberOne == numberOne && item.Status == (short)status && (!item.Date.HasValue  || item.Date.Value <= date));

  return result;
}

edited Aug 01 '17 at 12:55

answered Aug 01 '17 at 12:39

JBO

270
1
6

1

pfff of course how silly i am... I saw the other answer and think their is something to get from it. I will try and open my vs studio to be able to make a more reliable answer. – JBO Aug 01 '17 at 12:42
@dasblinkenlight, i edited the answer with a new piece of code. I hope it will helped more than the first one. – JBO Aug 01 '17 at 12:55
Worked as well! Thank you! – Moritz Schmidt Aug 01 '17 at 13:03

score 1 · Answer 3 · answered Aug 01 '17 at 12:57

The problem is that IsOlderThan30Days forces loading data into memory. You should be able to gain some performance by doing the computation in the database:

var now = DateTime.Now;
var resultWithDate = await this.dataBase
     .Something
     .CountAsync(item =>
         item.NumberOne== numberOne
     &&  item.Status == (short)status
     &&  (item.Date != null && EntityFunctions.DiffDays(item.Date, now) <= 30)
     );

EF-Core Method Call in LINQ Performance

3 Answers3