I wanted a very simple logging table. I wanted it to be time ordered and I decided to include a smalldatetime column in the primary key.
This failed badly with Entity Framework. Inserts resulted in a failure message indicating that zero rows were affected. This because a .Net DateTime value is not guaranteed to round trip into SqlServer and back. The SqlServer datetime (and smalldatetime) column types cannot store values with the same precision as the .Net DateTime type.
EF uses a query like this [1] to store the row and recover the freshly inserted identity column. The DateTime value is ‘rounded’ to suit SQL Server as it is inserted and the Select fails to read the freshly inserted row back because the datetime values are no longer equal.
I solved this by removing the datetime column from the primary key. It was not crucial for it to be in the primary key.
Moral – do not mix datetime and identity columns in your primary key.
[1] Entity Framework Generated Query
exec sp_executesql N'insert [dbo].[NewGroupPostalCodes]([PostalCode],
[Timestamp], [EmailAddress], [Comment])
values (@0, @1, @2, @3)
select [Id]
from [dbo].[NewGroupPostalCodes]
where @@ROWCOUNT > 0 and [PostalCode] = @0 and [Timestamp] = @1
and [Id] = scope_identity()',N'@0 nvarchar(8),@1 datetime,@2 nvarchar(7),@3
nvarchar(1)',@0=N'cv23 9ee',@1=''2011-05-16 10:49:36:043'',
@2=N'a@b.com',@3=N'w'