Seeding DateTime in EF 2.1 throws exception

[virshu]

Describe what is not working as expected.
I am using new way of seeding data in Entity Framework 2.1:

For example,
modelBuilder.Entity<Blog>().HasData(new Blog {BlogId = 1, CreateDate = DateTime.Now});
However, applying this to the database throws an exception. I can see that in the generated SQL the value comes as '2018-06-01T13:22:13.248-07:00' which is wrong. And since the field is obviously DateTime, I can't format it into the string the way I would want to.

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

Exception message:

Failed executing DbCommand (1,154ms) [Parameters=[], CommandType='Text', CommandTimeout='320']
IF EXISTS (SELECT * FROM [sys].[identity_columns] WHERE [name] IN (N'group_id', N'application_id', N'create_date', N'group_admin', N'group_description', N'group_module_delete', N'group_module_edit', N'group_module_insert', N'group_module_read', N'group_name', N'update_date') AND [object_id] = OBJECT_ID(N'[User_Groups]'))
    SET IDENTITY_INSERT [User_Groups] ON;
INSERT INTO [User_Groups] ([group_id], [application_id], [create_date], [group_admin], [group_description], [group_module_delete], [group_module_edit], [group_module_insert], [group_module_read], [group_name], [update_date])
VALUES (1, NULL, '2018-06-01T21:10:37.126-07:00', 'Enabled', 'ADMIN', 'Enabled', 'Enabled', 'Enabled', 'Enabled', 'ADMIN', NULL);
IF EXISTS (SELECT * FROM [sys].[identity_columns] WHERE [name] IN (N'group_id', N'application_id', N'create_date', N'group_admin', N'group_description', N'group_module_delete', N'group_module_edit', N'group_module_insert', N'group_module_read', N'group_name', N'update_date') AND [object_id] = OBJECT_ID(N'[User_Groups]'))
    SET IDENTITY_INSERT [User_Groups] OFF;

Conversion failed when converting date and/or time from character string.

Stack trace:

System.Data.SqlClient.SqlException (0x80131904): Conversion failed when converting date and/or time from character string.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite, String methodName)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.Execute(IRelationalConnection connection, DbCommandMethod executeMethod, IReadOnlyDictionary`2 parameterValues)
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteNonQuery(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQuery(IEnumerable`1 migrationCommands, IRelationalConnection connection)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.Migrate(String targetMigration)
   at Microsoft.EntityFrameworkCore.Design.Internal.MigrationsOperations.UpdateDatabase(String targetMigration, String contextType)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)
ClientConnectionId:3cc3208c-d4eb-4495-8de9-b3aebd983442
Error Number:241,State:1,Class:16

Further technical details

EF Core version: (found in project.csproj or packages.config)
2.1.0

Database Provider: (e.g. Microsoft.EntityFrameworkCore.SqlServer)
Microsoft.EntityFrameworkCore.SqlServer
Operating system: Windows 10 x64
IDE: (e.g. Visual Studio 2017 15.4) none (dotnet CLI). VS Community 15.7.3 is installed but not used here.

[ajcvickers]

Note for triage: I was able to reproduce this when the column is mapped to datetime. Columns mapped to datetime2 work okay. Different format string is used for datetime and datetime2:

private const string DateTimeFormatConst = "{0:yyyy-MM-ddTHH:mm:ss.fffK}";
private const string DateTime2FormatConst = "{0:yyyy-MM-ddTHH:mm:ss.fffffffK}";

[andresdsep]

Interestingly, today I found that comparing a C# date with a database datetime directly would work:
dbContext.Entities.Where(x => x.Created == DateTime.Now)
Using Contains would not:
dbContext.Entities.Where(x => datesArray.Contains(x.Created))

[andresdsep]

I just pulled version 2.2.0-preview2-35157 from NuGet and found this issue to still be occurring.

As I said before though, it only seems to happen when using .Contains in the expresssion:
repo.Where(x => x.RollDate == today)
produces:
exec sp_executesql N'SELECT [x].[AllTheColumnsHere] FROM [table] AS [x] WHERE ([x].[RollDate] = @__today)',N'@__rollDate_Date_1 datetime2(7)',@__today='2018-09-13 00:00:00'
whereas
repo.Where(x => datesArray.Contains(x.RollDate))
produces
SELECT [x].[AllTheColumnsHere] FROM [table] AS [x] WHERE [x].[RollDate] IN ('2018-09-13T00:00:00.0000000')

Any chance I'm just doing something wrong and there is something I can fix on my end?

[ajcvickers]

@andresdsep This fix did not make it into the preview2 release. This is why the milestone is still set to 2.0.0, and not 2.0.0-preview2.

[matipaulo]

Hi @ajcvickers @smitpatel

We found a similar issue with EF 2.1, we are getting Conversion failed when converting date and/or time from character string. when we are trying to execute the following code:

await _context.Database.ExecuteSqlCommandAsync($"INTO SomeTable(SomeDateField) VALUES({DateTime.Now:yyyy-MM-dd}");

We could sovle it using the following approach:

await _context.Database.ExecuteSqlCommandAsync($"INTO SomeTable(SomeDateField) VALUES({DateTime.Now.ToString("yyyy-MM-dd")}");

Looks like there is a proble with the interpolations....Also looks lije EF have problems mapping fields defined as Date, thats we have to use a RawSql...

[smitpatel]

@andresdsep - I am not seeing exactly same SQL as you but in preview2 package the SQL generated is something like this,

      SELECT [e].[Id], [e].[TheDate]
      FROM [Blogs] AS [e]
      WHERE [e].[TheDate] IN ('2018-09-18T00:00:00.000-07:00')

-07:00 part is incorrect which is fixed here in 2.2.0 package.

@matipaulo - Please file a new issue with detailed repro steps.

[andresdsep]

@smitpatel - After a lengthy interlude working on other projects, I'm finally able to verify this issue.

I have run the same code as before with the new stable 2.2.0 package and found the same issue is occurring.

When using Contains I'm seeing [x].[RollDate] IN ('2018-12-05T00:00:00.0000000') outputted. When using a simple == I'm seeing ([x].[RollDate] = @__rollDate_Date_0) ... @__rollDate_Date_0='2018-12-05 00:00:00'

[smitpatel]

@andresdsep - Please file a new issue with detailed repro steps.

[andresdsep]

Created: #14095