How can I make Entity Framework's generated INSERT statement to include a database generated IDENTITY column?

Question

This is Entity Framework 6.1.3

The SQL Server table has a two-column composite key.

ID      INT Identity(1,1) NOT NULL
VERSION INT               NOT NULL

Inserting a new record works because I don't set the ID on my object; I only set the VERSION. So a new record would look like this:

ID    VERSION
1     1

Perfect! The database generates the ID because the column is configured with Identity and my model is decorated with [DatabaseGenerated(DatabaseGeneratedOption.Identity)].

But now I need to insert another row with the same ID but a different VERSION; hence the composite key. So I would expect the second row to be:

ID   Version   
1    1
1    2       <- second row has same ID and different version

I do need this to work both ways because there is the scenario where a new ID should be auto-generated by the database, and the other scenario where I have the same ID but a different VERSION.

The Problem: Because my Code-First model has the ID configured with DatabaseGeneratedOption.Identity, when I set the ID property on my object, my SaveChanges generates the insert statement without the ID!

(Diagnostic Tools in VS shows that Entity Framework generated this statement)

ADO.NET: Execute Reader "INSERT [dbo].[T1]([Version], ... VALUES (@0, ...)

Note the omission of ID. Because I explicitly set the ID on my object, I expected to see this statement instead.

INSERT [dbo].[T1]([ID], [Version], ... VALUES (@0, @1, ...)

That's what I'm trying to accomplish.

The question is: How can I make Entity Framework include that ID column in its generated insert statement in an elegant way?

I don't want to use a stored procedure or hard code a SQL statement or hack the insert statement by 'squeezing in' the column.

If there is no way, I know that I would have to remove the use of Identity altogether and define my own IDs, which I'm trying to avoid.

Also, my SaveChanges() already makes use of SET IDENTITY_INSERT ON/OFF so that isn't any problem.

Here is the relevant part of my model: (I omitted other properties)

[Key, Column(Order = 0)]
[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
public int ID { get; set; }

[Key, Column(Order = 1)]
public int VERSION { get; set; }

One avenue that I've explored was to reset my DbContext with a twist in OnModelCreating, but that didn't make a difference. Of course, in that revision I did remove the DatabaseGenerated decorator off my ID property in the class. I inserted this into OnModelCreating:

if (this.AllowIdentityInsert)
{
    modelBuilder.Entity<T1>().Property(x => x.ID).HasDatabaseGeneratedOption(DatabaseGeneratedOption.None);
}
else
{
    modelBuilder.Entity<T1>().Property(x => x.ID).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
}

If I could successfully control the model by changing the ID property to DatabaseGeneratedOption to None before my SaveChanges, then this could work and be an elegant solution.

Has anyone run into this situation and found a good solution? Thanks so much for your input or suggestions.

Answer 1

Generally you don't want to use an identity column in that manner but I suppose if you are using a composite key you could. The problem that you will be faced with to insert your second record is that you will have to turn IDENTITY_INSERT on and off. So thinking of the SQL of it here is an example to show you what has to be done to accomplish the task.

IF OBJECT_ID('tempdb..#TblName') IS NOT NULL
    BEGIN
        DROP TABLE #TblName
    END

CREATE TABLE #TblName (
    ID INT IDENTITY(1,1) NOT NULL, Version INT NOT NULL
)

INSERT INTO #TblName (Version) VALUES (1)

SET IDENTITY_INSERT #TblName ON

INSERT INTO #TblName (ID, Version) VALUES (1,2)

SET IDENTITY_INSERT #TblName OFF

SELECT *
FROM
    #TblName

A more typical design is to actually maintain a log table via a trigger and store the history in it. Because in that table it wouldn't need the identity column simply another INT.

There are a few more 2 table designs to get around the limitation but you may also want to look into creating SQL SEQUENCE https://msdn.microsoft.com/en-us/library/ff878058.aspx and instead of using IDENTITY on the ID column retrieving a SEQUENCE when you need it and always inserting the value. If you use a SEQUENCE you get the added benefit of being able to add another IDENTITY column that will be a local table ID which is typically recommended rather than relying solely on the composite key.

Okay here is (to me) a very interesting way of doing getting around your IDENTITY issue and maintaining a "incremented version". You can use an Update able View instead of directly using your table. You would use 2 SEQUENCES one for ID and one for VersionId and then to get Version you would use ROW_NUMBER() in the view. You could expand this solution by adding INSTEAD OF INSERT/UPDATE trigger to handle setting of the IDS more automatically but I don't generally like triggers. Anyway, here is to me an interesting solution:

CREATE TABLE dbo.yourTable (
    TableId INT NOT NULL IDENTITY(1,1)
    ,Id INT NOT NULL
    ,VersionId INT NOT NULL
    ,Col VARCHAR(100) NOT NULL
    ,PRIMARY KEY (Id, VersionId)
)

GO

CREATE SEQUENCE dbo.SEQ_yourTableIdBy1
    START WITH 1
    INCREMENT BY 1;
GO

CREATE SEQUENCE dbo.SEQ_yourTableVersionIdBy1
    START WITH 1
    INCREMENT BY 1;
GO

CREATE VIEW dbo.yourTable_v
AS
    SELECT
       Id
       ,VersionId
       ,Version = ROW_NUMBER() OVER (PARTITION BY Id ORDER BY VersionId)
       ,Col
       ,LatestVersion = CASE
                  WHEN ROW_NUMBER() OVER (PARTITION BY Id ORDER BY VersionId DESC) = 1
                  THEN 1 ELSE 0 END
    FROM
       dbo.yourTable
GO

--New Record
INSERT INTO dbo.yourTable_v(Id, VersionId, Col)
VALUES (NEXT VALUE FOR dbo.SEQ_yourTableIdBy1, NEXT VALUE FOR dbo.SEQ_yourTableVersionIdBy1, 'A')

SELECT * FROM dbo.yourTable_v

--Change To Existing Record
INSERT INTO dbo.yourTable_v(Id, VersionId, Col)
VALUES (1, NEXT VALUE FOR dbo.SEQ_yourTableVersionIdBy1, 'B')

SELECT * FROM dbo.yourTable_v

link showing how it works http://rextester.com/GBHG23338

To Make Entity Framework believe the view is a table you may need to change the Key definition and the Entity Type here is a msdn blog on the subject. https://blogs.msdn.microsoft.com/alexj/2009/09/01/tip-34-how-to-work-with-updatable-views/

Benefits:

• this isn't going to break if 2 people try to submit simultaneously etc.
  • Entity Framework will think this is a normal table once you fake it out slightly by following the link.
  • While it will have VersionId incremented across all records it will still present a nice Version + 1 for use in your application.
  • And you can easily add a latest version column for use in your application/queries.