CODEWITHSUNDEEP
entity-frameworkef-core-2.1
JaneDoe
JaneDoe

Reputation: 462

How to explicitly set the ID property on an autoincrementing table in EFCore

I have a model which has an auto-incrementing ID field by default as is normal. However, I wish to seed the database with initial data and because there are foreign keys I wish to explicitly set the IDs of the seeded data.

My model

public class EntAttribute
{
    public int ID { get; set; }
    public string Title { get; set; }
}

My seeding code:

public class Seeder
{
    private class AllAttributes
    {
        public List<EntAttribute> Attributes { get; set; }
    }

    public bool SeedData()
    {
        AllAttributes seedAttributes;
        string strSource;
        JsonSerializer JsonSer = new JsonSerializer();

        strSource = System.IO.File.ReadAllText(@"Data/SeedData/Attributes.json");
        seedAttributes = JsonConvert.DeserializeObject<AllAttributes>(strSource);

        _context.AddRange(seedAttributes.Attributes);
        _context.SaveChanges();
        return true;
    }
}

Please note, I'm very new to both EFCore and C#. The above is what I've managed to cobble together and it seems to work right up until I save the changes. At this point I get:

SqlException: Cannot insert explicit value for identity column in table 'Attribute' when IDENTITY_INSERT is set to OFF.

Now I'm smart enough to know that this is because I can't explicitly set the ID field in the EntAttribute table because it wants to assign its own via auto-increment. But I'm not smart enough to know what to do about it.

Any help appreciated.

EDIT: Adding the solution based on the accepted answer below because the actual code might help others...

So I added to my Context class the following:

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.HasSequence<int>("EntAttributeNumbering")
            .StartsAt(10);

        modelBuilder.Entity<EntAttribute>()
            .Property(i => i.ID)
            .HasDefaultValueSql("NEXT VALUE FOR EntAttributeNumbering");
    }

This first ensures the a sequence is created (the name is arbitrary) and then secondly, sets it to be used for the relevant table instead of auto-increment. Once this was done I was able to my seed data. There are fewer than 10 records so I only needed to set the start value for the sequence to 10. More would normally make sense but I know there will never be more.

I also had to blitz my migrations because they'd somehow got in a mess but that's probably unrelated.

Upvotes: 1

Views: 2532

Answers (2)

Cyassin
Cyassin

Reputation: 1490

FYI for people using EF Core 3, if using int for your key you can set the start sequence value incase you have seeded data. I found this a much cleaner to solve this problem in my use case which just had a single seeded record.

e.g

            modelBuilder.Entity<TableA>()
                .Property(p => p.TableAId)
                .HasIdentityOptions(startValue: 2);
            modelBuilder.Entity<TableA>()
                .HasData(
                    new TableA
                    {   
                        TableAId = 1,
                        Data = "something"
                    });

https://github.com/npgsql/efcore.pg/issues/367#issuecomment-602111259

Upvotes: 1

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89006

With EF Core you can create and use a Sequence object to assign the IDs, and you can reserve a range of IDs for manual assignment by picking where the sequence starts. With a Sequence you can assign the IDs yourself, or let the database do it for you.

Upvotes: 3

Related Questions