Entering Keys Manually with Entity Framework

Entering keys manually with Entity Framework

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

Do I have to set foreign key properties manually when I change associations?

No. Entity framework does this for you. Read Relationships and Navigation Properties for more information.

By assigning a new object to a navigation property. The following
code creates a relationship between a course and a department.
If the objects are attached to the context, the course is also
added to the department.Courses collection, and the
corresponding foreign key property on the course object is set to the
key property value of the department.

• course.Department = department;

But as you observed, this only happens after you call SaveChanges or one of the other actions mentioned in the "Synchronizing the changes between the FKs and Navigation properties" portion of the document linked above.

If you are using POCO entities without proxies, you must make sure
that the DetectChanges method is called to synchronize the related
objects in the context. Note, that the following APIs automatically
trigger a DetectChanges call.

• DbSet.Add
• DbSet.Find
• DbSet.Remove
• DbSet.Local
• DbContext.SaveChanges
• DbSet.Attach
• DbContext.GetValidationErrors
• DbContext.Entry
• DbChangeTracker.Entries
• Executing a LINQ query against a DbSet

If this is not happening at all, my guess is that you haven't properly defined StoreID as the foreign key of the navigation property Store.

Is it possible to make Entity Framework insert primary key that I given?

You can add an annotation of fluent configuration to tell EF the keys are manual. See Entering keys manually with Entity Framework. You could also add a custom convention to handle globally: Convention for DatabaseGeneratedOption.None

Entity Framework: table without primary key

The error means exactly what it says.

Even if you could work around this, trust me, you don't want to. The number of confusing bugs that could be introduced is staggering and scary, not to mention the fact that your performance will likely go down the tubes.

Don't work around this. Fix your data model.

EDIT: I've seen that a number of people are downvoting this question. That's fine, I suppose, but keep in mind that the OP asked about mapping a table without a primary key, not a view. The answer is still the same. Working around the EF's need to have a PK on tables is a bad idea from the standpoint of manageability, data integrity, and performance.

Some have commented that they do not have the ability to fix the underlying data model because they're mapping to a third-party application. That is not a good idea, as the model can change out from under you. Arguably, in that case, you would want to map to a view, which, again, is not what the OP asked.

Can you remove Identity from a primary key with Entity Framework 6?

You can not use ALTER COLUMN to set whether a column is an identity column (How to alter column to identity(1,1)).

Instead, you have to:

• (backup DB)
• CREATE TMP_table with columns of original table, but ID column set to identity: false
• SET IDENTITY_INSERT [TMP_Table] ON
• copy data from original to TMP table
• SET IDENTITY_INSERT [TMP_Table] OFF
• DROP original table
• Rename TMP_table to original table name (EXECUTE sp_rename)

Tip: change the column in SQL Management Studio and inspect the emitted script (SQL SERVER – Add or Remove Identity Property on Column).

Set Id field when inserting using entity framework

In EF Core 3.x you can handle ChangeTracker.Tracked event and reset explicitly set autogenerated PK for each Added entity like this (it doesn't need to be outside, you can self attach the db context to that event):

dbContext.ChangeTracker.Tracked += (sender, e) =>
{
    if (!e.FromQuery && e.Entry.State == EntityState.Added && e.Entry.IsKeySet)
    {
        var pk = e.Entry.Metadata.FindPrimaryKey();
        if (pk.Properties.Count == 1)
        {
            var pkProperty = pk.Properties[0];
            if (pkProperty.ValueGenerated == ValueGenerated.OnAdd &&
                defaultValues.TryGetValue(pkProperty.ClrType, out var defaultValue))
            {
                e.Entry.State = EntityState.Detached;
                e.Entry.CurrentValues[pkProperty] = defaultValue;
                var newEntry = e.Entry.Context.Entry(e.Entry.Entity);
                newEntry.State = EntityState.Added;
            }
        }
    }
};

where defaultValues is a dictionary with boxed 0 (zero) values of the supported auto-increment property types (you can add more if needed):

static readonly Dictionary<Type, object> defaultValues = new Dictionary<Type, object>
{
    { typeof(int), default(int) },
    { typeof(long), default(long) },
    { typeof(decimal), default(decimal) },
};

But note that this won't fix relationships which use explicit FK properties without reference navigation property.

Can you remove Identity from a primary key with Entity Framework 6?

You can not use ALTER COLUMN to set whether a column is an identity column (How to alter column to identity(1,1)).

Instead, you have to:

• (backup DB)
• CREATE TMP_table with columns of original table, but ID column set to identity: false
• SET IDENTITY_INSERT [TMP_Table] ON
• copy data from original to TMP table
• SET IDENTITY_INSERT [TMP_Table] OFF
• DROP original table
• Rename TMP_table to original table name (EXECUTE sp_rename)

Tip: change the column in SQL Management Studio and inspect the emitted script (SQL SERVER – Add or Remove Identity Property on Column).

---

Related Topics