EF Core Implement sql server DATEPART as DbFunction static method

c# entity-framework-core linq

Question

I am trying to add the DATEPART sql-server function to Entity Framework Core by adding it as a static method on DbContext with the [DbFunction] attribute as described here https://docs.microsoft.com/en-us/ef/core/what-is-new/ef-core-2.0

The problem is that sql-server receives the datepart parameter as a string and it cant run it because the datepart parameter can't be a string (based on https://docs.microsoft.com/en-us/sql/t-sql/functions/datepart-transact-sql?view=sql-server-2017 "Note DATEPART does not accept user-defined variable equivalents for the datepart arguments.")

i have also tried removing the double quotes from the argument i pass to datepart, by doing sting.Replace("\"", "") but it still does not work (it change the argument from "'arg'" to 'arg')

Here is my code:

[DbFunction("DATEPART","")]
public static int DATEPART(string datepart, DateTime date)
{
    throw new NotSupportedException();
}

Is there any other data type that will work? am i missing something?

Thanks

1
0
12/26/2018 10:44:27 PM

Accepted Answer

It is possible to make use of the datepart SQL function by wrapping it with the DbFunctionAttribute. 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.Where(x => dbContext.DatePart("weekday", x.CreatedAt) == DayOfWeek.Monday);

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

0
6/7/2019 8:50:03 AM


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