C# EF6 index attribute not working

Question

I'm trying to combine two fields into a unique index with EF6. But the update-database command won't add the 'Owner' field into the index. I'm thinking it's because it's not a primitive type but a FK, but I'm unsure how to fix it.

public class Fund
{
    [Key]
    public int FundId { get; set; }

    [Required]
    [Index("IX_FundNameAndOwner", IsUnique = true, Order = 1)]
    [Index("IX_FundIdentifierAndOwner", IsUnique = true, Order=1)]
    public ApplicationUser Owner { get; set; }


    [Required]
    [Index("IX_FundNameAndOwner", IsUnique = true, Order=2)]
    [MaxLength(25)]
    public string Name { get; set; }

    [Required]
    [Index("IX_FundIdentifierAndOwner", IsUnique = true, Order=2)]
    [MaxLength(25)]
    public string Identifier { get; set; }

    public double Balance { get; set; }
}

Generated indexes:

CREATE UNIQUE NONCLUSTERED INDEX [IX_FundIdentifierAndOwner] ON [dbo].[Funds]([Identifier] ASC);

CREATE UNIQUE NONCLUSTERED INDEX [IX_FundNameAndOwner] ON [dbo].[Funds]([Name] ASC);

CREATE NONCLUSTERED INDEX [IX_Owner_Id] ON [dbo].[Funds]([Owner_Id] ASC);

Any help is greatly appreciated!

Answer 1

When you create a navigation property (reference to another table) you should specify the column name to use as foreign key. If you don't do that EF assumes it is a column named Objectname_Id.

You can see in the sql code that it called your column [Owner_Id]. The problem is that when you do it this way you have no control on the data annotations for that column.

Try this

public class Fund
{
    [Key]
    public int FundId { get; set; }

    [Required]
    [Index("IX_FundNameAndOwner", IsUnique = true, Order = 1)]
    [Index("IX_FundIdentifierAndOwner", IsUnique = true, Order = 1)]
    public int OwnerId { get; set; } // <---- ADD THIS!

    public virtual ApplicationUser Owner { get; set; }


    [Required]
    [Index("IX_FundNameAndOwner", IsUnique = true, Order = 2)]
    [MaxLength(25)]
    public string Name { get; set; }

    [Required]
    [Index("IX_FundIdentifierAndOwner", IsUnique = true, Order = 2)]
    [MaxLength(25)]
    public string Identifier { get; set; }

    public double Balance { get; set; }
}

Adding a column named ObjectId (or Objec_Id) EF understands by convention that the column is for the property Owner.

This is the script generated by the migration:

CREATE TABLE [dbo].[Funds] (
    [FundId] [int] NOT NULL IDENTITY,
    [OwnerId] [int] NOT NULL,
    [Name] [nvarchar](25) NOT NULL,
    [Identifier] [nvarchar](25) NOT NULL,
    [Balance] [float] NOT NULL,
    CONSTRAINT [PK_dbo.Funds] PRIMARY KEY ([FundId])
)
CREATE UNIQUE INDEX [IX_FundIdentifierAndOwner] ON [dbo].[Funds]([OwnerId], [Identifier])
CREATE UNIQUE INDEX [IX_FundNameAndOwner] ON [dbo].[Funds]([OwnerId], [Name])

Here is an explanation of the default Code First Conventions.