CODEWITHSUNDEEP
c#.netentity-framework-core
Jeremy Armstrong
Jeremy Armstrong

Reputation: 945

Adding a index on a Geographic data type in SQL using EF Core 2.2

I am using EF Core 2.2 code first to create a table that has a NTS Point property in it. That works fine and creates a table in SQL with a 'Geographic' data type. Now I want to index that column from code first. I am just doing a basic:

entity.HasIndex(x => x.Point);

I am getting the following error:

Message: System.Data.SqlClient.SqlException : Column 'PointColumnName' in table 'TableName' is of a type that is invalid for use as a key column in an index or statistics.

Is there a way use code first to put an index on this column?

Upvotes: 0

Views: 390

Answers (1)

Derviş Kayımbaşıoğlu
Derviş Kayımbaşıoğlu

Reputation: 30605

The easiest way is to customize your migration script according to your need. For Instance:

migrationBuilder.Sql(
@"
    UPDATE Customer
    SET Name = FirstName + ' ' + LastName;
");

Or you can use more comprehensive way Custom Migration Operations

class MyMigrationsSqlGenerator : SqlServerMigrationsSqlGenerator
{
    public MyMigrationsSqlGenerator(
        MigrationsSqlGeneratorDependencies dependencies,
        IMigrationsAnnotationProvider migrationsAnnotations)
        : base(dependencies, migrationsAnnotations)
    {
    }

    protected override void Generate(
        MigrationOperation operation,
        IModel model,
        MigrationCommandListBuilder builder)
    {
        if (operation is CreateSpatialIndexOperation createSpatialIndexOperation)
        {
            Generate(createSpatialIndexOperation, builder);
        }
        else
        {
            base.Generate(operation, model, builder);
        }
    }

    private void Generate(
        CreateSpatialIndexOperation operation,
        MigrationCommandListBuilder builder)
    {
        var sqlHelper = Dependencies.SqlGenerationHelper;
        var stringMapping = Dependencies.TypeMappingSource.FindMapping(typeof(string));

        builder
            .Append("CREATE INDEX ")
            .Append(sqlHelper.DelimitIdentifier(operation.Name))
            ...
            .Append(stringMapping.GenerateSqlLiteral(...))
            .AppendLine(sqlHelper.StatementTerminator)
            .EndCommand();
    }
}

then use it like

 protected override void OnConfiguring(DbContextOptionsBuilder options)
   => options
        .UseSqlServer(connectionString)
        .ReplaceService<IMigrationsSqlGenerator, MyMigrationsSqlGenerator>();

Upvotes: 1

Related Questions