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

Question

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?

what is the type of Point

Ghassen
– Ghassen

2018-12-23 16:48:32 +00:00
Commented Dec 23, 2018 at 16:48 — Ghassen
– Ghassen, Commented Dec 23, 2018 at 16:48
Sorry, it is a NetTopologySuite.Geometries Point.

Jeremy Armstrong
– Jeremy Armstrong

2018-12-23 19:00:58 +00:00
Commented Dec 23, 2018 at 19:00 — Jeremy Armstrong
– Jeremy Armstrong, Commented Dec 23, 2018 at 19:00

Derviş Kayımbaşıoğlu · Accepted Answer · 2018-12-23 17:12:40Z

1

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

answered Dec 23, 2018 at 17:12

Derviş Kayımbaşıoğlu

30.9k4 gold badges55 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jeremy Armstrong Over a year ago

I am not using EF migrations, but I still appreciate this answer. I was just hoping there was a way to describe the model using fluent options that did not involve migrations or magic strings... but the answer just may be that this is not built into the provider yet.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related