Entity Framework trying to add null primary key with auto increment

Question

I'm starting a new Blazor project using Entity Framework 7 and an existing database.

I created a class refering to a existing table from my database.

public class IEA_EtatsDemandes
{
    public IEA_EtatsDemandes() { }

    [Key]
    public int id_etat { get; set; }

    public string libelle { get; set; }
}

I wrote my DbContext.

public class DataContext : DbContext
{
    public DataContext() { }

    public DataContext(DbContextOptions<DataContext> options) : base(options) { }

    public virtual DbSet<IEA_EtatsDemandes> IEA_EtatsDemandes { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        
    }
}

I made a front page that calls my service and add a new data in my table.

public class IEA_EtatsDemandeService
{
    private IDbContextFactory<DataContext> _dbContextFactory;

    public IEA_EtatsDemandeService(IDbContextFactory<DataContext> dbContextFactory)
    {
        _dbContextFactory = dbContextFactory;
    }

    public void AddEtat(IEA_EtatsDemandes etat)
    {
        using(var context = _dbContextFactory.CreateDbContext())
        {
            context.IEA_EtatsDemandes.Add(etat);
            context.SaveChanges();
        }
    }
}

When I create my IEA_EtatsDemandes etat object that I pass to my AddEtat method, if I specify an id_etat, it works and the data is added to my table. But what I want is my primary key to auto increment.

I tried to use data anotations in my model

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

and in the DbContext

protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<IEA_EtatsDemandes>(entity =>
        {
            entity.Property(e => e.id_etat).ValueGeneratedOnAdd();
        });
    }

When my IEA_EtatsDemandes etat is created, id_etat is set to 0. On context.SaveChanges();, an error occurs :

Cannot insert the value NULL into column 'id_etat', table 'XXX.dbo.IEA_EtatsDemandes'; column does not allow nulls. INSERT fails. The statement has been terminated.

I don't understand why EF is trying to insert an null value, and I don't find how to not do so.

What's going on ?

Answer 1

It looks like your database schema is pre-existing. Your [DatabaseGenerated] annotation is sufficient to tell EF that you want the database to manage the PK, so you do not need the ValueGeneratedOnAdd, however you will need to configure the database to use an Identity column for the PK.

If that is an SQL Server database for instance, go into the table designer and for the PK field, look for the "Identity" option and select "Yes" to configure an identity sequence for that column. From there you should see the behaviour you are expecting.

The DatabaseGeneratedOption.Identity option tells EF to expect the database to set the ID, so don't pass a value on create (why the database is complaining about #null) and to expect to get and populate an ID after insert. When EF is set up for Code First and controlling the schema, this option will tell EF to setup that identity sequence on the table. Otherwise you need to set up the identity on the table yourself.