0

My system is database first, EF 4.3.1.

I have a table in which the PK is composed of two int columns and a datetime. The table is a 'comment' table, to keep comments..

CREATE TABLE [dbo].[ArticleComments](
    [ArticleId] [int] NOT NULL,
    [UserId] [int] NOT NULL,
    [MessageText] [nvarchar](max) NOT NULL,
    [CreatedAt] [datetime] NOT NULL,
    [ExternalData] [bigint] NULL,
    CONSTRAINT [PK_ArticleComments] PRIMARY KEY CLUSTERED 
    (
       [ArticleId] ASC,
       [UserId] ASC,
       [CreatedAt] ASC
    )
)

In my logic, when a comment is created, I send the ArticleComment object to another thread, where another context is opened to do additional actions on the object, in which eventually the object may be saved.

The problem is, when the object is later saved, it sometimes throws an exception that nothing was saved.

I opened the profiler to see what SQL is executed on SaveChanges()

exec sp_executesql N'update [dbo].[ArticleComments]
set [ExternalData] = @0
where ((([ArticleId] = @1) and ([UserId] = @2)) and ([CreatedAt] = @3))
',N'@0 bigint,@1 int,@2 int,@3 datetime2(7)',@0=3243423,@1=6931,@2=2,@3='2012-10-11 11:23:25.4734801'

And found out that the problem is that EF was mapping it to a datetime2(7) in stand of regular datetime, which is why this update will not change anything and the exception is thrown.

I checked the EDMX file, over there it says the type is datetime with precision 3.

FYI: in addition, datetime is rounded in SQL Server

SO, i found this article: Round .NET DateTime milliseconds, so it can fit SQL Server milliseconds Which shows a nice way to round .net time to SQL time. And I think there is a class for this in the framework Microsoft® System CLR Types for Microsoft® SQL Server® 2012 at System.Data.SqlTypes called SqlDateTime that will do the same.

So my code looks like this (using the extension from the SO answer just linked):

 hack the time -->  message.CreatedAt = message.CreatedAt.RoundToSqlServerDateTime();
                    db.ArticleComments.Attach(message);
                    message.ExternalData = someNumber;
                    db.SaveChanges();

And it works.

But all of these are hacks, not solutions. Why is EF failing here, what am I doing incorrectly?

Community
  • 1
  • 1
Jonathan Levison
  • 2,617
  • 1
  • 18
  • 22
  • SqlTypes is not an assembly from the .NET Framework. It's a type shipped with SqlServer. It can be installed stand alone take a look at this thread http://stackoverflow.com/questions/10117008/net-4-5-beta-dbgeography-notimplementedexception - there is a link to the download. I am mentioning this because when you move your app to a clean machine without SqlServer it won't work. – Pawel Oct 11 '12 at 16:21
  • Thanks for correcting me, will update the question. I have the CLR types installed already (got SQL Server 2012 as well) but still, the problem is that in my case, EF is mapped to the wrong data types in the database.. still hoping someone has encountered this before. – Jonathan Levison Oct 14 '12 at 06:50

0 Answers0