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.
Read more about SQL Server views at the Microsoft Documentation site.
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.