How to create a spatial index using the EF 6.1 API

Question

Well, the question is clear enough. Is it possible to create spatial indexes using the Fluity API Entity Framework 6.1?

Answer 1

The short answer. No, it is not. I saw that this refers to all blogs and no specific implementation examples were found. This is apparently due to the fact that spatial indexes are filtered indexes that are not supported in the Entity Framework.

To support my answer, I created a POC console application with the latest version of Entity Framework (6.1). I took the following steps

• Created a model that had a property like DbGeography
• Enabled Auto Migrations

• Ran Update-Database - protection against migration was launched with the addition of an index. The following was used in the index:

  modelBuilder.Entity<LocationEntity>().Property(t => t.Coordinates).HasColumnAnnotation("Index", new IndexAnnotation(new IndexAttribute("ix_locationentity_coordinates")));

No indexes were created, but the application did not crash either. I could try permutations on this, but my example seems to be consistent with an entity structure convention: Free level official documentation

Answer 2

The only way I know this is through "normal" migration. In EF6, I add a migration (in the example below it is called "V1"), which results in a new migration with empty methods Up () and Down (). You can then add custom SQL commands to these methods before starting the update database to place them in a “normal” migration stream.

You can modify the existing migration to add these features, but I prefer that my automatic forests be separated from my custom ones.

 public partial class V1 : DbMigration { public override void Up() { Sql("CREATE SPATIAL INDEX [IX_UserProfileAddresses_Location] ON [dbo].[UserProfileAddresses](Location)"); } public override void Down() { Sql("DROP INDEX [IX_UserProfileAddresses_Location] ON [dbo].[UserProfileAddresses]"); } } 

Not an ideal method, but not so bad as it executes the “normal” migration pattern for EF.