Get number of days between 2 dates in Entity Framework

Question

I'd like to order rows by an integer + number of days passed since today.

However this:

var now = DateTime.UtcNow;

db.Items.OrderBy(x => x.SomeInteger + (x.Date - now).Days);

Gives the following warning:

The LINQ expression 'orderby' could not be translated and will be evaluated locally.

In .NET framework it was possible to do this: DbFunctions.DiffDays

What's the equivalent in ASP.NET core?

Answer 1

From pull request use

var lstPeople = cxt
.People.Where(p => EF.Functions.DateDiffDay(p.Birth, DateTime.Now) > 0 )
.ToList();

Answer 2

I've opened this issue on github and it looks like it's now fixed and will be available in 2.1.

https://github.com/aspnet/EntityFrameworkCore/issues/10468

Workaround:

public static class DbUtility
{
    public static int DateDiff(string diffType, DateTime startDate, DateTime endDate)
    {
        throw new InvalidOperationException($"{nameof(DateDiff)} should be performed on database");
    }
}

In ApplicationDbContext:

protected override void OnModelCreating(ModelBuilder builder)
{
    base.OnModelCreating(builder);

    builder.HasDbFunction(typeof(DbUtility)
        .GetMethod(nameof(DbUtility.DateDiff)))
        .HasTranslation(args => {
            var newArgs = args.ToArray();
            newArgs[0] = new SqlFragmentExpression((string)((ConstantExpression)newArgs[0]).Value);
            return new SqlFunctionExpression(
                "DATEDIFF",
                typeof(int),
                newArgs);
        });
}

Used like this:

DbUtility.DateDiff("day", x.DateAdded, now)