In Entity Framework 6.1 (not Core), how can I use

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.

Answer 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.

Answer 2

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.

Answer 3

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

Answer 4

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.

Answer 5

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