EF Core - navigational property in index

Question

I have the following two classes

public class Tip
{
    public string Home { get; set; }
    public string Away { get; set; }
    public string Prediction { get; set; }
    public Tipster Tipster { get; set; }
    ... other properties
}


public class Tipster
{
    public int Id { get; set; }
    public string Username { get; set; }
    public string Platform { get; set; }
}

Now, I want to make unique index in theTip table. According to the EF Core documentation, there is no Data Annotations syntax, so I am using the fluent one:

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Tip>()
            .HasIndex(entity => new { entity.Tipster, entity.Home, entity.Away, entity.Prediction })
            .HasName("IX_UniqueTip")
            .IsUnique();
    }

Now, when I update the database I get the following error

C:..>dotnet ef database update System.InvalidOperationException: Cannot call Property for the property 'Tipster' on entity type 'Tip' because it is configured as a navigation property. Property can only be used to configure scalar properties.

It seems that EF didn't liked the fact that I am using referential property in the index. How can I fix that ?

Answer 1

You can't use navigation property in index defining expression. Instead, you should use the corresponding FK property.

The problem in your case is that you don't have explicit FK property in your model Tip. By convention EF Core will create int? TipsterId shadow property. So theoretically you should be able to use EF.Property method to access it:

.HasIndex(e => new { TipsterId = EF.Property<int>(e, "TipsterId"), e.Home, e.Away, e.Prediction })

Unfortunately this doesn't work currently (EF Core 2.0.1). So you have to resort to HasIndex overload with params string[] propertyNames:

.HasIndex("TipsterId", nameof(Tip.Home), nameof(Tip.Away), nameof(Tip.Prediction))

Answer 2

You must define the property TipsterId explicitly cause the Navigation property define it as shadow, so you cannot use it on custom index or alternate key

public class Tip
{
    public string Home { get; set; }
    public string Away { get; set; }
    public string Prediction { get; set; }

    public int TipsterId { get; set; }

    public Tipster Tipster { get; set; }
    ... other properties
}

Now you can

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Tip>()
        .HasIndex(entity => new { entity.TipsterId, entity.Home, entity.Away, entity.Prediction })
        .HasName("IX_UniqueTip")
        .IsUnique();
}