How to create multi-columns “reference” index (joi

Question

I know how to create common multi-columns index in c# which is mapping table in database. But I encounter one specifical question on Multiple columns index, here is the code:

public class Table1
{
  [Index("MultipleIndexColumn",1)]
  public Table2 Table2_ID {get; set;}
  [Index("MultipleIndexColumn",2)]
  public Table3 Table3_ID {get; set;}
  [Index("MultipleIndexColumn",3)]
  public DateTime CreateDateTime {get; set;}
}

EF6 will generate t-sql like this :

create index MultipleIndexColumn on Table1(CreateDateTime) which is not the expected sql sentence.

here is my expected : create index MultipleIndexColumn on Table1(Table2_ID,Table3_ID,CreateDateTime)

Could you guys help about this?

Answer 1

You can create index keys only on Primitive Datatypes.So try as shown below.

public class Table1
{

      [ForeignKey("Table2_ID")]
      public virtual Table2  Table2 { get; set; }

      [Index("MultipleIndexColumn",1)]
      public int Table2_ID { get; set; }

      [ForeignKey("Table3_ID")]
      public virtual Table3  Table3 { get; set; }

      [Index("MultipleIndexColumn",2)]
      public int Table3_ID { get; set; }

      [Index("MultipleIndexColumn",3)]
      public DateTime CreateDateTime {get; set;}

    }

Answer 2

I suppose your trouble might be in using "the navigation properties" = the reference types. You should try to define the foreign IDs as separate properties and mark them like:

public class Table1
{

  public Table2 Table2Ref {get; set;}

  public Table3 Table3Ref {get; set;}

  [Index("MultipleIndexColumn",3)]
  public DateTime CreateDateTime {get; set;}

  [Index("MultipleIndexColumn",1)]
  public int Table2Id {get; set;}
  [Index("MultipleIndexColumn",2)]
  public int Table3Id {get; set;}
}

or you could use the fluent api instead

You could use the Fluent API to define an index like that

https://msdn.microsoft.com/en-us/data/jj591617.aspx?f=255&MSPPError=-2147217396#PropertyIndex

For a complete list of the settings available in IndexAttribute, see the Index section of Code First Data Annotations. This includes customizing the index name, creating unique indexes, and creating multi-column indexes.

https://msdn.microsoft.com/en-us/data/jj591583#Index

Answer 3

Thank https://stackoverflow.com/users/1077309/sampath for inspiring me. Here is the Solution: as https://stackoverflow.com/users/1077309/sampath said You can create index keys only on Primitive Datatypes

public class Table1
{

      [ForeignKey("Table2_ID")]
      public virtual Table2  Table2 { get; set; }
      // here is important
      [Index("MultipleIndexColumn",1)]
      public int Table2_ID { get; set; }

      [ForeignKey("Table3_ID")]
      public virtual Table3  Table3 { get; set; }
       // here is important
      [Index("MultipleIndexColumn",2)]
      public int Table3_ID { get; set; }

      [Index("MultipleIndexColumn",3)]
      public DateTime CreateDateTime {get; set;}

    }

as the code The EF6 generate the index as I expected, create index MultipleIndexColumn on Table1(Table2_ID,Table3_ID,CreateDateTime)

and The EF6 Didn't generate redundant column Table3No,Table2No in database. That's perfect.