EF Core Fluent API - unique constraint not being detected/included in migration

Question

I am trying to add a unique constraint on two columns. I found multiple sources declaring that I should be using HasAlternateKey method for EF Core Fluent API applications. However, after running Add-Migration, the code that is generated in the migration files does not include the constraint - almost as if its being purposely ignored or not detected.

Here is the ModelBuilder code that I am using:

        modelBuilder.Entity<PlasmidStockCode>(e =>
        {
            e.ToTable("PlasmidStockCode");

            e.HasKey(c => c.ID);
            e.Property(c => c.ID).ValueGeneratedOnAdd();

            e.HasAlternateKey(c => new { c.ClientName, c.PlasmidName });

            e.HasMany(c => c.PlasmidStockComments).WithOne().HasForeignKey(c => c.PlasmidStockCodeID).OnDelete(DeleteBehavior.Restrict);
            e.HasMany(c => c.PlasmidStockLots).WithOne().HasForeignKey(c => c.PlasmidStockCodeID).OnDelete(DeleteBehavior.Restrict);
            e.HasMany(c => c.qPCRTargets).WithOne().HasForeignKey(c => c.PlasmidStockCodeID).OnDelete(DeleteBehavior.Restrict);
        });

        AddBaseConfiguration(modelBuilder);
    }

I've also attempted to use .HasIndex, but unfortunately, is not being detected/included either.

Any idea as to what I may be missing? Thanks in advance!

Is your ModelSnapshot in a valid state and reflects the configurations you have done in the fluent API? Because EF generates the new migration files based on the difference between the ModelSnapshot and your previous migrations — i regular, Aug 17 '18 at 15:17
Just tried the same on my workstation using command `Scaffold-DbContext "Server=localhost;Database=Test1;Trusted_Connection=True;" Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models'`, which resulted in the following code: `entity.HasIndex(e => new { e.Surname, e.Lastname }).HasName("UniqueName").IsUnique();` Which version of .NET Core are you using? — Bouke, Aug 17 '18 at 17:15
@i regular AddBaseConfiguration is a method in our Base model. It adds all of our base fields to the table/model, such as IsActive, LastUpdatedBy, LastUpdatedDate, etc. — Dan, Aug 17 '18 at 19:04
The way it is now, the question is unanswerable, since both `HasAlternateKey` and `HasIndex` should work. Please provide [mcve]. — Ivan Stoev, Aug 17 '18 at 23:11
I have the same problem. Migrations do work. Other changes are detected. Just not `HasAlternateKey` or `HasIndex`. Using EF Core 2.2.0. Note, I do this in the `IEntityTypeConfiguration.Configure()` method implementation, since `OnModelBuilding` is overridden and sealed in my solution. — Stefan de Kok, Jun 09 '19 at 19:42
I was able to fix my case. The composite alternate key included the Id field of a navigation property. By changing the shadow foreign key property into a real property, it now works. Not pretty. I hate adding foreign keys as properties in my domain classes, but it does the job. Anyone have a way to avoid this, would love to hear it... — Stefan de Kok, Jun 09 '19 at 20:05
created new question for this case: https://stackoverflow.com/q/56518114/1592432 — Stefan de Kok, Jun 09 '19 at 20:25

score 3 · Answer 1 · answered Aug 17 '18 at 15:47

3

Use IsUnique and HasIndex methods in you DBContext class's OnModelCreating method to add the composite key to your table. Then add migration using Add-Migration <YourMigrationName> and then run Update-Database in Program Manager Console.

modelBuilder.Entity<PlasmidStockCode>(b =>
    {
        b.HasIndex(e => (new { e.ClientName, e.PlasmidName })).IsUnique();
    });

answered Aug 17 '18 at 15:47

Vivek Nuna

25,472
25
109
197

His approach will do this 'under the hood' – i regular Aug 17 '18 at 16:28
@iregular didn’t get you? – Vivek Nuna Aug 17 '18 at 16:40
I appreciate the response, but unfortunately, this didn't work. I changed my code to reflect exactly this, ran another add-migration, and there were not any changes to the migration file generated (in either the up or down methods). – Dan Aug 17 '18 at 16:49
Is your class added in DBContext file? – Vivek Nuna Aug 17 '18 at 16:54
Are you adding or altering table? – Vivek Nuna Aug 17 '18 at 16:55
@viveknuna I'm simply trying to say that this method will indeed work, but the suggested by the OP i.e. using HasAlternateKey will do the same thing :) – i regular Aug 17 '18 at 17:26
But I have no clue why his migrations aren't updating proberly the ModelSnapshot must not reflect the changes, because both your and mine solution work for me – i regular Aug 17 '18 at 17:31
@Dan can you check your database or snapshot, whether it already has these changes or not? – Vivek Nuna Aug 17 '18 at 17:50
@viveknuna @i regular I just tried adding another migration and updating the database. Unfortunately, there were not any constraints/indexes added to the database, and I do not see any references to them in the ModelSnapshot either. – Dan Aug 17 '18 at 18:09
if they are not added to the modelsnapshot something is wrong did you remember to save :p? – i regular Aug 17 '18 at 18:59
@Dan you can try by creating a different migration, like adding a new table or altering an existing table. And check whether you are able to generate the migration and update database – Vivek Nuna Aug 17 '18 at 19:13
@Dan if nothing works, last thing you can try by deleting the existing database. And run add migration and update-database. – Vivek Nuna Aug 17 '18 at 19:16

score 0 · Answer 2 · answered Aug 17 '18 at 15:12

0

I did something similar and it seems to work just fine:

 builder.Entity<AccountUser>().HasAlternateKey(c => new { c.a, c.b });

The constraint is added to the Up method of the generated migration file

migrationBuilder.AddUniqueConstraint(
name: "AK_Users_a_b",
table: "Users",
columns: new[] { "a", "b" });

answered Aug 17 '18 at 15:12

i regular

584
6
16

I tried this, and also Vivek's code below, but unfortunately, neither are producing any changes to the up/down methods in the migration files that are generated. I'm stumped. Something to note, I had to set the max length and required attributes for the columns as data annotations in the models (StringLength) instead of in the ModelBuilder, as they were also not being detected/added to the generated migration files. – Dan Aug 17 '18 at 16:55
Well idk then this should work if done properly (both me and bouke has tried). As i noted in the comments do you see your changes in the modelbuilder reflected in the snapshot? – i regular Aug 17 '18 at 17:27

EF Core Fluent API - unique constraint not being detected/included in migration

2 Answers2