Translate DatePart.Microsecond and Nanosecond members

[KLuuKer]

We need access to SQL's DatePart nanosecond to do some in sql sorting (don't ask me why it's a stupid reason).

Would it be possible to have this in EF.Functions?

incase someone wants to lookup the command....
https://docs.microsoft.com/en-us/sql/t-sql/functions/datepart-transact-sql?view=sql-server-2017

[Kukkimonsuta]

I'd like to implement this. From what I could find, the following tables describe current possible translations:

DatePart	Currently translated from	Proposal
year	DateTime.Year
quarter
month	DateTime.Month
dayofyear	DateTime.DayOfYear
day	DateTime.Day
week
weekday
hour	DateTime.Hour
minute	DateTime.Minute
second	DateTime.Second
millisecond	DateTime.Millisecond
microsecond		EF.Functions.DatePartMicrosecond
nanosecond		EF.Functions.DatePartNanosecond
TZoffset
ISO_WEEK

DateDiff	Currently translated from	Proposal
year	EF.Functions.DateDiffYear
quarter
month	EF.Functions.DateDiffMonth
dayofyear
day	EF.Functions.DateDiffDay
week
hour	EF.Functions.DateDiffHour
minute	EF.Functions.DateDiffMinute
second	EF.Functions.DateDiffSecond
millisecond	EF.Functions.DateDiffMillisecond
microsecond	EF.Functions.DateDiffMicrosecond
nanosecond	EF.Functions.DateDiffNanosecond

I've included DateDiff to see how the implementation matrix compares for more exotic parts (quarter, week..).

1. Is adding microsecond and nanosecond good enough or do we want to fill other blanks?
2. Do we add only DatePartMicrosecond and DatePartNanosecond to EF.Functions or do we also add other variants that are already covered by DateTime.XXX?

[smitpatel]

@divega to advise on above list.

[cyptus]

is there any way to make use of datepart(week, ...) or ISO_WEEK right now?

[Kukkimonsuta]

@divega I'm still interested in putting together PR, but I'd like to request advice on the requested API (see above)

[cyptus]

I found an workaround. you can make use of the SQL datepart and datediff 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);
repository.Where(x => dbContext.DatePart("week", x.CreatedAt) > 1);
...

more info: #9549

watch out not to call the DbFunction method on an interface of DbContext. The call must happend directly on the DbContext instance (you can cast IDbContext to DbContext for the call).