Creating entity with DateTime in primary key

Question

Suppose we have "EfTest" database with the following table in SQL server:

create table Test
(
    Id INT NOT NULL IDENTITY(1,1),
    TS DATETIME NOT NULL
    PRIMARY KEY (TS, Id)
)

And we want to add stuff there with EF (EF 5 in my case). We create edmx file from database in our sample project and write following code:

    static void Main(string[] args)
    {
        var ctx = new EFTestEntities();

        var t = new Test();
        t.TS = DateTime.Now;

        ctx.Tests.Add(t);

        ctx.SaveChanges();
    }

We run it and it fails with DbUpdateConcurrencyException on ctx.SaveChanges() line, saying:

Store update, insert, or delete statement affected an unexpected number of rows (0). Entities may have been modified or deleted since entities were loaded. Refresh ObjectStateManager entries.

Obviously, no concurrency happening there. SQL Server profiler reports that following query is run by EF:

exec sp_executesql N'insert [dbo].[Test]([TS])
values (@0)
select [Id]
from [dbo].[Test]
where @@ROWCOUNT > 0 and [Id] = scope_identity() and [TS] = @0',N'@0 datetime2(7)',@0='2015-08-27 18:29:45.1025442'

Problem lies here: @0 datetime2(7),@0='2015-08-27 18:29:45.1025442'. DATETIME2 is higher precision than DATETIME and TS check in the WHERE clause prevents it from finding the row it just inserted.

Question: putting aside the reason why I have DATETIME column as part of PK, how do I make EF to insert rows without throwing exceptions?

~~I could make TS column to be of DATETIME2 type, which would resolve the issue, but I don't want to waste DB space just because of some EF issue.~~ I double checked and it seems both DATETIME and DATETIME2 occupy 8 bytes, so I guess I go that way.

I could edit .edmx file in text editor and make it think that primary key is Id column only (so EF wouldn't use TS to lookup Id after insert), but VS will overwrite my edits next time I update entities from DB.

Any other options?

Maybe there are better solutions but you can use Stored proc to insert into db as a last resort. — Sameer, Aug 28 '15 at 08:40
you need to specify in your model that your using a composite primary key, otherwise EF will never work see this post http://stackoverflow.com/questions/14873169/creating-composite-key-entity-framework — Thomas, Dec 15 '15 at 19:59
I use database first approach, so EF understands very well that I have composite primary key. — n0rd, Dec 15 '15 at 20:24

score 1 · Accepted Answer · answered Dec 15 '15 at 20:26

1

I ended up using DATETIME2 as column type as there is no space trade off: both DATETIME and DATETIME2 occupy 8 bytes.

answered Dec 15 '15 at 20:26

n0rd

11,850
5
35
56

Creating entity with DateTime in primary key

1 Answers1