Entity Framework 7 fails to insert datetime

Question

I'm using EntityFramework 7, beta7 and have the following entity:

C#

public class Log
{
    [Column(TypeName ="datetime")]
    public DateTime Date { get; set; }
    ...
}

SQL

CREATE TABLE [dbo].[Logs] (
    [Date] [datetime] NOT NULL,
    ...
)

And I execute the following:

db.Logs.Add(new Log { Date = DateTime.UtcNow });
db.SaveChanges();

This is successful on Windows, but fails under mono on debian. Same SQL server/database. The SQL generated is below. Note the difference of the type and value rendered for @p1:

Windows

exec sp_executesql N'SET NOCOUNT OFF;
INSERT INTO [Logs] ([Browser], [Date], [Exception], [HostAddress], [Level], [Logger], [Message], [Thread], [Url], [Username])
OUTPUT INSERTED.[Id]
VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9);
',N'@p0 nvarchar(max) ,@p1 datetime2(7),@p2 nvarchar(max) ,@p3 nvarchar(max) ,@p4 nvarchar(4000),@p5 nvarchar(4000),@p6 nvarchar(4000),@p7 nvarchar(4000),@p8 nvarchar(max) ,@p9 nvarchar(max) ',@p0=NULL,@p1='2015-09-28 23:02:26.0367851',@p2=NULL,@p3=NULL,@p4=N'INFO',@p5=N'Fanatics.ConsoleApp.Program',@p6=N'Console app test',@p7=N'0',@p8=NULL,@p9=NULL
go

Linux

exec sp_executesql N'SET NOCOUNT OFF;
INSERT INTO [Logs] ([Browser], [Date], [Exception], [HostAddress], [Level], [Logger], [Message], [Thread], [Url], [Username])
OUTPUT INSERTED.[Id]
VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9);
',N'@p0 nvarchar(4000), @p1 char(27), @p2 nvarchar(4000), @p3 nvarchar(4000), @p4 nvarchar(4000), @p5 nvarchar(4000), @p6 nvarchar(4000), @p7 nvarchar(4000), @p8 nvarchar(4000), @p9 nvarchar(4000)',@p0=NULL,@p1='2015-09-28T23:03:21.5561720',@p2=NULL,@p3=NULL,@p4=N'INFO',@p5=N'Fanatics.ConsoleApp.Program',@p6=N'Console app test',@p7=N'0',@p8=NULL,@p9=NULL
go

The failure on Linux is:

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

Questions

1. Why does Windows generate a datetime2 when the type is explicitly set to datetime?
2. The linux version is generating invalid SQL, hence the failure. How can I insert datetime values on mono/linux?

Answer 1

1. .NET DateTime type has wider range than sql server's "datetime" type. Actually, .NET DateTime has the same range as sql server's "datetime2" type, that is why Entity Framework will use datetime2 (if possible) everywhere when converting DateTime to sql date (like in your example). Type of table column does not matter in this case. You can read this design meeting notes where EF team discusses problem with datetime and datetime2 and decides to leave this as is (and the reasons behind that).

2. Mono uses TDS to work with sql server (based on FreeTDS, and it uses quite outdated version of it. This version has no idea about sql server's "datetime2" type, so instead of upgrading to new version of TDS (maybe that is too much work for now), kind of hack was implemented, converting datetime2 from\to string. Now, datetime2 has higher precision than datetime, so when converting datetime2 to string and then converting that string to datetime (which is done implicitly) causes the error you see. That is easy to check:

   select cast('2015-09-28T23:03:21.5561720' as datetime2) -- < all fine
   select cast('2015-09-28T23:03:21.5561720' as datetime) -- < error from your question
   

   You can read a bit more about it here.

You might ask if you can use EF with sql server on mono. Well, you may workaround by setting ProviderManifestToken to 2005 in your EF model. This will make EF thing it works with sql server 2005, and it will not use datetime2 everywhere. But you will lose other types which were added after sql server 2005 obviously, not to mention that is dirty hack.

As a side note - better don't do any serious development on mono with sql server. Sql server provider in mono is full of bugs, and bug above is one of the most innocent. It has problems with null values, problems with connection pool (this one is very serious - if you timeout on connection pool connection - this connection will be broken for the whole lifetime and ALL queries on that connection will fail. Hack - remove connection from connection pool if your query timed out on it), etc. Most of this bugs are known for years and not fixed still. If you have choice - just use postgresql, it's free, good, and I had no problems with it on mono.