SQL Server 2014 Memory Optimized Table with Entity Framework 6.1 CodeFirst

Question

My application uses two lookup tables I want to be Memory Optimized Tables.

There seems to be no Attribute yet to declare this;

Is there a possibility to hook into the table creation process and look for an custom attribute and modify the create table command?

I use Entity Framework 6.1 with Code First.

I seek a way to make it declarative, so if e.g. EF 6.2 or newer version supports it official, i can drop my hack.

Answer 1

I'd replace the CreateTable with the specific sql you want to optimise your database, called in the Up migration using Sql("create table etc..").

This is restricted to Sql Server 2014 and further restricted by other requirements for memory optimisation. Not worth the candle trying to do it with attributes within EF.

Answer 2

Maybe you can create an Entity Framework DB initializer that drop and recreate the table?

Something like (pseudo code!):

    public class MyDbInitializer : CreateDatabaseIfNotExists<MyDbContext>
    {
        protected override void Seed(MyDbContext context)
        {
            context.Database.ExecuteSqlCommand("DROP TABLE MyTable");
            context.Database.ExecuteSqlCommand("CREATE TABLE MyTable (bla bla bla)  WITH (MEMORY_OPTIMIZED=ON)");
        }
    }

I can't test this feature because I don't have SQL 2014 but I have used the Seed method to alter my database in many situations so it should work.

You can then assign your database initializer using:

System.Data.Entity.Database.SetInitializer(new MyDbInitializer());