Generate a composite unique constraint/index, in EF Core

Question

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?

Ivan Stoev · Answer 1 · 2019-02-13T14:22:05.680

23

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");

edited Feb 13 '19 at 14:22

answered Nov 23 '16 at 18:12

Ivan Stoev

195,425
15
312
343

Yeah that's what I'm doing now, as I said in the question though, you lose static typing. Maybe this is the only way. – grokky Nov 24 '16 at 08:32
6

For who's reading. You dont lose static typing. Just use nameof(Type.Property) – Sibay May 17 '18 at 13:08
lose the "}" bracket though. entityTypeBuilder.HasIndex("Name", "CategoryId").IsUnique(); – Verbe Feb 13 '19 at 13:48
@BenVertonghen Thanks, fixed. – Ivan Stoev Feb 13 '19 at 14:23

score 13 · Accepted Answer · answered Nov 23 '16 at 16:11

13

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.

answered Nov 23 '16 at 16:11

steamrolla

2,373
1
29
39

6

Yeah but that's messy. Is that the only option? – grokky Nov 23 '16 at 17:03
2

Why do you think that is messy? Adding a foreign key property is not messy, why do you think that? @grokky – kizilsu Nov 23 '16 at 17:20
2

@grokky - I'd argue that not defining the foreign key property is messy. The [EF docs recommend it](https://learn.microsoft.com/en-us/ef/core/modeling/relationships#no-foreign-key-property), and, I'd rather not introduce a shadow property. – steamrolla Nov 23 '16 at 17:32
1

@kizilsu It adds an infrastructure-related property to a domain-related class. It's messy. I don't like it, but it seems that this is the only available option, without giving up on static typing. – grokky Nov 24 '16 at 08:33
If I understand correctly, you are using your entity models directly into your system. I recommend you that you should seperete your db and business logic layers. In business logic models you don't need to keep that foreign key property @grokky – kizilsu Nov 24 '16 at 09:28
1

@kizilsu No I'm not doing that. But the point of an ORM is to be shielded from the workings of the database. Sometimes that's not possible, like in this case. – grokky Nov 24 '16 at 09:36

score 1 · Answer 3 · answered Sep 04 '20 at 19:33

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();

score 1 · Answer 4 · answered Nov 11 '22 at 06:42

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));

Generate a composite unique constraint/index, in EF Core

4 Answers4

Linked

Related