In Entity Framework 6.1 (not Core), how can I use the IndexAttribute to define a clustered index?

Question

Entity Framework 6.1 (code-first) has added the possibility of adding indexes via the IndexAttribute. The attribute takes a parameter for specifying whether the index should be clustered or non-clustered.

At the same time, AFAIK, Entity Framework requires every entity to have a primary key (annotated with the KeyAttribute), and that primary key is always created as a clustered key.

Therefore, as soon as I apply the IndexAttribute with IsClustered = true, I get an error because, due to the key, there already is a clustered index.

So, how can I create a clustered index that is not the primary key using the IndexAttribute? Is the IsClustered property of the IndexAttribute usable at all?

(For a little more context: I'm mapping a table that is only used for reading via LINQ queries. I do not need to actually insert, update, or delete entities from that table. Therefore, I don't need a primary key at all. Ideally, I'd like a table without a primary key, but with a non-unique, clustered index optimized for reading.)

Edit (2014-04-11): See also https://entityframework.codeplex.com/workitem/2212.

Possible duplicate of [How to create a Clustered Index with Entity Framework Core](https://stackoverflow.com/questions/38944128/how-to-create-a-clustered-index-with-entity-framework-core) — Zze, Mar 06 '18 at 10:40
@Zze How would it be a duplicate of that question when Entity Framework Core is a totally different thing than Entity Framework 6.1? Also, this question is from 2014, when Entity Framework Core didn't even exist. — Fabian Schmied, Mar 08 '18 at 19:26

Colin · Accepted Answer · 2014-04-04T15:02:49.447

There can only be one clustered index on a table and by default Entity Framework/Sql Server puts it on the primary key.

So what use is the IsClustered attribute on an index that is not the primary key? Good question! (+1)

This class:

public class Blog
{
    [Key()]
    public int Id { get; set; }

    [MaxLength(256)]//Need to limit size of column for clustered indexes
    public string Title { get; set; }

    [Index("IdAndRating", IsClustered = true)]
    public int Rating { get; set; }

}

will generate this migration:

    public override void Up()
    {
        CreateTable(
            "dbo.Blogs",
            c => new
                {
                    Id = c.Int(nullable: false, identity: true),
                    Title = c.String(maxLength: 256),
                    Rating = c.Int(nullable: false),
                });
            .PrimaryKey(t => t.Id)
            .Index(t => t.Rating, clustered: true, name: "IdAndRating");
    }

Alter the migration to this:

    public override void Up()
    {
        CreateTable(
            "dbo.Blogs",
            c => new
                {
                    Id = c.Int(nullable: false, identity: true),
                    Title = c.String(maxLength: 256),
                    Rating = c.Int(nullable: false),
                });

        CreateIndex("dbo.Blogs", 
                    new[] { "Rating", "Title" }, 
                    clustered: true, 
                    name: "IdAndRating");

    }

And that should create your table without a primary key but with the clustered index on the other columns

EDIT In your scenario where you don't need to insert, update or delete data, you don't need a full blown entity, you could use raw sql queries to populate the classes. You would need to add your own sql to the migration to create the table because EF won't automate it, but that means you can create the table and index just as you want it.

Thanks for the answer; I'll just take it as "the `IndexAttribute`'s IsClustered property is effectively useless and requires manual workarounds, such as hand-written migrations or a subclassed code generator". — Fabian Schmied, Apr 11 '14 at 05:45
This isn't useful for a table that already exists. We need to be able to drop the existing clustered index, re-create it as non-clustered, then create a new clustered index on other columns. If we do all this manually, how does EF know that the primary key is no longer clustered? Does it care, or would it impact query generation? — Triynko, Jun 17 '16 at 18:51
@Triynko EF doesn't need to know that the primary key is no longer clustered. It doesn't care and there is no impact on query generation — Colin, Jun 20 '16 at 10:50

score 5 · Answer 2 · answered Apr 08 '14 at 12:34

You can derive your own class from SqlServerMigrationSqlGenerator and change pk creation there:

public class NonClusteredPrimaryKeySqlMigrationSqlGenerator : SqlServerMigrationSqlGenerator
{
    protected override void Generate(System.Data.Entity.Migrations.Model.AddPrimaryKeyOperation addPrimaryKeyOperation)
    {
        addPrimaryKeyOperation.IsClustered = false;
        base.Generate(addPrimaryKeyOperation);
    }

    protected override void Generate(System.Data.Entity.Migrations.Model.CreateTableOperation createTableOperation)
    {
        createTableOperation.PrimaryKey.IsClustered = false;
        base.Generate(createTableOperation);
    }

    protected override void Generate(System.Data.Entity.Migrations.Model.MoveTableOperation moveTableOperation)
    {
        moveTableOperation.CreateTableOperation.PrimaryKey.IsClustered = false;
        base.Generate(moveTableOperation);
    }

full example here https://entityframework.codeplex.com/workitem/2163

score 2 · Answer 3 · answered Mar 06 '15 at 15:02

Below is the code based on raditch's answer that worked for me. This allows the primary keys to default to clustered. It may need tweaked as we do not use the built in ef migrations to actually handle the changes

public class NonClusteredPrimaryKeySqlMigrationSqlGenerator : SqlServerMigrationSqlGenerator
{
    public override IEnumerable<System.Data.Entity.Migrations.Sql.MigrationStatement> Generate(IEnumerable<MigrationOperation> migrationOperations, string providerManifestToken)
    {
        var primaries = migrationOperations.OfType<CreateTableOperation>().Where(x => x.PrimaryKey.IsClustered).Select(x => x.PrimaryKey).ToList();
        var indexes = migrationOperations.OfType<CreateIndexOperation>().Where(x => x.IsClustered).ToList();
        foreach (var index in indexes)
        {
            var primary = primaries.Where(x => x.Table == index.Table).SingleOrDefault();
            if (primary != null)
            {
                primary.IsClustered = false;
            }
        }
        return base.Generate(migrationOperations, providerManifestToken);
    }
}
public class EFCustomConfiguration : DbConfiguration
{
    public EFCustomConfiguration()
    {
        SetMigrationSqlGenerator("System.Data.SqlClient", () => new NonClusteredPrimaryKeySqlMigrationSqlGenerator());
    }
}

score 1 · Answer 4 · answered Apr 02 '14 at 13:37

1

Telling you the truth - the IndexAttribute is totally redundant and not suitable for professinal development. They lack core functionality and focus on stuff that makes little sense.

Why? Because it never can will and should be as flexible as a build script. Clustered index is only one thing - the next thing I would miss is a filtered index, mostly in teh form of "Unique index for non null, non-unique index for null" on a field, which I happen to use very regularly for optional unique codes (because in SQL Server a NULL is equal to another NULL in SQL generation, so you can only have one NULL at a time in a unique index).

If I were you I would stay away from database generation - and migrations - and use a classical setup/migration scripts approach. Thta is something where you can do more complex multi step migrations without possibly ata loss. EF does not handle anything but the most basic scenarios - and in these areas I doubt that is enough. Can be it is because I also and mostly work on large databases where we do our changes very carefully - adding an index can take some time when you hit a double digit number of billions of rows (!0+).

I would prefer the developers would focus on some ofher missing areas tht can not easily and better be worked around, like performance, like core ORM features (better enums, second level caching, bulk delete API, more performance inserts and updates - all things that are doable). Code First is nice. Code First generating and maintainign the database is - painfull outside extremely simple scenarios.

answered Apr 02 '14 at 13:37

TomTom

61,059
10
88
148

Not sure what you mean by "staying away from migrations." What is a "classical setup/migration scripts approach"? When I want a unique index I use Sql in a migration - a bit like this: http://stackoverflow.com/a/22038834/150342 or this http://stackoverflow.com/a/18205124/150342 – Colin Apr 02 '14 at 14:18
This is a set of scripts that get executed to update version numbers of databases. The same way people have managed database versioning in the last like 50 years. New version executes a set of scripts to update the database in a controlled manner. – TomTom Apr 02 '14 at 14:24
OK but don't you use migrations to generate those scripts and handle versioning? – Colin Apr 02 '14 at 14:29
No, never. See, Migrations are suuuper primitive. Hopw do you move from a field with a username to a lookup table? YOu have to first generate the table, then populate it, then add the field for the foreign key, thn populate it. No simple "go from schema a to schema b and just forget all the data in the dropped field". I also never want this to happen in a program start - it is an adminstrative change (backup, run migration) that has to be scheduled (due to possibly longer downtime while migrations run). All bu tthe most primitive / small data scenario are totally not covered by migrations. – TomTom Apr 02 '14 at 14:42
OK. This is how I would do it in a migration: 1. Alter the model to the new design. 2.Write the sql to populate the new design 3. Add the sql to the migration in between the create table instruction and the drop column instruction. Then use the migration to generate the change script – Colin Apr 02 '14 at 15:03
That is all nice and dandy. Now make the new table partitiones, set compression, add some filterd indices and we start talking about real world scenarios that I do pretty much in every migration. Note that all those thigns can not be natively expressed in a migration. So, I can as well write the migration scripts manually. – TomTom Apr 02 '14 at 15:05
I figure you can insert any kind of sql into migrations and so I let the migrations do the grunt work. I also like the fact I can roll backwards and forwards using migration files to any version of the schema I wish. It may not suit you but I like it. – Colin Apr 02 '14 at 15:22
The problem with this is that roll back is double work - everything is double work except in degenerate edge cases, and those are the only ones automatially supported. – TomTom Apr 02 '14 at 15:59
Usually it's just a case of scripting a drop and recreate to the previous version of the object. How do you rollback using your technique? – Colin Apr 02 '14 at 18:12
@Colin I do not. Why should I ever need a rollback to a database schema on an existing database? If I need to remove a feature that still is a "next version". This sounds so nice in theory, but in practice for production databases this is a total non issue. Never have seen the need to rollback a database schema in more than 25 years working with them. – TomTom Apr 02 '14 at 18:14
Ok. This is what I like about migrations. Migrations automatically create the sql to create or modify the schema required to match your model. Migrations can be customised by adding sql for views, triggers, indexes, table partitions, compression or anything you can write sql to do. Migrations provide rollback scripts that can also be customised if you wish. Migrations let you rollback or create from scratch a development database to any version that you wish, in seconds. Migrations automatically track the database version for you. And the migration files go into source control. It works for me – Colin Apr 02 '14 at 22:26
@TomTom While I share your opinion that migrations is not powerful enough to support all scenarios - and I'll definitely use SQL scripts when I need such a scenario -, the question was really about the `IndexAttribute` (I've reworded the question to stress this) and its `IsClustered` option, which seems to be unusable to me. I just want to know whether I'm missing something :) – Fabian Schmied Apr 03 '14 at 06:31

score 0 · Answer 5 · answered Jun 21 '16 at 17:40

I write here my solution if anyone still interested in this subject. Below code changes output of add-migration command.

public class CustomMigrationCodeGenerator : CSharpMigrationCodeGenerator
{
    protected override void Generate(CreateTableOperation createTableOperation, IndentedTextWriter writer)
    {
        if (createTableOperation.Columns.Any(x => x.Name == "Index") &&
             createTableOperation.Columns.Any(x => x.Name == "Id"))
        {
            if (createTableOperation.PrimaryKey != null)
            {
                createTableOperation.PrimaryKey.IsClustered = false;
            }
        }
        base.Generate(createTableOperation, writer);
    }
}

You can register this generator in migration configuration:

internal sealed class Configuration : DbMigrationsConfiguration<Ubrasoft.Freeman.WebApi.Db.MainDb>
{
    public Configuration()
    {
        AutomaticMigrationsEnabled = false;
        CodeGenerator = new CustomMigrationCodeGenerator();  
        SetSqlGenerator("System.Data.SqlClient", new CustomMigrationSqlGenerator());        
    }

    protected override void Seed(Ubrasoft.Freeman.WebApi.Db.MainDb context)
    {

    }
}

And here is the generated migration code:

public override void Up()
    {
        CreateTable(
            "Tenant.Tenant",
            c => new
                {
                    Id = c.Guid(nullable: false),
                    TenantNo = c.Byte(nullable: false),
                    Name = c.String(nullable: false, maxLength: 20),
                    Index = c.Int(nullable: false, identity: true),
                    CreatedDate = c.DateTime(nullable: false, precision: 0, storeType: "datetime2"),
                    UpdatedDate = c.DateTime(nullable: false, precision: 0, storeType: "datetime2"),
                    IsDeleted = c.Boolean(nullable: false),
                })
            .PrimaryKey(t => t.Id, clustered: false)
            .Index(t => t.Index, unique: true, clustered: true);

    }

Here is the article about custom MigrationCodeGenerator.

In Entity Framework 6.1 (not Core), how can I use the IndexAttribute to define a clustered index?

5 Answers5

Linked