Compare date with today on the server side with EntityFramework Core and linq to entities

Question

I'm trying to translate some code from EF6 to EF Core 1 RC1 and I'm stuck at the point on which I need to compare the dates with the current one, on the server side using linq. There is also another part of the query which was getting the day of week of these dates.

The EF6 code uses the SqlFunctions class which is fully qualified as System.Data.Entity.SqlServer.SqlFunctions, that afaik hasn't been ported to the new version.

What's the recommended approach or the alternative to keep this working on EF Core?

The original linq to entities query is like this:

var result = db.Logs.Select(log => new {
      Date = log.Date,
      Rel = System.Data.Entity.SqlServer.SqlFunctions.DateDiff("day", log.Date, System.Data.Entity.SqlServer.SqlFunctions.GetDate()),
      Dow = System.Data.Entity.SqlServer.SqlFunctions.DatePart("dw", log.Date) - 1 
});

score 7 · Accepted Answer · edited May 23 '17 at 11:46

7

For the moment, Sql Functions are not supported, here is the opened issue

Provide standard LINQ methods that correspond to standard SQL operations (something like DbFunctions from EF6)

Here are some alternatives you can try:

You can try using raw sql using context.set().FromSql("...").
Otherwise you can create computed colums on the db side and mark these columns as generated in the EF model

edited May 23 '17 at 11:46

Community

1
1

answered Apr 12 '16 at 04:47

Thomas

24,234
6
81
125

Could you please say, what is situation for now? That ticket is opened for now, but maybe it was resolved in some other way? – Ssss Dec 05 '16 at 14:24

score 4 · Answer 2 · answered Jun 07 '19 at 08:59

It is possible to make use of the datepart SQL function by wrapping it with the DbFunctionAttribute. Same is possible for datediff and so on. Tricky part is to tell ef core not to handle the datepart type parameter as a string. Example:

DbContext:

public int? DatePart(string datePartArg, DateTime? date) => throw new Exception();

public void OnModelCreating(DbModelBuilder modelBuilder) {
    var methodInfo = typeof(DbContext).GetRuntimeMethod(nameof(DatePart), new[] { typeof(string), typeof(DateTime) });
    modelBuilder
        .HasDbFunction(methodInfo)
        .HasTranslation(args => new SqlFunctionExpression(nameof(DatePart), typeof(int?), new[]
                {
                        new SqlFragmentExpression(args.ToArray()[0].ToString()),
                        args.ToArray()[1]
                }));
}

Query:

repository.GroupBy(x => dbContext.DatePart("week", x.CreatedAt));

some more info: https://github.com/aspnet/EntityFrameworkCore/issues/10404

Isn't that possible only in EF Core 3.1 which is still a preview? — cryss, Nov 26 '19 at 10:27
no its possible in ef core 2.x, in ef 3.0 there has been an issue which is fixed in 3.1: https://github.com/aspnet/EntityFrameworkCore/issues/17918 — cyptus, Nov 26 '19 at 10:52

Compare date with today on the server side with EntityFramework Core and linq to entities

2 Answers2