Entity Framework Core Prevent Duplicate Values, Adding specific multiple constraints without overiding existing ones

Question

in the past I faced an issue with duplicate values in db so I added some composite contraint

Entity Framework Core Prevent Duplicate Values, Add specific constraint

Today I noticed I was able to add a duplicate record in the database. The following constraint does not work as expected, it only works for the activated sales, which is giving me impression that the following configuration using fluentapi is overiding. How to make sure I can apply both so I have only 1 constraint for pending sales AND 1 constraint for activated sales.

//preventing more than 1 pending sale in db for one user   THIS ONE IS NOT APPLIED
    modelBuilder.Entity<Sale>().HasIndex(e => new { e.SaleStatus, e.UserObjectId }).IsUnique().HasFilter("[SaleStatus] = 0");

    //preventing more than 1 activated sale for 1 user in db THIS ONE WORKS AS EXPECTED
    modelBuilder.Entity<Sale>().HasIndex(e => new { e.SaleStatus, e.UserObjectId }).IsUnique().HasFilter("[SaleStatus] = 5");

I tried to change the order and also tried to apply them in a separate migrations but it always works only for one of them.

vernou · Accepted Answer · 2023-08-17 13:57:20Z

From the documentation EntityTypeBuilder.HasIndex :

Configures an unnamed index on the specified properties. If there is an existing index on the given list of properties, then the existing index will be returned for configuration.

In your case, the two indexes have the same properties, EF Core treat both as the same :

var index1 = modelBuilder.Entity<Sale>().HasIndex(e => new { e.SaleStatus, e.UserObjectId });
index1.IsUnique().HasFilter("[SaleStatus] = 0");

var index2 = modelBuilder.Entity<Sale>().HasIndex(e => new { e.SaleStatus, e.UserObjectId });

Assert.ReferenceEqual(index1, index2 ); // True
index2.IsUnique().HasFilter("[SaleStatus] = 5"); //Override the precedent filter

A solution is to name index like :

modelBuilder.Entity<Sale>().HasIndex(e => new { e.SaleStatus, e.UserObjectId }, "Sale_Index1_StatusIs0").IsUnique().HasFilter("[SaleStatus] = 0");

modelBuilder.Entity<Sale>().HasIndex(e => new { e.SaleStatus, e.UserObjectId }, "Sale_Index1_StatusIs5").IsUnique().HasFilter("[SaleStatus] = 5");

thanks @vernou interesting, I tried that with HasDatabaseName("IX_UniquePendingSales") but that did not work, would be curious how did you find that magical overload :)
From the below link in my answer. The page show all overloads.

Collectives™ on Stack Overflow

Entity Framework Core Prevent Duplicate Values, Adding specific multiple constraints without overiding existing ones

1 Answer 1

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Linked

Related