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

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

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