Making two columns unique when jointly compounded using Entity Framework

Question

I have two columns called Monkey and Donkey. Both are non-empty, i.e. null is not allowed. The setup is declared as follows.

protected override void OnModelCreating(ModelBuilder builder)
{
  base.OnModelCreating(builder);
  ...
  builder.Entity<Thing>()
    .Property(a => a.Monkey)
    .IsRequired();
  builder.Entity<Thing>()
    .Property(a => a.Donkey)
    .IsRequired();
}

Now, I learned that the combined content must be unique, i.e. no record may have the same monkey and donkey. According to MSDN (index on multiple props), I'm supposed to use HasColumnAnnotation and pass an instance of IndexAnnotation. It's also suggested in this answer to use IndexAnnotation.AnnotationName from System.Data.Entity.Infrastructure.Annotations.

The problem is that I can't find that method and instead only see HasAnnotation. It's unclear to me if it's a more recent version (as the post linked to is a bit dated by now). I can't access the namespace Entity in the using statement, neither.

builder.Entity<Thing>()
  .Property(a => a.Monkey)
  .HasAnnotation(...);

Is that the right approach or should I continue looking for the other annotation method? What is missing to get the namespace in? Is indexing the appropriate way to go to begin with?

I also tried with index but that requires me to declare a specialized class, which seems clunky and suggests it's the wrong way to go.

builder.Entity<Thing>()
  .HasIndex(a => new IndexDedicatedClass(a.Monkey, a.Donkey))
  .IsUnique();

ASP.NET Core 3.1 ships only with EF Core 3.1, MSDN link is for EF6, see this — milo
– milo, Commented Aug 17, 2021 at 6:22

abdusco · Accepted Answer · 2021-08-17 06:34:50Z

2

You can add a composite index that spans multiple columns, then mark it as unique, which creates a unique index on the DB.

modelBuilder.Entity<Thing>(builder =>
{
    builder
        .HasIndex(e => new { e.Monkey, e.Donkey })
        .IsUnique();
});

This in effect helps ensure that no ($monkey, $donkey) tuple appear twice in the table.

Further info

https://learn.microsoft.com/en-us/ef/core/modeling/indexes?tabs=fluent-api#composite-index

answered Aug 17, 2021 at 6:34

abdusco

11.3k3 gold badges38 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Konrad Viltersten Over a year ago

Ah, I was into that approach but didn't realize I could use an anonymous class for that purpose. Now that I see it, it's kind of obvious. I guess I got stuck in the hard-type-thinking. Is such an approach impacting heavily on the performance or is it precisely equivalent to setting a composite key using pure SQL?

abdusco Over a year ago

It translates to pure SQL. Generate a migration, then its sql script, you'll see a CREATE UNIQUE INDEX $name ON $table($col1, $col2) statement.

Collectives™ on Stack Overflow

Making two columns unique when jointly compounded using Entity Framework

1 Answer 1

Further info

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Further info

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related