[聚合问答] Entering keys manually with Entity Framework

c#,asp.net,sql,entity-framework 2017-11-30 12 阅读

I'm trying to use Entity Framework code first for a simple database project and I run into a problem I simply cannot figure out.

I noticed EF was setting the ID for my tables automatically increasing by 1 each time, completely ignoring the value I entered manually for that field. After some searching it is my understanding that the right way to disable this behavior is doing:

modelBuilder.Entity<Event>().Property(e => e.EventID).HasDatabaseGeneratedOption(DatabaseGeneratedOption.None);

However now I'm just getting this error and I have no idea why:

Unhandled Exception: System.Data.Entity.Infrastructure.DbUpdateException: An error occurred while updating the entries. See the inner exception for details. ---

System.Data.UpdateException: An error occurred while updating the entries. See the inner exception for details. ---> System.Data.SqlClient.SqlException: Cannot insert explicit value for identity column in table 'Events' when IDENTITY_INSERT is set to OFF.

If it's helpful, here is the POCO class in question:

public class Event
{
    [Key, Required]
    public int EventID { get; set; }

    public string EventType { get; set; } //TODO: Event Type Table later on
    public DateTime StartDate { get; set; }
    public DateTime EndDate { get; set; }

    public virtual ICollection<Match> Matches { get; set; }
    public virtual ICollection<EventParticipation> EventParticipation { get; set; }
}

Thanks in advance.

2个回答

31

By default Entity Framework assumes that an integer primary key is database generated (equivalent to adding the attribute HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity) or calling Property(e => e.EventID).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity); in the Fluent API.

If you look at the migration that creates the table you should see this:

   CreateTable(
                "dbo.Events",
                c => new
                    {
                        EventID = c.Int(nullable: false, identity: true),
                        //etc
                    })
                .PrimaryKey(t => t.EventID );

Then you changed the model using the Fluent API to DatabaseGenerated.None. EF puts this in the migration:

AlterColumn("dbo.Events", "EventID", c => c.Int(nullable: false, identity: false))

And the sql generated is this:

ALTER TABLE [dbo].[Events] ALTER COLUMN [EventID] [int] NOT NULL

Which actually does diddly squat. Dropping the IDENTITY from a column is not trivial. You need to drop and recreate the table or create a new column, then you have to copy the data and fix up foreign keys. So it's not surprising that EF isn't doing that for you.

You need to work out how best to do it for yourself. You could roll back your migrations to 0 and re-scaffold from scratch now that you have specified DatabaseGeneratedOption.None, or you could change the migration manually to drop and recreate the table.

Or you could drop and recreate the column:

DropColumn("Customer", "CustomerId"); 
AddColumn("Customer", "CustomerId", c => c.Long(nullable: false, identity: false));

EDIT Or you could Switch Identity On/Off With A Custom Migration Operation

2017-11-30
25

Since I prefer attributes, here the alternative for the sake of completeness:

[Key]
[DatabaseGenerated(DatabaseGeneratedOption.None)]
public int Id { get; set; }

2017-11-30

注:本文内容来自互联网,旨在为开发者提供分享、交流的平台。如有涉及文章版本等事宜,请你联系站长进行处理。