DateTime.AddX failure

[los93sol]

Describe what is not working as expected.

Using EFCore to timeshift timestamps does not work as expected.

If you are seeing an exception, include the full exceptions details (message and stack trace).

Exception message:
System.Data.SqlClient.SqlException (0x80131904): Conversion failed when converting date and/or time from character string.

Steps to reproduce

            var slotSize = 2.0;
            var test = context.Records.Select(record => new
            {
                Timestamp = record.Timestamp,
                Test = new DateTime(1900, 1, 1)
                .AddMinutes(
                    Math.Round(
                        SqlServerDbFunctionsExtensions.DateDiffMinute(
                            null,
                            new DateTime(1900, 1, 1),
                            record.Timestamp) / slotSize,
                        0)
                    * slotSize)
            })
            .ToList();

The above generates SQL like this... SELECT [record].[Timestamp], DATEADD(minute, ROUND(DATEDIFF(MINUTE, '1900-01-01T00:00:00.0000000', [record].[Timestamp]) / @__slotSize_0, 0) * @__slotSize_0, '1900-01-01T00:00:00.0000000') AS [Test] FROM [Records] AS [record]

The issue is specifically in the DATEADD where it puts the second argument as '1900-01-01T00:00:00.0000000'.
If I manually change that to be '1900-01-01T00:00:00.000' the query executes fine.
Also, using DateTime.Now works as expected because the generated SQL contains GETDATE(). Additionally, referencing from another field also works fine, but it seems to be impossible to have a defined date like this which I need to do in order to time shift my actual dates to group them efficiently to the nearest slot size for dashboarding purposes.

I've also tried changing the DateTimeKind and can get it to render different output, but it's still invalid. I am totally blocked on my project due to this bug. Is there a workaround I can use or am I just doing something wrong?

To further complicate this, the above is a repro, in my actual code I'm generating this whole thing from an expression tree.

Further technical details

EF Core version: 2.2 Database Provider: Microsoft.EntityFrameworkCore.SqlServer

[ajcvickers]

Note for triage: still repros on 3.0.

Unhandled exception. Microsoft.Data.SqlClient.SqlException (0x80131904): Conversion failed when converting date and/or time from characte
r string.
   at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)

   at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean
 asyncClose)
   at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleR
esultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at Microsoft.Data.SqlClient.SqlDataReader.TryHasMoreRows(Boolean& moreRows)
   at Microsoft.Data.SqlClient.SqlDataReader.TryReadInternal(Boolean setTimeout, Boolean& more)
   at Microsoft.Data.SqlClient.SqlDataReader.Read()
   at Microsoft.EntityFrameworkCore.Storage.RelationalDataReader.Read()
   at Microsoft.EntityFrameworkCore.Query.RelationalShapedQueryCompilingExpressionVisitor.QueryingEnumerable`1.Enumerator.MoveNext()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at Program.Main() in C:\Stuff\ThreeOhP7P\ThreeOhP7P\Program.cs:line 253
   at Program.<Main>()
ClientConnectionId:1dfc6ee4-eadb-4adf-9fdb-bf23e43125f1
Error Number:241,State:1,Class:16

public class Record
{
    public int Id { get; set; }
    public DateTime Timestamp { get; set; }
}

public class BloggingContext : DbContext
{
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder.UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=Test;ConnectRetryCount=0");

    public DbSet<Record> Records { get; set; }
}

public class Program
{
    public static async Task Main()
    {
        using (var context = new BloggingContext())
        {
            context.Database.EnsureDeleted();
            context.Database.EnsureCreated();

            context.Add(new Record { Timestamp = new DateTime(1973, 9, 3)});

            context.SaveChanges();
        }

        using (var context = new BloggingContext())
        {
            var slotSize = 2.0;
            var test = context.Records.Select(record => new
                {
                    Timestamp = record.Timestamp,
                    Test = new DateTime(1900, 1, 1)
                        .AddMinutes(
                            Math.Round(
                                SqlServerDbFunctionsExtensions.DateDiffMinute(
                                    null,
                                    new DateTime(1900, 1, 1),
                                    record.Timestamp) / slotSize,
                                0)
                            * slotSize)
                })
                .ToList();        }

    }
}

[smitpatel]

Weird SqlServer issue date An expression that can resolve to one of the following values: date datetime datetimeoffset datetime2 smalldatetime time For date, DATEADD will accept a column expression, expression, string literal, or user-defined variable. A string literal value must resolve to a datetime. Use four-digit years to avoid ambiguity issues. See Configure the two digit year cutoff Server Configuration Option for information about two-digit years.

[los93sol]

Sounds like this might be a bit before it gets addressed so I'm attempting to work around the issue this morning. My initial thought was to use FromSql or Query, but I'm hitting walls there too because I'm projecting into a type that isn't mapped in the database. Any suggestions to work around this bug would be greatly appreciated

[smitpatel]

Introduce local variable like this var date = new DateTime(1900, 1, 1) and use the variable in the query. We will generate parameter rather than inline constant in SQL and it won't hit the issue.

[los93sol]

Thanks, that works for me when manually writing the linq, just need to sort out how to do that when dynamically building the expression tree and I'll be good to go.

[kp2017-zz]

Could the fix be to modify the SqlServerDateTimeTypeMapping.SqlLiteralFormatString to always return a convert in front of the datetime2 literal? This seems to break the literal definition but I see a similar solutions was used in DateTimeTypeMapping.

I have tried to create a branch off 3.1 but I get a 403 error. How do I create a branch for pull requests?

[smitpatel]

To fix the issue, DateTimeMethod translator should recognize that date is a constant value and generate a Sql Constant with datetime typeMapping rather than datetime2.

[kp2017-zz]

I see what your mean now SmitPatel. The DateAdd Method only accepts literals that are datetime. Sorry, I missed that in the original description.