Autonumber with Entity Framework

Autonumber with Entity Framework

Set the StoreGeneratedPattern attribute to "Identity" in your SSDL for the autoincrement field. It should help.

C#, entity framework, auto increment

Check in your EDMX model, that the autoincrement field's StoreGeneratedPattern attribute is set to "Identity". In this way, EF knows that the autonumbers are handled by the DB.

Here this is explained better: Autonumber with Entity Framework

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.

Entity Framework 6 - Creating record with Autonumber ID throws DbUpdateException

The link that @ssanga pointed to had no accepted solution but the general answer seamed to be set the ID to -1. I tried this but got a different exception which pointed to another issue. The EF autogenerated class for the table had the following lines...

Public Overridable Property tWorkRequests1 As ICollection(Of tWorkRequest) = New HashSet(Of tWorkRequest)
Public Overridable Property tWorkRequest1 As tWorkRequest

Deleting these lines and my Create worked. I then went back to my edmx file in Visual Studio and Generated Model for Database again and these lines did not reappear.

Not sure why they were there in the first place.

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 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.

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.

How to create Autoincrement column in SQLite using EF core?

My model has an entity of type Primary Key integer which should be served as auto increment

The problem is that the property in question is not a PK, but a part of a composite PK, in which case it's not considered as auto generated by convention, as explained in the Generated Values Conventions section of the EF Core documentation:

By convention, non-composite primary keys of type short, int, long, or Guid will be setup to have values generated on add. All other properties will be setup with no value generation.

You need to specify that explicitly:

modelBuilder.Entity<TurnosGeneral>()
    .Property(e => e.No)
    .ValueGeneratedOnAdd();

Update: The above is the general approach applicable for most of the databases. But SQLite supports AutoIncrement only for column of type INTEGER PRIMARY KEY, hence this is not EF Core limitation. Either don't use auto increment or make it non-composite PK.

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

---

Related Topics