CODEWITHSUNDEEP
entity-frameworkentity-framework-coreentity-framework-migrationsef-fluent-api
grokky
grokky

Reputation: 9295

Generate a composite unique constraint/index, in EF Core

I need a composite unique constraint for my entity's Name property, which is unique per Category (for which it has an FK).

So something like this:

entityTypeBuilder
  .HasIndex(i => new { i.Name, i.Category.Id })
  .IsUnique();

But this fails when I generate a migration, because of the Category.Id navigation property.

I know I can hardcode the values as strings, but I don't want to lose the static typing.

What options do I have?

Upvotes: 23

Views: 19992

Answers (4)

misanthrop
misanthrop

Reputation: 939

The detailed answer is only in the comments of the post https://stackoverflow.com/a/40771546/9641435

So I wanted to share the actual code that you could use if you do not want to lose static typing:

entityTypeBuilder
  .HasIndex(nameof(MyType.Name), nameof(MyType.CategoryId))
  .IsUnique();

or:

entityTypeBuilder
  .HasAlternateKey(nameof(MyType.Name), nameof(MyType.CategoryId));

Upvotes: 2

Craig
Craig

Reputation: 414

As an extension to Ivan's excellent response:

If you define the foreign key upfront, you can at least control the name association

const string auditForeignKey = "AuditId";
builder.HasOne(e => e.Audit)
       .WithMany(e => e.AuditLocations)
       .HasForeignKey(auditForeignKey);

builder.HasIndex(auditForeignKey, nameof(AuditLocation.LocationId)).IsUnique();

Upvotes: 1

Ivan Stoev
Ivan Stoev

Reputation: 205849

As soon as you know the shadow property name, you can use (at least in EF Core 1.1.0) the string based HasIndex method overload

public virtual IndexBuilder HasIndex(params string[] propertyNames)

e.g.

entityTypeBuilder
  .HasIndex("Name", "CategoryId")
  .IsUnique();

Same for HasAlternateKey:

entityTypeBuilder
  .HasAlternateKey("Name", "CategoryId");

Upvotes: 23

steamrolla
steamrolla

Reputation: 2491

Add a foreign key for Category of CategoryId on the entity in question and use that in the index builder instead of the navigation property.

Upvotes: 13

Related Questions