Code First: Allow indexes to be specified using attribute/fluent API


"Problem Description:
I'm looking forward to [StoreIgnore] attribute in EF 4 CTP 5. I would like to see a way to create index in table. Something like: [Indexed(Unique=false,Clustered=false)] int A { get; set; } [Indexed(Unique=false,Clustered=false)] int B { get; set; } [Indexed(Unique=false,Clustered=false)] int C { get; set; }"

This item was migrated from the DevDiv work item tracking system [ID=87553].

This work item originated from connect.microsoft.com. A member of the EF team at Microsoft should close the related Connect issue when closing this work item.
Closed Feb 11, 2014 at 10:05 PM by maumar
Verified, closing


mnbayazit wrote Aug 9, 2012 at 3:36 AM

This is the one thing that's really preventing me from diving into EF. This is a must-have feature and I'm shocked it hasn't been implemented yet. I know there are workarounds, but really, they shouldn't be necessary.

RoMiller wrote Jan 29, 2013 at 6:57 PM

EF Team Triage: We agree that this would be a good scenario to enable. Taking into account where we are in the EF6 release along with the size and the impact of this feature our team is not planning to implement it in EF6. Therefore, we are moving it to the Future release to reconsider in the next release.

You can specify indexes when using migrations by adding a CreateIndex statement to the migration that is scaffolded for you. This achieves the same thing requested in this issue, but we agree that an attribute would be useful.

RongieZeng wrote Jan 30, 2013 at 7:15 AM

Sad to hear that. Have been waiting for this future for a long time.

RoMiller wrote Jan 31, 2013 at 10:59 PM

@RongieZeng - We're at the stage in the EF6 release where we've had to draw a line and leave some things out. We agree this would be a useful feature though. BTW it is super easy to add indexes in your migrations in the meantime :)

jez9999 wrote Feb 5, 2013 at 8:36 PM

@RoMiller I don't know if I'm misunderstanding, but what is the point in changing the schema in a migration, but not representing that as an attribute or using the Fluent API when creating the model? Surely that means that the schema change will not be applied when the database is created from scratch (like on a new install of SQL Server).

karldodd wrote May 2, 2013 at 9:29 AM

Very disappointed that it won't be included in EF 6, given that the feature was suggested long long ago.

RichardHsbn wrote May 18, 2013 at 8:09 PM

I'm amazed that it isn't in EF, this should have been there from the start. Adding an index to an table is an basic requirement. And now i read that it is being skipped over and over again for every new release, that really makes me wonder if i have to look for an other ORM if EF doesn't even support the basics.

divega wrote Aug 14, 2013 at 10:25 AM

Note for triage: we discussed tackling this in 6.1.

rdmartin wrote Aug 27, 2013 at 12:29 AM

