Disabling identity (auto-incrementing) on integer

问题:

I am using code first approach in a ASP.NET MVC 3 application and all integer primary keys in models (public int Id { get; set; }) are by default configured as an identity with auto-incrementing. How to disable this and enable a way to manually enter the integer for the primary key?

The actual situation is that the Id integers have a special meaning and I would therefore like to have them choosable at creation and later editable. It would be ideal if in case the integer is not given at creation time it is auto-incremented, else the specified value is used. But editable primary fields is my primary need. Is there any way to do this elegantly in ASP.NET MVC 3?

回答1:

Use these data annotation options:

• [System.ComponentModel.DataAnnotations.KeyAttribute()]
• [System.ComponentModel.DataAnnotations.DatabaseGenerated(System.ComponentModel.DataAnnotations.DatabaseGeneratedOption.None)]

回答2:

You can use FluentMapping:

modelBuilder.Entity<*entityname*>().Property(m => m.*fieldname*)
             .HasDatabaseGeneratedOption(DatabaseGeneratedOption.None);

回答3:

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.

回答4:

If this is an identity field in the database you cant. Well you can by setting identity insert off on the database side but by good practice you shouldn't unless bulk inserting records. If you don't want this as an identity set the identity option to false in the DB for that column.

回答5:

use attribute :

public class MessageSubject
    {
        [DatabaseGenerated(DatabaseGeneratedOption.None)]
        public int Id { get; set; }
        public string Title { get; set; }
        public string Comment { get; set; }
        public bool BuildIn { get; set; }
    }

回答6:

If you want to use fluent api with EntityFramework Core 2.0 you write:

modelBuilder.Entity<*myEntity*>()
  .Property(e => e.*myFieldname*)
  .ValueGeneratedNever();