EF-Core Method Call in LINQ Performance

c# entity-framework entity-framework-core linq

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

1
0
8/1/2017 1:25:50 PM

Accepted Answer

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.

3
8/1/2017 1:04:06 PM

Popular Answer

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)
     );


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow