I have just installed the latest accepted EntityFramework.dll version 5.0.0, I believe ...
however, I get confused half the time as there is a Runtime Version of
v4.0.30319 and a Version of 4.4.0.0 but, I am sure from the website I
got referred to in my searching (which told me to install via "Package
Mangager Console" from which you get to via menu within VS
"Tools|Library Package Manager|Package Manager Console" and typing at
the "PM>" prompt "Install-Package EntityFramework [optional: version
number or -Pre for the lastest pre-release (beta version)]") it was
5.0.0.
... , and there is an attribute "System.CompnentModel.DataAnnotations.DatabaseGenerated(Computed, Identity or None) (previous version) or [...].Schema.DatabaseGenerated (latest version)" which you could use. So either use this attribute or use fluent mapping idea as mentioned above (by William Haack (edited by Remo Gloor)) and if not code first (i.e. changing production) then as also mentioned above (by Adam Tuliper) you will need to write and execute a one off script to turn off identity insert. On top of that if you do not supply an ID you can simulate an identity insert by retriving the MAX(ID) + 1 on your table in code (and keep in mind concurrency issues on a multi-user environment) or in a trigger. Alternatively if you wanted to plug holes, as the expression goes, you could probably do this in a trigger as well by intercepting the inserted row and checking if the ID column is set, if so proceed with the insert otherwise,
set the value first. One approach to plug holes is to use that trick (which I saw on some web-site that I cannot remember, so I am guessing a little here) where you effectively do an anti-inner join from some large-table with a just a single column of rownumbers to your table to find the first available non used identity number (i.e. find the first rownumber that is not a member of the target table).
In SQL Server 2005 and beyond:
CREATE TRIGGER updInsYourTable_PlugHolesOnIDIfNull
ON YourTable
FOR update, insert AS
BEGIN
DECLARE @ID INT
SELECT @ID = ID FROM INSERTED
IF @ID IS NULL
BEGIN
;WITH CTE_StagedNumbers AS
(
SELECT ROW_NUMBER() OVER (ORDER BY o.object_id) AS NextFreeIdentity
FROM ( SELECT object_id FROM sys.objects
-- UNION ALL
-- SELECT object_id FROM sys.objects
/* NB: Here if sys.objects is not larger enough say on a small schema
configured database then use a different table otherwise you can
always union all on the same table as many times as you want to
double, triple etc. its size. */
) o
)
UPDATE YourTable
SET ID = (
SELECT TOP 1 NextFreeIdentity
FROM CTE_StagedNumbers
WHERE NextFreeIdentity NOT IN (SELECT ID FROM YourTable)
)
WHERE ID IS NULL
END
END
GO
Note, the the CTE_StagedNumbers is not necessary it is just there for emphasis and the main trick is not necessarily in the setting up of the row number but, if you were to set up a permanent staging table (say StagedNumbers) with just a single integer column with no auto-identity (say NextFreeIdentity INT NOT NULL PRIMARY KEY), (Aside NB: YourTable definition for your ID column must accept null's as we are using an after trigger) pre-filled with consequtive positive-integer values starting at 1 using the above technique, then remove the CTE altogether and replace the CTE_StagedNumbers in the final select with StagedNumbers then this will be most efficient.
