Skip to main content

Introduction to MSSQL Server In-Memory OLTP with .NET 6 and EF Core

· One min read
Adnan Rafiq

SQL Server Enterprise offers scalable memory optimized relational tables. Yes, all data inside table will be loaded in memory. In-Memory tables has two flavors.

  • Durable - Offers ACID
  • Non-Durable

In-Memory table(s) are relational tables. Only difference is data is reterieved from memory than from disk. Which can significantly reduce your resource consumption (dramatically) & speed by 30X. Table & Row Size - see this

If you use Entity Framework to query your datbase. You do not need to do anything special about how you manipulate data. It works perfectly.

I wrote a sample app here - In-Memory SQL App

In EF Code First approach, you have to have specific IsMemoryOptimized.

protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Course>().IsMemoryOptimized();
        
        //modelBuilder.Entity<Course>().HasMany<CourseEnrollment>().WithOne(x => x.Course);

        modelBuilder.Entity<Person>().ToTable("Persons").HasDiscriminator<PersonRole>(nameof(Person.Role)).HasValue<Student>(PersonRole.Student).HasValue<Teacher>(PersonRole.Teacher);
        
        //modelBuilder.Entity<Person>().HasMany<CourseEnrollment>().WithOne(x => x.Student);

        modelBuilder.Entity<Person>().IsMemoryOptimized();
        modelBuilder.Entity<Student>().IsMemoryOptimized();
        modelBuilder.Entity<Teacher>().IsMemoryOptimized();

        modelBuilder.Entity<CourseEnrollment>().HasKey(x => new { x.CourseId, x.StudentId });
        
        modelBuilder.Entity<CourseEnrollment>().IsMemoryOptimized();

    }