0

I am using EF Core 5 with SQL Server and I have following simple entity:

public class Person
{
    public Guid Id { get; set; }
    public string Name { get; set; }
}

which is added in DbContext as:

public DbSet<Person> People { get; set; }

I run add-migration Person get following generated migration code:

public partial class Person : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.CreateTable(
            name: "People",
            columns: table => new
            {
                Id = table.Column<Guid>(type: "uniqueidentifier", nullable: false),
                Name = table.Column<string>(type: "nvarchar(max)", nullable: true)
            },
            constraints: table =>
            {
                table.PrimaryKey("PK_People", x => x.Id);
            });
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DropTable(
            name: "People");
    }
}

Then I run update-database to create this Person table in the database.

  context.People.Add(new Person() { Name = "John" });
  context.SaveChanges();

Looking in the EF log I see SQL command executed:

  Executed DbCommand (21ms) [Parameters=[@p0='115b2a5d-56b7-42c8-2f42-08d8cd03a34e', @p1='John' (Size = 4000)], CommandType='Text', CommandTimeout='30']
  SET NOCOUNT ON;
  INSERT INTO [People] ([Id], [Name])
  VALUES (@p0, @p1);

I notice the first parameter for Id is @p0='115b2a5d-56b7-42c8-2f42-08d8cd03a34e' so this means that Guid is generated by EF and not by database. I want it to be generated by the database.

So I add following attributes to the Id property:

public class Person
{
    [Key]
    [Required]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public Guid Id { get; set; }
    public string Name { get; set; }
}

But when I create a new migration it gives me empty Up() and Down() methods which means from the EF perspective nothing should be changed in the database. Why is this? And how can I make EF Core 5 create Id primary key auto generated by database.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
mlst
  • 2,688
  • 7
  • 27
  • 57
  • It may not change the migration, but does it change the generated SQL in the log? – DavidG Feb 09 '21 at 14:37
  • Is Fluent API option for you? https://stackoverflow.com/questions/25094711/entity-framework-auto-generate-guid – madoxdev Feb 09 '21 at 14:46
  • If you do this, with these attributes, from the outset (erase the db and start again) does it default NewID()? – Caius Jard Feb 09 '21 at 14:51
  • @DavidG I just checked in the log and it doesn't change SQL generated by EF, so the parameter `@p0` is still passed to database. – mlst Feb 09 '21 at 15:09
  • @madoxdev, I just tried Fluent API like this: "modelBuilder.Entity().Property(x => x.Id).HasDefaultValueSql("NEWID()");" but next time I try to add new `Person` I get `SqlException: Cannot insert the value NULL into column 'Id', table 'dbo.People'; column does not allow nulls. UPDATE fails. The statement has been terminated.` – mlst Feb 09 '21 at 15:10
  • @CaiusJard I just tried deleting database and recreating it from scratch with `update-database` command and it didn't solve the problem. What I see in SSMS is that "Default Value or Binding" for Id column is empty which might be the cause of issue. I expected that this is set to `newid()` due to attribute `[DatabaseGenerated(DatabaseGeneratedOption.Identity)]` but this didn't happen. – mlst Feb 09 '21 at 15:22
  • Perhaps your answer is here https://stackoverflow.com/questions/53493025/entity-framework-core-auto-generated-guid – Robin Webb Feb 09 '21 at 15:29
  • As a side note be careful when using Guid as primary key since it is a little bit special in sql server https://stackoverflow.com/questions/11938044/what-are-the-best-practices-for-using-a-guid-as-a-primary-key-specifically-rega/11938495 – fuchs777 Feb 09 '21 at 15:34
  • I wasn't expecting it to solve the problem per se; I was just curious to see whether the presence of that attribute would mean EFC specified the column any differently (my suspicion was "it wouldn't"). Telling EFC "the db generates this" seemingly doesn't necessarily mean "so set it up so it does" – Caius Jard Feb 09 '21 at 15:49
  • @mlst a GUID as a PK is a horrible idea *unless* you ensure you use a sequential GUID algorithm. Otherwise, the random nature of the keys will result in immediate table and index fragmentation, wasted space and seriously harm performance even if you use SSD storage. Check [Using NEWID vs NEWSEQUENTIALID for performance](https://blog.sqlauthority.com/2019/10/30/sql-server-using-newid-vs-newsequentialid-for-performance/) – Panagiotis Kanavos Feb 10 '21 at 08:58

1 Answers1

0

I was looking reading up on the line [DatabaseGenerated(DatabaseGeneratedOption.Identity)] and found this:

Entity Framework Core will not implement a value generation strategy. Database providers differ in the way that values are automatically generated. Some will generate values for selected data types such as Identity, rowversion, GUID.

In case you only want a complex key generated for your mssql database then I suggest to change the Id's type from guid to string

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

In the migration file it looks like this:

migrationBuilder.CreateTable(
columns: table => new
            {
                Id = table.Column<string>(type: "nvarchar(450)", nullable: false),
                Name = table.Column<string>(type: "nvarchar(max)", nullable: true)
            },
Visualcoach
  • 111
  • 1
  • 5