My understanding is that currently one cannot specify a clustered index on anything other than the primary key (http://stackoverflow.com/questions/5813190). There are use cases where one can achieve dramatic performance benefits by specifying a custom clustured index. I request the implementation of this feature include this ability.

MobyDiskBD wrote Sep 5, 2013 at 4:40 PM

It is great that Microsoft continues to push new features into each version of the E-F. But it is being done at the expense of absolute basics. It took until version 5 to support enums, even though that is one of the most fundamental data types. Version 6 doesn't even support indexes, one of the most fundamental features of SQL.

When the same question is asked over and over on stack overflow, that should send a clear message. People have been asking for this since version 4.

cokobware wrote Sep 16, 2013 at 3:38 PM

This should be put on the list for feature implementation after EF6 releases. Community hacks are ok, but basic support s preferred. This seems like a no-brainer to me.

joezen777 wrote Sep 25, 2013 at 10:57 PM

The many attribute based Index creation methods I've seen don't carry between the actual indexes in SQL and how they should be used. (An index is a physical subset of data pre-sorted in a certain way, not a helpful flag for searching the main data.) I would suggest defining indexes by creating interfaces that apply to the POCO. Then include a mapping for the interface that maps the order of the index columns, uniqueness, and whether they are an INCLUDE. Index interfaces would include a object link between them and the full parent.

so like
public class Poco : DomainBase, IPhoneIndex {
    public int Id {get;set;}
    public string FirstName {get;set;}
    public string LastName {get;set;}
    public string Phone {get;set;}

public interface IIndex<TEntity> where TEntity : DomainBase {
   public TEntity Parent {get;set;}

public interface IPhoneIndex : IIndex<Poco> {
    public string Phone {get;set;} 
    public string LastName {get;set;}

public interface INameIndex : IIndex<Poco> {
    public string LastName {get;set;}
    public string FirstName {Get;set;}

internal class INameIndexMap : EntityTypeConfiguration<INameIndex> {
    internal INameIndexMap() {
       ToTable("IndexName","dbo").SortBy(x => x.LastName).ThenByDescending(x => x.FirstName);//.Include(x => x.PhoneNumber

ajcvickers wrote Nov 7, 2013 at 9:30 PM

First check in related to this: 00a41e149341

Allow 'Code First' annotations to flow through the EDMX. This is required for the index attribute (item 57).

There was already code in the EDM serializer and deserializer to handle serialization of custom metadata annotations. The only annotations that are serialized are those that have a name with the format "A:B" where A is treated as an XML namespace and B as the XML attribute name. This change tests that this works and adds some infrastructure around it by serializing the ClrType attributes that Code First already uses.

In addition to this being a test case for annotation serialization it also paves the way to allow Code First-style o/c loading when using an EDMX, which would then avoid the naming collisions and nested type limitations that EDMX currently has. (Full support for this requires changes to the designer so that it annotates the EDMX with appropriate CLR type names.) An EDMX can flag its use of the Code First loaded by adding an annotation to the EDM container.

A new service has been added that allows for the serialization and deserialization of annotations such that annotations do not have to have just string values.

liuhongbo2012 wrote Nov 15, 2013 at 11:04 PM

will the fix works for oracle, mysql, or other database that my has a slightly incompatible sql on creating index?

ajcvickers wrote Nov 27, 2013 at 6:29 PM

Second check in: cd315136d5a9

The Annotated Fluent Reference Manual (HasAnnotation APIs for item 57)

This change adds Code First fluent API to allow serializable annotations to be placed on store columns. This is required for flowing information through Code First such as is needed by Zumo and to implement the index attribute. The equivalent APIs for conventions are not included here and will be added in a future check in.

The basic idea is that anytime you can do HasColumnName or MapKey, then you can also do HasAnnotation or HasKeyAnnotation. HasKeyAnnotation requires that the name of the key be provided as well since this seems to be the best way to fit it into the current fluent API classes.

ajcvickers wrote Dec 4, 2013 at 6:19 PM

Third check in: a73ee8ecfa7b

Writing on the table... (Fluent API for serializable custom annotations on store types)

This change introduces HasAnnotation for types. This works basically the same way has HasAnnotation for properties. It can be used on the base entity configuration in which case any table that all or part of the entity maps to will get the annotations. It can also be used inside Map calls to put annotations onto different tables for things like TPC, splitting, and many-to-many join tables. In these cases only the table being configured by the given Map call with get the annotations. In cases where multiple configurations map to the same table (e.g. TPH, splitting) the annotations can be specified on one or more of these configurations, but an exception is thrown if two configurations try to set the same annotation to different values. This is the same thing with do on properties.

ajcvickers wrote Dec 5, 2013 at 11:49 PM

Check in 4: 62b893dc1c89

Taking notes at the convention... (HasAnnotation support for types and properties on lightweight conventions)

This change includes the API and testing for HasAnnotation used in lightweight conventions. behavior is the same as when using normal fluent API except that if an annotation has already been explicitly configured in the fluent API then the convention won't touch it.

ajcvickers wrote Dec 14, 2013 at 1:55 AM

Checkin 5: 5829f9087b90

978-1449312961 doesn't have them...but we do! (Almost) (Introduce IndexAttribute and flow into model as IndexAnnotations)

This change introduces the IndexAttribute class together with a convention to read these attributes and generate column annotations based on the the attributes. This includes the ability to merge annotations as necessary when multiple attributes are placed on one property or when multiple properties with attributes are mapped to the same column. Also included is serialization to and from the SSDL XML.

Note that this checkin does NOT cover processing of the annotations by the model differ or the associated generation of indexes by Migrations.

Dennis_Wanke wrote Dec 14, 2013 at 9:13 AM

I wonder if entity model is somehow covered by this issue (http://entityframework.codeplex.com/workitem/1932)

ajcvickers wrote Dec 26, 2013 at 9:50 PM

Checkin 6: 0270fbee705d

What's annotaters, precious? (Add Migrations support for annotations.)

This change adds general-purpose support for column and table annotations in Migrations. This includes:
  • Detecting differences in the model differ
  • Passing information about annotation differences to migration operations
  • Scaffolding of migrations with changes in annotations
Note that this checkin does NOT cover processing of the index annotations by the model differ or the associated generation of indexes by Migrations.

ajcvickers wrote Jan 7, 2014 at 9:07 PM

Checkin 7: c52022252108

Going in for the operation... (Create Migrations operations based on IndexAttributes)

This change updates the model differ to understand index annotations in the store model and create appropriate create and drop operations for the indexes when they are added to the model, removed from the model, or changed in the model.

ajcvickers wrote Jan 7, 2014 at 9:24 PM

Resolving this issue since Migrations operations for indexes can now be specified using IndexAttribute. Two additional and related work items have been created for other enhancements in this area:

BullCreek wrote Aug 25, 2014 at 10:54 PM

It would be nice if when addressing 1966 and/or 1969, you would also address the problem that IndexAttribute IsUnique is essentially useless for nullable columns. Often people want a column where the value can either be null, or not null - and if not null, you want it to be unique. This is accomplished in SQL Server 2008 and later using filtered indexes (http://msdn.microsoft.com/en-us/library/cc280372.aspx).

It would be great if you could extend the IndexAttribute and/or fluent API functionality to support filtered indexes to address this shortcoming. It would be somewhat DB specific, in that MSSQL < 2008 doesn't support it, and mysql and postgres I think ignore null by default so it isn't needed but supporting less than 2008 for all features probably isn't practical anyway?

I'd be willing to make a stab at this, but it strikes me that the easy way wouldn't necessarily be elegant but I'm not sure making it elegant is worth the trouble. Today the code is
            writer.Write("CREATE ");

            if (createIndexOperation.IsUnique)
                writer.Write("UNIQUE ");

            if (createIndexOperation.IsClustered)
                writer.Write("CLUSTERED ");

            writer.Write("INDEX ");
            writer.Write(" ON ");
you could just add another optional parameter to the attribute and tack the following on to the above code:

if (!string.IsNullOrWhitespace(createIndexOperation.Filter)

For this case where you want to ignore NULL values, the Filter string would be something like "WHERE Column IS NOT NULL".