How To Add A View To An Entity Framework Core DbContext

Working with databases is a fun yet challenging task. As our application’s mature, we see opportunities to optimize our codebase along with query performance. Views are a great way to achieve both goals. With Entity Framework Core 5 (EF Core), it’s never been more straightforward to take advantage of all the tools our database has to offer.

In this post, we’ll walk through adding an empty database migration, defining a complex SQLite view, and then modifying our EF Core DbContext to give us access to our view. Let’s get started!

What’s A SQL View

For folks who aren’t aware, a view is a virtual table based on the result-set of a predefined query. Views allow us to encapsulate an otherwise complex query into a logical entity in our database. Taking advantage of views can help simplify queries and may help improve database performance. Each database engine will implement views differently, so consult the documentation of the underlying provider.

The Working Data Model

We’ll be working with a previous data model around Entertainment. The DbContext defines tables like Productions and Ratings, but otherwise is not very important to this post. For clarity, here are the two tables defined in C#. This post assumes you have a basic understanding of EF Core data contexts and how they map to a database.

public abstract class Production
{
    public int Id { get; set; }
    public string Name { get; set; }
    public DateTime Release { get; set; }

    public List<Character> Characters { get; set; } = new List<Character>();
    public List<Rating> Ratings { get; set; } = new List<Rating>();
}

public class Rating
{
    public int Id { get; set; }
    public int ProductionId { get; set; }
    public Production Production { get; set; }
    public string Source { get; set; }
    public int Stars { get; set; }
}

Our goal is to create a SQL View that can reduce the complexity of the following LINQ statement.

var highestRated = database
    .Productions
    .Select(x => new
    {
        id = x.Id,
        name = x.Name,
        avg = x.Ratings.Average(r => r.Stars)
    })
    .OrderByDescending(x => x.avg);

Adding An Empty Migration

We first need to create a migration to add our view definition to our database. We can add an empty migration by asking the Entity Framework Core CLI to add a new migration, as long as there are no outstanding model changes yet to be applied. I’ve found that any seed data in the DbContext makes it difficult to create an empty migration.

 dotnet ef migrations add ProductionRatingAveragesView

Once EF Core generates the additional migration, we will have the following code generated in our Migrations folder. The namespace will differ based on your project.

using Microsoft.EntityFrameworkCore.Migrations;

namespace Entertainment.Migrations
{
    public partial class ProductionRatingAveragesView : Migration
    {
        protected override void Up(MigrationBuilder migrationBuilder)
        {

        }

        protected override void Down(MigrationBuilder migrationBuilder)
        {

        }
    }
}

The SQLite View Migration

We’re working with an entertainment domain with references to Productions and Ratings. Instead of writing complex LINQ expressions or raw SQL statements every time we need rating averages, let us formalize the idea of average ratings. In this case, we’ll be using SQLite views and creating a new view named ProductionRatingAverages.

create view ProductionRatingAverages as
select p.Id, p.Name, avg(r.Stars) as AverageStarRating
from Productions p inner join Ratings r on p.Id = R.ProductionId
group by p.Id, p.Name;

We’ll want to add this SQL creation script to our empty migration file of ProductionRatingAveragesView by modifying the Up. We also want to remove the view if we decide to revert the migration; we can ensure this behavior by altering the Down method.

using Microsoft.EntityFrameworkCore.Migrations;

namespace Entertainment.Migrations
{
    public partial class ProductionRatingAveragesView : Migration
    {
        protected override void Up(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.Sql(@"
create view ProductionRatingAverages as
select p.Id, p.Name, avg(r.Stars) as AverageStarRating
from Productions p inner join Ratings r on p.Id = R.ProductionId
group by p.Id, p.Name;
");
        }

        protected override void Down(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.Sql(@"
drop view ProductionRatingAverages;
");
        }
    }
}

I prefer to use the @ approach when adding SQL and newline characters to C# files. I find the format is much easier to read, even though it might look strange. I would also recommend against storing SQL in external files when working with EF Core. Developers may be tempted to change those files rather than creating a new migration. Changing migrations will only lead to pain. Remember, migrations are temporal, and once applied should be locked.

The next step is to add our new ProductionAverage model to our DbContext, which if you’re familiar for any EF Core user.

Setup Our DbContext Model Definition

Since we have our view already created, we can create a model that mimics our result set’s columns. We have Id, Name, and AverageStarRating values.

public class ProductionRatingAverage
{
    public int Id { get; set; }
    public string Name { get; set; }
    public double AverageStarRating { get; set; }
}

Next, let’s add a DbSet to our DbContext.

public DbSet<ProductionRatingAverage> ProductionRatingAverages { get; set; }

Finally, let’s map our entity to our view within the DbContext.OnModelCreating method.

modelBuilder
    .Entity<ProductionRatingAverage>()
    .ToView(nameof(ProductionRatingAverages))
    .HasKey(t => t.Id);

We can use nameof because our DbSet property name matches our view name, but this method also accepts any arbitrary string value. The support for string values can help when our database naming conventions diverge from our C# naming conventions.

Calling Our View From C#

Finally, we can use our newly defined view like we would any other DbSet.

var database = new EntertainmentDbContext();

var averages = 
    await database
        .ProductionRatingAverages
        .ToListAsync();

Be mindful that this collection is a view, and any attempt to update rows will likely fail. Some SQL databases support read/write views, but only when we define a view with one table reference.

Conclusion

With EF Core 5, we can introduce views in our DbContext and track the evolution of our views using the built-in database migration mechanism. Models behave as they would when mapped directly to a table to take advantage of default mapping conventions. Within the OnModelCreating method, we can define a view’s primary keys, column mappings, and more if naming conventions differ. Finally, for developers, the experience is what it always has been, LINQ expressions.

I hope you found this post useful, and please leave a comment regarding how SQL Views have helped you solve a difficult problem. Also, check out my other Entity Framework posts for awesome guides and tutorials.