How to create an index for a string property in Entity Framework 7

entity-framework-core visual-studio-2015

Question

I'm trying to create a code first model for Entity Framework 7. I'm using recently released Visual Studio 2015 Beta and the following versions of EntityFramework packages (snippet from my project.json file):

"EntityFramework.SqlServer": "7.0.0-beta1",
"EntityFramework.Commands": "7.0.0-beta1",

It looks like currently no data annotations are available and I'm using the OnModelCreating override and recently implemented (partly?) migrations to create my model.

For now, primary keys and one-to-one relations work as well as creating indices for integer types. For example:

builder.Entity<Article>(e =>
{
    e.Key(c => c.Id);
    e.OneToOne<Category>(c => c.Category);
    e.Index(c => c.Time).IsUnique(false);
});

This snippet results in the following migrations code to be generated:

migrationBuilder.CreateTable("Article",
            c => new
                {
                    Id = c.String(),
// ...
                    CategoryIdKey = c.Int(nullable: false),
                    Time = c.DateTime(nullable: false),
// ...
               })
            .PrimaryKey("PK_Article", t => t.Id)
            .UniqueConstraint("UC_Article_CategoryIdKey", t => t.CategoryIdKey);

        migrationBuilder.AddForeignKey("Category", "FK_Category_Article_CategoryId", new[] { "CategoryId" }, "Article", new[] { "CategoryIdKey" }, cascadeDelete: false);

        migrationBuilder.CreateIndex("Article", "IX_Article_Time", new[] { "Time" }, isUnique: false, isClustered: false);

But when I'm trying to add index to string property, the migration is generated, but when applied is rejected by SQL Server, apparently due to column type being nvarchar(MAX). It seems like the .Required().MaxLength(100) does not force limited string column type generation. And though there is a method to change the column type, I cannot seem to find the way to call it through ModelBuilder:

        builder.Entity<Keyword>(e =>
        {
            e.Key(c => c.Id);
            e.Property(c => c.Word).Required().MaxLength(100);
            e.Index(c => c.Word).IsUnique(true);
        });

Resulting migration:

        migrationBuilder.CreateTable("Keyword",
            c => new
                {
                    Id = c.Int(nullable: false, identity: true),
                    Word = c.String(nullable: false, maxLength: 100)
                })
            .PrimaryKey("PK_Keyword", t => t.Id);

        migrationBuilder.CreateIndex("Keyword", "IX_Keyword_Word", new[] { "Word" }, isUnique: true, isClustered: false);

Is there a way to create an index on string property in the beta version of EF7?

Accepted Answer

Unfortunately at this time (7.0.0-beta1), the max length and column type metadata is not honored when determining which column type to use. For now, you'll have to drop down to raw DDL in the migration.

// Add before CreateIndex
migrationBuilder.Sql("ALTER TABLE [Keyword] ALTER COLUMN [Word] nvarchar(4000)");



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why