Advanced Usage of MSSQL Indexed Views in EF Core

In the world of Microsoft SQL Server, views are a powerful tool for simplifying complex data queries. Simple views offer no real performance gain but are often used to implement simple database queries (I still would prefer App implementations over Database implementations for simple views). But indexed views offer a real performance gain.

Indexed views, also known as materialized views, are special database objects that store the results of a view persistently in the database. This persistence is updated as soon as a write access occurs that affects data records contained in the view. This mechanism makes it possible to save the results of a complex query and retrieve them as required without having to execute the query again. This is a real performance boost, especially for applications that have an enormously high read rate.

Simple Views in EF Core

Unfortunately, EF Core only offers very limited support for views in MSSQL; ultimately only basic features.

For this purpose, a so-called keyless entity is created - here as an example a user and their number of forum posts - which are then mapped to a view in the configuration.

public class UserForumPostCount
{
    public int UserId { get; set; }
    public int TotalPostCount { get; set; }
}

public class UserForumPostCountConfig : IEntityTypeConfiguration<UserForumPostCount>
{
    public void Configure(EntityTypeBuilder<UserForumPostCount> builder)
    {
        builder.HasNoKey();
        
        builder.ToView("UserForumPostCountView");
    }
}

The view - this is not a materialized view - itself is created within a migration using an SQL command.

CREATE VIEW UserForumPostCountView
AS
    SELECT 
        UserId,
        COUNT(*) AS TotalPostCount
    FROM 
        ForumPosts
    GROUP BY 
        UserId;

Finally, only the view needs to be registered in the DbContext.

public class MyDbContext : DbContext
{
    // ....

    public DbSet<UserForumPostCount> UserForumPostCountViews
        => Set<UserForumPostCount>();

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        // ...
        modelBuilder.ApplyConfiguration(new UserForumPostCountConfig());
    }

Disadvantages

This way of declaration has some disadvantages, the two biggest ones are:

• Views in EF Core do not support navigation properties; other entities can therefore not be accessed - although this is supported in all common relational databases. EF Core refuses to do this with corresponding error messages like The navigation cannot be added because it targets the keyless entity type 'Document'. Navigations can only target entity types with keys. (see See 'Entity Framework Core Keyless Navigation Problem' on stackoverflow).
• Depending on the database system, there are additional functionalities that cannot be used by simply declaring the EF Core view (ToView). For indexed views, there is the so-called NOEXPAND hint, which is there to ensure that the SQL server really reads from the persisted view and does not re-execute the view. This hint cannot be set via EF Core ToView.

Advanced Usage - Indexed Views

Indexed Views are so powerful that every means is right to use them. One workaround is to declare the view as a normal entity and abuse the functionality of EF Core here - at your own risk, of course.

First, the view is created via the migration.

CREATE VIEW UserForumPostCountView WITH SCHEMABINDING AS
SELECT 
    UserId,
    COUNT_BIG (*) AS TotalPostCount
FROM 
    dbo.ForumPosts
GROUP BY 
    UserId;

CREATE UNIQUE CLUSTERED INDEX IX_UserForumPostCountView
    ON UserForumPostCountView (UserId);

The special features here are:

• Some SQL functions are not supported in indexed views.
• COUNT_BIG must be used, which means we no longer use int in the entity but must use long
• The whole thing is done with the help of schema binding
• A clustered index is created

In our entity configuration we can now not use ToView, but use our own SQL command.

public class UserForumPostCount
{
    public int UserId { get; set; }
    public long TotalPostCount { get; set; }
}

public class UserForumPostCountConfig : IEntityTypeConfiguration<UserForumPostCount>
{
    public void Configure(EntityTypeBuilder<UserForumPostCount> builder)
    {
        builder.HasKey(x => x.UserId);

        builder.ToSqlQuery(
            /* language = sql*
             @"""
                 SELECT 
                    UserId,
                    TotalPostCount
                 FROM 
                    UserForumPostCountView WITH (NOEXPAND);
             """;
    }
}

This is absolutely necessary in order to be able to use the SQL hint NoExpand, which ensures that the query is never performed dynamically but always against the already persisted result - and thus brings the performance gain.

Side Effects

A nice side effect is that this type of view declaration now also allows navigation properties in our view entity:

public class UserForumPostCount
{
    public int UserId { get; set; }
    public virtual UserEntity User { get; set; } = null!;

    public long TotalPostCount { get; set; }
}

public class UserForumPostCountConfig : IEntityTypeConfiguration<UserForumPostCount>
{
    public void Configure(EntityTypeBuilder<UserForumPostCount> builder)
    {
        builder.HasKey(x => x.UserId);
        
        builder.ToSqlQuery(
            /* language = sql*
             @"""
                 SELECT 
                    UserId,
                    TotalPostCount
                 FROM 
                    UserForumPostCountView WITH (NOEXPAND);
             """;

         builder.HasOne(x => x.User)
            .WithOne()
            .HasForeignKey(x => x.UserId);
        }
    }

Effect on the migration

Due to the workaround of the declaration of the view, EF Core does not know that it is a view. EF Core now thinks that this is a separate entity and generates corresponding CREATE commands in the migration.

These must all be adapted manually:

• Manual view creation
• Manual index creation
• Adapt/remove EF Core migration commands

Conclusion

EF Core can be used wonderfully with indexed views in MSSQL; even navigation properties work without a wall, which I have been using successfully for years even in very large applications. However, everything is done at your own risk with the knowledge that this is really an unsupported workaround - for which we may not get official support even in 200 years.

It is to be used with caution; you need advanced experience, you need to know what you are doing - but you will be rewarded with a huge performance boost.