cover

With EF Core you can read data from SQL views, but can not manage the view lifecycle automatically like it does for tables. In this article I will describe an implementation where you can version SQL views in an anutomatic way for any SQL server that you are using. Examples provided here are for PostgreSQL

If you prefer the video version, check out these 2 videos:

Let’s assume in our case we do have some customers and each customer can has a parent. The requirenment is to retrieve the customers along with it’s parents. We will start by defining the customer hierarchy class that we will work with at the application level.

public class CustomerHierarchy
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int? ParentId { get; set; } 
    public int Level { get; set; }

    private CustomerHierarchy() {} // needed for EF Core to load
}

To load up the hierarchy we want to be as efficient as possible, hence we are creating the View that will load this. Here is a PostgreSQL version of it

Create a SQL file and name it view.customer.CustomerHierarchy.v1.sql and place it somewhere in your infrastructure folder structure. This will allow us to not have SQL in the C# code. Important set the SQL file as Embedded Resource. We need this to be able to load it later.

CREATE VIEW customer_with_parents AS
WITH RECURSIVE customer_hierarchy AS (
    -- Base case: Select the customer without any parents
    SELECT
        c.id,
        c.name,
        c.parent_id,
        1 AS level 
    FROM customers c
    WHERE c.parent_id IS NULL 
    UNION ALL

    SELECT
        c.id,
        c.name,
        c.parent_id,
        ch.level + 1 AS level 
    FROM customers c
    JOIN customer_hierarchy ch ON c.parent_id = ch.id 
)
SELECT * FROM customer_hierarchy;

It’s imposible to do in EF Core such a query, but we will still be reading using EF Core

For working with views, we need an interface that will bridge the gap between EF Core mapping and the SQL View itself. We will name it IQueryViewsince we are planning to reuse it for multiple mappings

    public interface IQueryView
    {
        static abstract string[] Versions { get; }
    }

Specific for the CustomerHierarchy we will map the entitiy and make the link between the entitiy and it’s view.

    public class CustomerHierarchyEntityTypeConfiguration : IEntityTypeConfiguration<CustomerHierarchy>, IQueryView
    {
        public const string Name = "customer_with_parents";
        public static string[] Versions => [SqlScriptResourceLoaderHelper.GetSqlContent("view.customer.CustomerHierarchy.v1.sql")];

        public void Configure(EntityTypeBuilder<CustomerHierarchy> e)
        {
            e.ToView(Name);

            e.HasKey(e => e.Id);
            e.Property(p => p.Name);
            e.Property(p => p.ParentId);
        }
    }

The SqlScriptResourceLoaderHelper.GetSqlContent function is just a helper one to be able to load content from an Embedded Resource file. For clarity here is the actual code:

internal static class SqlScriptResourceLoaderHelper
{
    public static string GetSqlContent(string resourceName)
    {
        var assembly = System.Reflection.Assembly.Load("Infrastructure");
        if (assembly == null)
            throw new InvalidOperationException("Failed to get executing assembly.");

        var fullResourceName = assembly
            .GetManifestResourceNames()
            .FirstOrDefault(name => name.EndsWith(resourceName));

        if (fullResourceName == null)
            throw new ArgumentException($"Resource '{resourceName}' not found.");

        var stream = assembly.GetManifestResourceStream(fullResourceName);
        if (stream == null)
            throw new Exception($"Failed to get resource stream for '{resourceName}'.");
        
        using (stream)
        using (var reader = new StreamReader(stream))
            return reader.ReadToEnd();
    }
}

Specific to Postgres, we need a way to execute creation/deletion of the view when a new migration is added. For this we will create a base migration class where we will put in the infrastructure project.

using Auctions.Application.Storage;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Migrations;

namespace Auctions.Infrastructure.Postgres;

public abstract class PostgresViewMigration<TConfig, TEntity> : Migration 
    where TConfig : IEntityTypeConfiguration<TEntity>, IQueryView
    where TEntity : class
{
    protected abstract int Version { get; }

    protected override void Up(MigrationBuilder migrationBuilder)
    {
        EnsureVersionInRange();
        var query = TConfig.Versions[Version - 1];
        migrationBuilder.Sql($"CREATE OR REPLACE VIEW {GetQualifiedViewName()} AS {query}");
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        EnsureVersionInRange();
        if (Version > 1)
        {
            var query = TConfig.Versions[Version - 2];
            migrationBuilder.Sql($"CREATE OR REPLACE VIEW {GetQualifiedViewName()} AS {query}");
            return;
        }

        migrationBuilder.Sql($"DROP VIEW {GetQualifiedViewName()}");
    }

    private void EnsureVersionInRange()
    {
        if (Version == 0)
            throw new ArgumentOutOfRangeException("Version is 1 indexed");
        if (Version > TConfig.Versions.Length)
            throw new ArgumentOutOfRangeException($"Version {Version} is greater than the number of versions defined in {typeof(TConfig).Name}");
    }

    private string GetQualifiedViewName()
    {
        var model = TargetModel.FindEntityType(typeof(TEntity));
        if (model == null)
            throw new InvalidOperationException($"Model {typeof(TEntity).Name} is not registered");

        var name = model.GetViewName();
        if (name == null)
            throw new InvalidOperationException($"View name is not defined for {typeof(TEntity).Name}");

        var schema = model.GetViewSchema();
        if (string.IsNullOrEmpty(schema))
            return Escape(name);

        return $"{Escape(schema)}.{Escape(name)}";
    }

    private static string Escape(string value) => $"\"{value}\"";
}

This class will be used to inherit the view migration, so let’s use it. For this we will create a migration the normal way by using Add-Migration AddCustomerHierarchyView command. We assume no other changes have been done to the model, this should create an empty migration:

namespace Auctions.Infrastructure.Migrations
{
    /// <inheritdoc />
    public partial class AddCustomerHierarchyView : Migration
    {
        protected override void Up(MigrationBuilder migrationBuilder)
        {

        }

        protected override void Down(MigrationBuilder migrationBuilder)
        {

        }
    }
}

However we need to change the signature and use the class we have created earlier, and this will look like this:

namespace Auctions.Infrastructure.Migrations
{
    /// <inheritdoc />
    public partial class AddCustomerHierarchyView : PostgresViewMigration<CustomerHierarchyEntityTypeConfiguration, CustomerHierarchy>
    {
        protected override int Version => 1;
    }
}

What it does is to define the migration AddCustomerHierarchyView as beeing the v1 of the view. Internally it will apply the SQL script defined as Embedded Resource.

When we issue update-database we can see that the Migration is applied and the SQL script view is executed as expected.

Conclusions

Most of the scenarions are supported with a few caveats:

  1. deletion of the view is not supported - in case you realize later on you don’t need the view anymore
  2. no materialized views support

However these are also doable but let’s consider for another article.