EF Core Implement sql server DATEPART as DbFunction static method

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://learn.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://learn.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

I don't think there's any way to use that function with EF Core other than writing manual SQL. — DavidG, Dec 26 '18 at 22:57
You could define a UDF that wraps datepart, then call the UDF from your code. The query compiler should inline the function call so there should be no performance impact. — Dai, Dec 26 '18 at 22:57
@Dai you mean a function in sql-server and that function will call DATEPART, but i in code will call my custom function — JG3, Dec 26 '18 at 23:05
Why would you want to do this? What would the code look like. If you're trying to change the value type (datetime to int) before c#, you're doing it in the wrong place. If you're trying to use it for a where clause, your schema is bad. — Erik Philips, Dec 27 '18 at 01:31
@ErikPhilips I have two tables in database that i need to query where tableA is in the week of tableB, that why i am trying to use DATEPART wk, i can't add a stored procedure to the database, is there any other way i can so thanks? tnk — JG3, Dec 27 '18 at 14:13
@ErikPhilips tnx for your reply, another reason why i need it to be in code not in DB is because i need to construct the filter at run time based on the user selection, so there is a UI that the user can apply any filter they want and i am adding Where clauses to an IQueryable - sorry for not explaining in the beginning — JG3, Dec 27 '18 at 15:17
@Dai I created a UDF that takes a date and return like this: RETURN DATEPART(week, @date), the performance desecrated dramatically (from 2 seconds to 25), am i doing something wrong with the way the UDF is defined?, i dont have much experience with scalar function performance, but a basic google search shows that it could hurt performance, any help much appropriate — JG3, Dec 27 '18 at 16:57
@JoelG Can you post some example queries? I suspect you're running into issues with SARGability. — Dai, Dec 27 '18 at 17:34
Does this answer your question? [SqlFunctions.DatePart equivalent in EF Core](https://stackoverflow.com/questions/52529454/sqlfunctions-datepart-equivalent-in-ef-core) — adamjford, Dec 05 '21 at 21:10

score 1 · Accepted Answer · answered Jun 07 '19 at 08:50

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

EF Core Implement sql server DATEPART as DbFunction static method

1 Answers1