How to create multi-columns "reference" index (join index) in c# model with EF6

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?

Sampath · Accepted Answer · 2016-09-21 08:01:00Z

2

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;}

    }

edited Sep 21, 2016 at 8:01

answered Sep 21, 2016 at 7:06

Sampath

66.2k70 gold badges327 silver badges461 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Potato Over a year ago

Thank you Inspire me, I get the solution

Potato Over a year ago

Thank you for Inspiring me.

Sampath Over a year ago

No problem ! :D

Community · Accepted Answer · 2017-05-23 12:32:43Z

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.

Santhos · Accepted Answer · 2016-09-21 06:55:36Z

0

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

edited Sep 21, 2016 at 6:55

answered Sep 21, 2016 at 6:50

Santhos

3,5285 gold badges33 silver badges49 bronze badges

1 Comment

Potato Over a year ago

If I did this , there will be no foreign key references constraint. and I know your approach can work, because the type is primitives type. I need the foreign key constraint

Collectives™ on Stack Overflow

How to create multi-columns "reference" index (join index) in c# model with EF6

3 Answers 3

3 Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related