Efcore: The 'DateDiffDay' method is not supported because the query has switched to client-evaluation.

Created on 16 Sep 2020 · 12Comments · Source: dotnet/efcore

After updating our package references to

<PackageReference Include="Microsoft.EntityFrameworkCore.InMemory" Version="5.0.0-rc.1.20451.13" />
<PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="5.0.0-rc.1.20451.13" />
<PackageReference Include="Microsoft.EntityFrameworkCore.Tools" Version="5.0.0-rc.1.20451.13">

Some of our queries which contain EF.Functions.DateDiffDay then fail when being tested against our InMemory database with the error message

Error Message:
System.InvalidOperationException : The 'DateDiffDay' method is not supported because the query has switched to client-evaluation. Inspect the log to determine which query expressions are triggering client-evaluation.
Stack Trace:
at Microsoft.EntityFrameworkCore.SqlServerDbFunctionsExtensions.DateDiffDay(DbFunctions _, DateTime startDate, DateTime endDate)
at lambda_method13273(Closure , ValueBuffer )
at System.Linq.Enumerable.SelectEnumerableIterator2.MoveNext() at Microsoft.EntityFrameworkCore.InMemory.Query.Internal.InMemoryShapedQueryCompilingExpressionVisitor.QueryingEnumerable1.Enumerator.MoveNextHelper()
at Microsoft.EntityFrameworkCore.InMemory.Query.Internal.InMemoryShapedQueryCompilingExpressionVisitor.QueryingEnumerable1.Enumerator.MoveNextAsync() at System.Runtime.CompilerServices.ConfiguredCancelableAsyncEnumerable1.Enumerator.MoveNextAsync()
at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsyncTSource
at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToArrayAsyncTSource

Could you confirm if EF.Functions.DateDiffDay should be available when using the InMemory provider?

Further technical details

EF Core version:
Database provider: (e.g. Microsoft.EntityFrameworkCore.InMemory)
Target framework: (e.g. .NET 5.0 rc1)
Operating system: Windows 10
IDE: dotnet test

closed-by-design customer-reported

Source

DavidBetteridge

All 12 comments

Could you confirm if EF.Functions.DateDiffDay should be available when using the InMemory provider?

It will not be. DateDiffDay method translates to DATEDIFFDAY function in T-Sql function. It will throw error if used with any provider other than SqlServer.

smitpatel on 16 Sep 2020

@smitpatel assume you mean other than SQL Server provider

ErikEJ on 16 Sep 2020

👍1

Thanks @ErikEJ. Corrected.

smitpatel on 16 Sep 2020

Here is a simple example with works with 3.1.8 but not with 5.0.0-rc.1.20451.13

It might just be luck it works with 3.1.8 as I agree that EF.Functions.DateDiffDay is in Microsoft.EntityFrameworkCore.SqlServer not Microsoft.EntityFrameworkCore.InMemory!

` class Program
{
static void Main(string[] args)
{
var ctx = new Context();
ctx.BookLoans.AddRange
(
new BookLoan { Title = "Yesterday", DateBorrowed = new DateTime(2020, 9, 15) },
new BookLoan { Title = "LastWeek", DateBorrowed = new DateTime(2020, 9, 8) },
new BookLoan { Title = "LastMonth", DateBorrowed = new DateTime(2020, 8, 15) }
);
ctx.SaveChanges();

        var overDueBooks = ctx.BookLoans.Where(b => EF.Functions.DateDiffDay(b.DateBorrowed, DateTime.Now) > 30).ToArray();
        foreach (var book in overDueBooks)
        {
            Console.WriteLine(book.Title);
        }
        Console.ReadLine();
    }
}

class BookLoan
{
    [Key]
    public int Id { get; set; }
    public string Title { get; set; }

    public DateTime DateBorrowed { get; set; }
}

class Context : DbContext
{
    public DbSet<BookLoan> BookLoans { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseInMemoryDatabase("TESTDB");
    }
}`

DavidBetteridge on 16 Sep 2020

If it was working with InMemory in 3.1 then it was a bug. We intentionally decided to make it SqlServer specific since we cannot reliably implement database functions in client side.

smitpatel on 16 Sep 2020

@smitpatel There were client-side implementations of this and related methods in 3.1. We should document this as a breaking change.

ajcvickers on 17 Sep 2020

👍1

@ajcvickers https://github.com/dotnet/EntityFramework.Docs/pull/2651

smitpatel on 17 Sep 2020

👍1

I'm getting this error against SQL Server from this pseudocode:

int value = EFModel.RelatedCollection.Sum(m => EF.Functions.DateDiffDay(m.DateField1, m.DateField2) + 1);

Here are the relevant packages (no in-memory provider is configured):

<PackageReference Include="Microsoft.EntityFrameworkCore" Version="5.0.0-rc.2.20475.6" />
<PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="5.0.0-rc.2.20475.6">
  <PrivateAssets>all</PrivateAssets>
  <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
</PackageReference>
<PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="5.0.0-rc.2.20475.6" />
<PackageReference Include="Microsoft.EntityFrameworkCore.Tools" Version="5.0.0-rc.2.20475.6">
  <PrivateAssets>all</PrivateAssets>
  <IncludeAssets>runtime; build; native; contentfiles; buildtransitive</IncludeAssets>
</PackageReference>