CODEWITHSUNDEEP
sql-serverentity-frameworkentity-framework-coreef-fluent-api
Los Morales
Los Morales

Reputation: 2155

Creating unique index over multiple columns that includes FK column

I'm using EntityFramework Core (code first, fluent API) and SQL Server 2012. I have two tables: Account and AccountType. AccountType is essentially a lookup table with predefined values that maps to an enum. It looks something like this:

Id | Type

0 | Personal

1 | Savings

2 | ...

My Account table has a couple of fields and an FK to the AccountType table. Looks something like this:

Id | AccountTypeId | Name | Fila

1 | 0 | John Doe | A1

2 | 1 | Jane Doe | A1

....

In my DBContext class, I'm trying to make the AccountTypeId + Name + Fila fields a unique index since the combination of those 3 fields will always be unique in my model. When I do this:

Entity<Account>.HasIndex(p => new { p.AccountType, p.Name, p.File }).IsUnique();

I get the following error when trying to add migration:

The property 'AccountType' cannot be added to the entity type 'Account' because a navigation property with the same name already exists on entity type 'Account'.

I'm not sure if this is a DB issue or an EF issue. If I remove p.AccountType from the index, I don't get any errors.

Upvotes: 1

Views: 355

Answers (1)

Sampath
Sampath

Reputation: 65870

You can try as shown below.

Note : You must use primitive data type to create an index.

Entity<Account>.HasIndex(p => new { p.AccountTypeId , p.Name, p.File }).IsUnique();

Upvotes: 1

Related Questions