0

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();
Konrad Viltersten
  • 36,151
  • 76
  • 250
  • 438
  • ASP.NET Core 3.1 ships only with EF Core 3.1, MSDN link is for EF6, see [this](https://learn.microsoft.com/en-us/ef/core/modeling/indexes?tabs=fluent-api#composite-index) – milo Aug 17 '21 at 06:22

1 Answers1

1

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

abdusco
  • 9,700
  • 2
  • 27
  • 44
  • 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? – Konrad Viltersten Aug 17 '21 at 06:53
  • 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. – abdusco Aug 17 '21 at 06:54