c# .NET 4.8 How to create multi column index using default foreign keys on existing entity in Databa-CodePudding

I'm using .NET 4.8. I have 3 tables: TableA, TableB and TableC. TableA has foreign key relationships already set up to TableB and TableC. This was done using a code first migration.

This is the code for TableA:

public class TableA : Entity<int>
{
    public bool SomeColumn1 { get; set; }
    public bool SomeColumn2 { get; set; }
    public bool SomeColumn3 { get; set; }

    public virtual TableB TableB { get; set; }
    
    public virtual TableC TableC { get; set; }
}

So in my db TableA has the columns TableB_Id and TableC_Id that relate back to their respective tables.

I want to add a unique index to TableA such that the combination of TableB_Id and TableC_Id is unique.

I want to do this in my DatabaseContext.cs file using the FLUENT pattern. I added this to my DatabaseContext.cs in the OnModelCreating() method:

modelBuilder.Entity<TableA>().HasIndex(a => new { a.TableB, a.TableC }).IsUnique();

(a.TableB and a.TableC are the only options available when I enter a. )

I then compile my solution and try to create a migration but I get an error that says

Sequence contains no matching element

I found this SO post that suggests to add values in the base entity, so I changed my code for TableA to this:

public class TableA : Entity<int>
{
    public bool SomeColumn1 { get; set; }
    public bool SomeColumn2 { get; set; }
    public bool SomeColumn3 { get; set; }

    [Required]
    [Index("IX_TableBAndTableC", 1, IsUnique = true)]
    public int TableB_Id { get; set; }
    public virtual TableB TableB { get; set; }
    
    [Required]
    public int TableC_Id { get; set; }
    [Index("IX_TableBAndTableC", 2, IsUnique = true)]
    public virtual TableC TableC { get; set; }
}

and TableB_Id and TableC_Id are now available in my code in DatabaseContext.cs but when I compile the solution and create a migration it drops the existing TableB_Id and TableC_Id columns on TableA and replaces them with TableB_Id1 and TableC_Id1 and then creates the index.

I then found this SO post that suggests to add a foreign key decorator to the base entity so I commented out the code in my DatabaseContext.cs file and changed my code in my TableA entity to be this:

public class TableA : Entity<int>
{
    public bool SomeColumn1 { get; set; }
    public bool SomeColumn2 { get; set; }
    public bool SomeColumn3 { get; set; }

    [ForeignKey("TableB_Id")]
    public virtual TableB TableB { get; set; }
    
    [ForeignKey("TableC_Id")]
    public virtual TableC TableC { get; set; }
}

but when I compile the solution and generate a migration the migration is empty with nothing in the up() and down() methods.

I searched in the official documentation but was not able to find anything that specifically instructed on how to create a multi column unique index using the default columns that get created when I set up the foreign keys using a code first migration (TableA.TableB_Id and TableA.TableC_Id). The columns TableA.TableB_Id and TableA.TableC_Id are created by the entity framework automatically so I am not able to use them directly in my DatabaseContext.cs file.

Please can someone give me some help in figuring out how to create a multi column unique index on these two default foreign keys TableB_Id and TableC_Id by either adding something to the DatabaseContext.cs file or the base entity code for TableA, or both.

A correct, clearly explained answer will be marked as accepted and upvoted. Thanks.

CodePudding user response：

After playing around with it I found this page that talks about foreign key relationships in entity framework. This is where I found a different way to format my code for my TableA entity that will accomplish what I need to do.

So here is the code for my TableA entity:

public class TableA : Entity<int>
{
    public bool SomeColumn1 { get; set; }
    public bool SomeColumn2 { get; set; }
    public bool SomeColumn3 { get; set; }

    [ForeignKey("TableB")]
    public int TableB_Id { get; set; }
    public virtual TableB TableB { get; set; }
        
    [ForeignKey("TableC")]
    public int TableC_Id { get; set; }
    public virtual TableC TableC { get; set; }
}

This is very similar to one approach I tried in my original post, I just had a few things not set up right.

After I added this code to my TableA entity, back in my DatabaseCOntext.cs file I was able to add this line in the OnModelCreating() method:

modelBuilder.Entity<TableA>().HasIndex(a => new { a.TableB_Id, a.TableC_Id }).IsUnique();

which is now pointed at the fields TableA.TableB_Id and TableA.TableC_Id which weren't available before.

I compiled my solution and created a new migration and it appeared like it was going to do everything I needed it to.

I compiled the solution again and ran the migrations and the columns TableA.TableB_Id and TableA.TableC_Id are still present, set up as foreign keys to their respective tables and there is a unique constraint on them that will not allow duplicate rows to be inserted.

I hope this is helpful to others. If what I did is not clear, please leave a comment and I'll do my best to clarify.