entity-framework-coreef-fluent-apiseedingidentity-column

Insert value into identity column in Core 2.2


Trying to seed database in MSSQL server. 'Id' column is set to identity. I fail to understand why EF needs data for 'Id:

public class Location
{
    public int? Id { get; set; }

    public string Name { get; set; }

    public IList<Office> Offices { get; set; }

}

... fluent API:

modelBuilder.Entity<Location>()
     .HasKey(k => k.Id);

modelBuilder.Entity<Location>()
     .Property(p => p.Id)
     .UseSqlServerIdentityColumn()
     .ValueGeneratedOnAdd();

modelBuilder.Entity<Location>()
     .HasData(
            new Location() { Name = "Sydney" },
            new Location() { Name = "Melbourne" },
            new Location() { Name = "Brisbane" }
    );

... as far as I understand 'Id' doesn't need to be provided if it's generated by server on insert. Why do I get the messages about not providing Id ...


Solution

  • I think that the error is here

    public int? Id { get; set; }
    

    Id should not be nullable.

    Update: What I mean is that you should write:

    public int Id { get; set; }
    

    The question mark makes your property nullable, but since it is a primary key it cannot be null.

    I did a littel example here:

    using System.Collections.Generic;
    
    namespace ConsoleApp2.Models
    {
        public class Location
        {
            public int Id { get; set; }
    
            public string Name { get; set; }
    
            public IList<Office> Offices { get; set; }
        }
    }
    

    Fluent Api

          migrationBuilder.CreateTable(
                    name: "Locations",
                    columns: table => new
                    {
                        Id = table.Column<int>(nullable: false)
                            .Annotation("SqlServer:ValueGenerationStrategy", SqlServerValueGenerationStrategy.IdentityColumn),
                        Name = table.Column<string>(nullable: true)
                    },
                    constraints: table =>
                    {
                        table.PrimaryKey("PK_Locations", x => x.Id);
                    });
    

    I can add new location without problems.

    using ConsoleApp2.Models;
    using System.Collections.Generic;
    
    namespace ConsoleApp2
    {
        class Program
        {
            static void Main(string[] args)
            {
                MyDbContext _c = new MyDbContext();
    
                List<Office> list = new List<Office>()
                {
                      new Office()
                    {
                        OfficeName = "Reception"
                    }
                };
    
    
                Location l = new Location()
                {
                    Name = "New York",
                    Offices = list
                };
    
                _c.Locations.Add(l);
                _c.SaveChanges();
            }
        }
    }
    
    

    Im using .net core 2.1 with EFcore 2.2.2.

    I hope that help.