DatePart function in Linq .NET 5 using variable interval

Question

For EF Core 5, SqlFunctionExpression construction should be used to use the DatePart function like following:

public int? DatePart(string datePartArg, DateTime? date) => throw new InvalidOperationException($"{nameof(DatePart)} cannot be called client side.");

protected override void OnModelCreating(ModelBuilder builder)
        {
            var methodInfo = typeof(DbFunctionExtensions).GetMethod(nameof(DatePart));

            var datePartMethodInfo = typeof(NotificationDbContext) // Your DB Context
                .GetRuntimeMethod(nameof(NotificationDbContext.DatePart), new[] { typeof(string), typeof(DateTime) });
            builder.HasDbFunction(datePartMethodInfo)
               .HasTranslation(args =>
                        new SqlFunctionExpression("DATEPART",
                            new[]
                            {
                            new SqlFragmentExpression((args.ToArray()[0] as SqlConstantExpression).Value.ToString()),
                            args.ToArray()[1]
                            },
                            true,
                            new[] { false, false },
                            typeof(int?),
                            null
                        )
                    );
        }

Afterwards we can call it like this

_context.DatePart("day", x.Date);

But how can we pass variable instead of static "days" like below

string daysInterval="day";
_context.DatePart(daysInterval, x.Date);

daysInterval will be initialized by a function returning year,month or day based on a condition. So how can we generically pass daysInterval instead of hard coding it?.

Answer 1

I don't think this is possible, as the first argument to DATEPART can't be parameterized. EG the SQL looks like:

select datepart(day,getdate())

not

select datepart('day',getdate())

and this query

_context.DatePart(daysInterval, x.Date);

will be translated with a parameter for daysInterval. AFAIK to get this to would you would have to either transform the query expression and dynamically replace the variable expression with a literal expression, which is way too much work.

Or provide your own TSQL Scalar function that takes a varchar for the first argument. Like

create function datepart2(@interval varchar(20), @d datetime2)
returns int
as
begin
  if @interval = 'day'
    return datepart(day,@d)
  else if @interval = 'second'
    return datepart(second,@d)
  --. . .

  return -1
end

And register it like this:

modelBuilder.HasDbFunction(datePartMethodInfo)
   .HasTranslation(args =>
            new SqlFunctionExpression("dbo.DATEPART2",
                new[]
                {
                    args.ToArray()[0],
                    args.ToArray()[1]
                },
                true,
                new[] { false, false },
                typeof(int?),
                null
            )
        );