Entering keys manually with Entity Framework

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.

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][1] 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] [2]


[1]:

[To see links please register here]

[2]:

[To see links please register here]

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

using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

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

Note: This works also in EF Core.