EF Code First - Multiple column index with navigation property

Question

I want to put an index on multiple columns (like stated in this question), but one of the properties is a navigation property without a foreign key property in the model.

TL;DR at the bottom.

---

My model:

public class User
{
    public int Id { get; set; }
    public string Email { get; set; }
    public virtual Shop Shop { get; set; }
}

public class Shop
{
    public int Id { get; set; }
    public string Name { get; set; }
}

public class Context : DbContext
{
    public DbSet<User> Users { get; set; }
    public DbSet<Shop> Shops { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<User>().HasRequired(u => u.Shop).WithMany();
        modelBuilder.Entity<User>().Property(u => u.Email).HasMaxLength(256);
    }
}

---

I use an extension (based on the for mentioned question):

internal static class ModelBuilderExtensions
{
    public static StringPropertyConfiguration AddMultiColumnIndex(this StringPropertyConfiguration config, string indexName, int columnOrder, bool unique = false, bool clustered = false)
    {
        var indexAttribute = new IndexAttribute(indexName, columnOrder) { IsUnique = unique, IsClustered = clustered };
        var indexAnnotation = new IndexAnnotation(indexAttribute);

        return config.HasColumnAnnotation(IndexAnnotation.AnnotationName, indexAnnotation);
    }

    public static PrimitivePropertyConfiguration AddMultiColumnIndex(this PrimitivePropertyConfiguration property, string indexName, int columnOrder, bool unique = false, bool clustered = false)
    {
        var indexAttribute = new IndexAttribute(indexName, columnOrder) { IsUnique = unique, IsClustered = clustered };
        var indexAnnotation = new IndexAnnotation(indexAttribute);

        return property.HasColumnAnnotation(IndexAnnotation.AnnotationName, indexAnnotation);
    }
}

I wish to create an index as follows:

modelBuilder.Entity<User>().Property(x => x.Email).AddMultiColumnIndex("UX_User_EmailShopId", 0, unique: true);
modelBuilder.Entity<User>().Property(x => x.Shop.Id).AddMultiColumnIndex("UX_User_EmailShopId", 1, unique: true);

But this gives me an error when scaffolding a migration:

System.InvalidOperationException: The type 'Shop' has already been configured as an entity type. It cannot be reconfigured as a complex type.

When I try to add the index as follows it gives another error:

modelBuilder.Entity<User>().Property(x => x.Shop).AddMultiColumnIndex("UX_User_EmailShopId", 1, unique: true);

The type 'My.NameSpace.Shop' must be a non-nullable value type in order to use it as parameter 'T' in the generic type or method 'System.Data.Entity.ModelConfiguration.Configuration.StructuralTypeConfiguration.Property(System.Linq.Expressions.Expression>)

---

TL;DR

So my question is, how do I add an index using the fluent-api on the email and shop (id) combination when the shop_id is not defined in my model (and I don't want to define it)?

The resultant SQL should look something like:

CREATE UNIQUE NONCLUSTERED INDEX [UX_User_EmailShopId] ON [dbo].[User]
(
    [Email] ASC,
    [Shop_Id] ASC
)

Answer 1

TL&DR: If you want to have multiple indexes in the FLUENT Pattern, you can do the following:

You can now (EF core 2.1) use the fluent API as follows:

modelBuilder.Entity<ClassName>()
            .HasIndex(a => new { a.Column1, a.Column2});

Answer 2

You cannot do it using fluent-api without shop_id is defined in the model.

If you noticed, you are using DbModelBuilder, which is refer to model, i.e. the models 'defined' in your code. fluent API goes only one way, from code to DB, but not the opposite.

Still you can create the index without add shop_id to the model but with pure SQL , not the fluent API

You could do it using annotation. Your User class need to be like , but only with with EF 6.1

public class User
{
    [Index("UX_User_EmailShopId", 1, IsUnique =  true)]
    public int Id { get; set; }

    [Index("UX_User_EmailShopId", 2, IsUnique = true)]
    public string Email { get; set; }

    [Index("UX_User_EmailShopId", 3, IsUnique = true)]
    public int ShopId { get; set; }

    [ForeignKey("ShopId")]
    public virtual Shop Shop { get; set; }
}

Answer 3

There is a couple of things you must do to achieve this using C# and fluent syntax.

1. Add ShopId (foreign key to User class. EF will understand that this is the foreign key for the Shop navigation property, so no more actions needed

   public virtual Shop Shop { get; set; }
   public int ShopId { get; set; }
   

2. Use x.ShopId in you modelbuilder index-config

   modelBuilder.Entity<User>().Property(x => x.ShopId).AddMultiColumnIndex("UX_User_EmailShopId", 1, unique: true);
   

3. Add MaxLength constraint on your User.Email property config in OnModelCreating. This because index columns can max be 900 bytes, and a regular public string in C# translates into a varchar(max) in sql server.

   modelBuilder.Entity<User>().Property(c => c.Email).HasMaxLength(255).AddMultiColumnIndex("UX_User_EmailShopId", 0, unique: true);;
   

So what you end up with is one extra property in the User class and this OnModelCreating method in your dbcontext:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Entity<User>().HasRequired(u => u.Shop).WithMany();
    modelBuilder.Entity<User>().Property(x => x.Email).HasMaxLength(255).AddMultiColumnIndex("UX_User_EmailShopId", 0, unique: true);
    modelBuilder.Entity<User>().Property(x => x.ShopId).AddMultiColumnIndex("UX_User_EmailShopId", 1, unique: true);
}

These small changes will result in this index being created in SQL-server:

CREATE UNIQUE NONCLUSTERED INDEX [UX_User_EmailShopId]
ON [dbo].[Users]([Email] ASC, [ShopId] ASC);

Alternatively using migrations, and not wanting to add the "FK" property ShopId to you User class, you could add the index creation in the migration UP method.

CreateIndex("Users", new[] { "Email", "ShopId" }, true, "UX_User_EmailShopId");