-1

I'm using Entity Framework Core and trying to insert an entity (with a timestamp) into the database, but an exception is thrown

Cannot insert an explicit value into a timestamp column...

I got the generated SQL from EF when insert, and tried to run manually that code, but it does not work.

exec sp_executesql N'SET NOCOUNT ON;
                     INSERT INTO [PersonalLookup] ([Description], [IsDeleted], [LookupTypeId], [ProfileId], [Timestamp])
                     VALUES (@p0, @p1, @p2, @p3, @p4);',
           N'@p0 nvarchar(4000),@p1 bit,@p2 int,@p3 int,@p4 varbinary(8000)',
           @p0=N'test',@p1=0,@p2=1,@p3=1,@p4=NULL

When I run the SQL code below, system returned with same error about the timestamp. Is that the EF problem? Because I check the fluent included the IsRowVersion and the timestamp is non-nullable in the database.

Timestamp column in database

Fluent mapping config

Call EF to save the entity

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Fred Pham
  • 11
  • 3
  • Post the table definition please – Caius Jard Sep 22 '19 at 04:58
  • I added the timestamp field in database, I used type timestamp. I tried to manually add a record, and it worked, but cannot with EF generated code – Fred Pham Sep 22 '19 at 05:22
  • And also post your fluent mapping code, and the local class entity definition that you're using – Caius Jard Sep 22 '19 at 05:23
  • Don't use TIMESTAMP, use ROWVERSION if you're versioning rows. Show your table definition and client side code. – Caius Jard Sep 22 '19 at 05:25
  • I also added the code in mapping file and when I call EF to add new entity to db, SQL Server does not have RowVersion type, it is changed to Timestamp. – Fred Pham Sep 22 '19 at 05:27
  • Quote msdn for sqls 2017 *The timestamp syntax is deprecated. This feature is in maintenance mode and may be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature* - is your sql server old? – Caius Jard Sep 22 '19 at 08:10
  • Are you doing this because you want row versioning or do you want to store a date/time? Can you also post the code of your entity class? You posted a mapping fragment of just id and timestamp and your personallookup table appears to have more fields than this, and no column called ID so it's a bit of an unclear question st the moment – Caius Jard Sep 22 '19 at 08:14
  • Also dont post code as images. – Jota.Toledo Sep 22 '19 at 08:23
  • It was my fault when I did not show all the context. I implement the class from base class, which contains the timestamp field. And I just missed the call to base class mapping when I config the mapping of child class. Now it worked correctly! Thanks for your sponsor Caius – Fred Pham Sep 22 '19 at 12:30

1 Answers1

1

I believe you've hit upon the problem of making the (reasonable) assumption that the TIMESTAMP data type in SQL Server is for recording dates and times like it in in other databases such as Oracle or Postgres - it isn't; it's intended for row versioning purposes and isn't related to dates or times at all. You could argue it was poorly named originally, and ms would agree- it's now a synonym for rowversion and timestamp will be dropped

Use the DATETIME type for your column instead if you only need a low precision date/time record, or a DATETIME2(7) if you need a higher precision record

DateTime2 vs DateTime in SQL Server

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Caius Jard
  • 72,509
  • 5
  • 49
  • 80
  • *Because I check the fluent included the IsRowVersion* let me guess the OP wants to have a timestamp column for row versioning purpose. But we do not know exaclty without seeing that "fluent" code – Sir Rufo Sep 22 '19 at 05:12
  • Good catch; let's see what client side code and table defs he posts up - I still think it might be a case of the tail wagging the dog – Caius Jard Sep 22 '19 at 05:27