Implement DatePart nanosecond

[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: https://github.com/aspnet/EntityFrameworkCore/issues/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).

[ralmsdeveloper]

@smitpatel,

Can I do this?

[sudoman281]

I can't create an instance of SqlFragmentExpression - the constructor is internal.

[cyptus]

@sudoman281 this has been fixed: https://github.com/aspnet/EntityFrameworkCore/pull/18208

[icesasoftcorp]

this code in EF Core 3.1 It doesn't work 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] })); }

Please can you help me error in SqlFunctionExpression

[smitpatel]

.HasTranslation(args => new SqlFunctionExpression(nameof(DatePart), typeof(int?), new[]
{
new SqlFragmentExpression(args.ToArray()[0].ToString()),
args.ToArray()[1]
}));
}
// Replace with
.HasTranslation(args => SqlFunctionExpression.Create(nameof(DatePart), new[]
{
new SqlFragmentExpression(((SqlConstantExpression)args[0]).Value.ToString()),
args[1]
}, typeof(int?)));
}

[icesasoftcorp]

thanks @smitpatel, the code stayed like this

var methodInfo = typeof(AppDbContext).GetRuntimeMethod(nameof(DatePart), new[] { typeof(string), typeof(DateTime) }); modelBuilder .HasDbFunction(methodInfo) .HasTranslation(args => SqlFunctionExpression.Create(nameof(DatePart), new[] { new SqlFragmentExpression(((SqlConstantExpression)args.ToArray()[0]).Value.ToString()), args.ToArray()[1] }, typeof(int?), null));

[ksalk]

Hi, I'd like to implement this feature and add new methods as part of EF.Functions. I'd like to add support for both micro- and nanoseconds.

[ajcvickers]

@maumar @roji @cincuranet Can one of you check that this is a straightforward translation that we still want to do?

[roji]

Makes sense to me - note that .NET 7 added Microsecond and Nanosecond properties to DateTime, so we don't need to introduce an EF.Functions method for this. Should be very straightforward.

[CRFricke]

I'll have a go at this. Seems we need some trivial updates to the EFCore.SqlServer member translator classes for DateTime, TimeOnly, and TimeSpan and a bunch of tests.

I assume it doesn't matter, but I'll mention it anyway. The .NET Millisecond, Microsecond, and Nanosecond properties return values between 0 - 999, whereas DATEPART returns the whole fractional time value (e.g. 0 - 999999999 for nanosecond).

[roji]

@CRFricke sure, feel free to submit a PR. The general idea is that if we translate a built-in .NET member (like DateTime.Millisecond), the translation to SQL should return the same results as the .NET call does (with regards to the DATEPART behavior).

[CRFricke]

@roji PR #34135 is awaiting review. Also, the Cosmos test runs are awaiting approval.