How to Generate and Auto Increment Id with Entity Framework

How to generate and auto increment Id with Entity Framework

This is a guess :)

Is it because the ID is a string? What happens if you change it to int?

I mean:

 public int Id { get; set; }

Auto increment primary key with Entity Framework

To Make auto-increment at Database level you should include IDENTITY in the DDL: UserID int identity(1,1) not null

(1,1) stands for UserID will start at 1 and increment by 1 for each INSERT.

Auto-increment on partial primary key with Entity Framework Core

Well those Data Annotations should do the trick, maybe is something related with the PostgreSQL Provider.

From EF Core documentation:

Depending on the database provider being used, values may be generated
client side by EF or in the database. If the value is generated by the
database, then EF may assign a temporary value when you add the entity
to the context. This temporary value will then be replaced by the
database generated value during SaveChanges.

You could also try with this Fluent Api configuration:

modelBuilder.Entity<Foo>()
            .Property(f => f.Id)
            .ValueGeneratedOnAdd();

But as I said earlier, I think this is something related with the DB provider. Try to add a new row to your DB and check later if was generated a value to the Id column.

Generate and auto increment the Id in Entity Framework database first

You should be calling Add instead of Update as this is a new instance you want to insert into the data store. Your Save method should check if the primary (auto incremented key) has a value greater than 0 to see if it is new or not. Alternatively you can also see if it is already attached. There is also no need to call Update, setting the entity to state modified does nothing except ensure that all properties will be written back to the DB but the DbContext implements change tracking on entities so this will already happen (unless you are working in a detached state).

public void Save(CampaignLanguage campaignLanguage)
{
    if(campaignLanguage.Id == 0)
        _campaignLanguageRepository.Add(campaignLanguage);
    else
        _campaignLanguageRepository.Update(campaignLanguage);

    _unitOfWork.Commit();
}

---

On a side note: The type DbContext already implements a Unit of Work pattern and DbSet<T> is an implementation of a Repository pattern. There should not be any need to add another customer Unit of work and repository pattern around EF, you are just creating a whole abstraction layer for no reason that will problems with readability as well as issues later when you want to perform more complex operations like joining multiple tables together in a query.

How to auto increment Id with VARCHAR or NVARCHAR in Entity Framework Core?

There are mainly different workarounds, the simplest would be modifying the OnModelCreating method to use one of the fluent API functions HasComputedColumnSql on the computed column.

Code First Approach

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Employee>()
    .Property(e => e.EmployeeNo)
    .HasComputedColumnSql("[PreFix]+ RIGHT('0000000' + CAST(Id AS VARCHAR(7)), 7)");
}

Database First Approach

For a database first approach you could still have the computation logic defined while creating the table and use the following modification in the context side of the entity framework core.

protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Contact>()
            .Property(p => p.EmployeeNo)
            .ValueGeneratedOnAdd();
    }

How to tell Entity Framework that my ID column is auto-incremented (AspNet Core 2.0 + PostgreSQL)?

You have to use here "ValueGenerationOnAdd()". As the issue you are getting is already reported on GitHub. Please find the below link.

https://github.com/npgsql/Npgsql.EntityFrameworkCore.PostgreSQL/issues/73

You can find more info regarding Generated Value pattern from following link.

Value generated on add

public classs SampleContext:DBContext{
public DbSet<Tag> Tag { get; set; }

protected override void OnModelCreating(ModelBuilder modelBuilder){
    modelBuilder.Entity<Tag>()
        .Property(p => p.ID)
        .ValueGeneratedOnAdd();
 }
public class Tag{
  public int Id { get; set; }
  public string Name { get; set; }
  public string Description{get;set;}
  }
}

Source:- https://www.learnentityframeworkcore.com/configuration/fluent-api/valuegeneratedonadd-method

Hope this will help

Using Entity Framework with code-first no autoincrement

You will have to put below two attributes on ID column

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

As per this blog:

If you forget to mention [Key] , assuming you have made it not null, and explicitly say > Id in C# code, EF will try to pass NULL since
its an identity and will throw an exception “Cannot insert the value
NULL into column……….“, so can just modify
DatabaseGeneratedOption.Identity to DatabaseGeneratedOption.None –
which might not fulfill the auto-increment need. So, just keep [Key]
and let DB generator to fill it for you. This is the approach when it
comes to concurrency.

I hope this answers your query.

Best practices for auto increment a number EF Core

One way would be to do the following;

1) Use integers as keys, and set ClientNumber property to be nullable.

    public class Bar
    {
        public int BarId { get; set; }
        public ICollection<Client> Clients { get; set; }
    }

    public class Client
    {
        public int ClientId { get; set; }
        public Bar Bar { get; set; }
        public int BarId { get; set; }
        public int? ClientNumber { get; set; }
    }

2) Set ClientId to identity (automatic increment) and ClientNumber to database generated (otherwise EF Core will not re-read the value from database after insert)

entity.Property(e => e.ClientId).ValueGeneratedOnAdd();
entity.Property(e => e.ClientNumber).ValueGeneratedOnAddOrUpdate();

3) Add a trigger to modify the ClientNumber after insert

    create trigger ClientNumber on Clients after insert 
    as
    begin
        set nocount on;
        update c set ClientNumber = n.RowNum from Clients c
        inner join (
            select ClientId, ROW_NUMBER() OVER(PARTITION BY BarId ORDER BY ClientId ASC) as RowNum
            from Clients
        ) n on n.ClientId = c.ClientId
    where c.ClientId in (select ClientId from inserted)
    end

Now all works automatically on insert:

            var bar1 = new Bar() { Clients = new List<Client>() };
            var bar2 = new Bar() { Clients = new List<Client>() };

            bar1.Clients.Add(new Client());
            bar1.Clients.Add(new Client());

            bar2.Clients.Add(new Client());
            bar2.Clients.Add(new Client());

            context.Bars.AddRange(new[] { bar1, bar2 });
            await context.SaveChangesAsync();

            bar1.Clients.Add(new Client());
            bar2.Clients.Add(new Client());
            bar1.Clients.Add(new Client());
            await context.SaveChangesAsync();

Will render

ClientId    BarId   ClientNumber
1   1   1
2   1   2
6   1   3
7   1   4
3   2   1
4   2   2
5   2   3

Downside is that you cannot use the unique index IX_CLIENT_NUMBER since ClientNumber will be NULL until the trigger executes.

---

Related Topics