In SQL, a view is a virtual table based on the result-set of an SQL statement. They are typically used as read-only objects that are optimized to provide data for a given scenario. Entity Framework Core 5 can handle views and in this article, I’m going to show you how.

Adding a view

First of all, we need to add a view to the database. The best way to do so is to add a database migration with an appropriate SQL. Let’s start by adding a migration with EF Core global tool command:

dotnet ef migrations add vwGuestArrivals

This will generate a migration, that we can put our SQL into. Let’s see how it may look:

public partial class vwGuestArrivals : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        var sql = @"
            CREATE OR ALTER VIEW [dbo].[vwRoomsOccupied] AS
                SELECT r.[From], r.[To], ro.Number As RoomNumber, ro.Level, ro.WithBathroom
                FROM Reservations r
                JOIN Rooms ro ON r.RoomId = ro.Id";

        migrationBuilder.Sql(sql);
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.Sql(@"DROP VIEW vwRoomsOccupied");
    }
}

This view presents rooms that are occupied, where we can filter by date. This kind of data can be useful for example when planning maintenance.

Getting view data

In Entity Framework Core 5 views can be represented as a regular DbSet. In my case, to map all view’s columns, we need to create a RoomOcupied model, that would look like this:

[Keyless]
public class RoomOccupied
{
    public DateTime From { get; set; }

    public DateTime To { get; set; }

    public int RoomNumber { get; set; }

    public int Level { get; set; }

    public bool WithBathroom { get; set; }
}

Now we need to add a DbSet to my PrimeDbContext and we need to configure our model, so that RoomsOccupied will be executed against view. Let’s see how that can be accomplished:

public class PrimeDbContext : DbContext
{
    public PrimeDbContext(DbContextOptions<PrimeDbContext> options)
        : base(options)
    {
    }

    public virtual DbSet<RoomOccupied> RoomsOccupied { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder
            .Entity<RoomOccupied>(eb =>
            {
                eb.HasNoKey();
                eb.ToView("vwRoomsOccupied");
            });
    }
}

As you can see it is a normal DbSet, that can be queried as we wish. However, there is a tiny detail that makes this collection different. Notice that we configure RoomOccupied entity to have no key. This way we do not need to have a key in the result, but it also means that it would only be read-only model.

Currently, Entity Framework Core 5 does not support updating the view, while it is possible in SQL Server database. You can specify a view with a key, though. Just remember to remove HasNoKey in configuration and [Keyless] attribute in the entity.

Let’s use the code that we just wrote. To do so the easiest possible way, I just added a method to my ASP.NET Core API. Here is how it looks like:

[HttpGet("GetRoomsOccupied")]
public IActionResult GetGuestArrivalsFromView([FromQuery] string date)
{
    var parsedDate = DateTime.ParseExact(date, "dd-MM-yyyy", CultureInfo.InvariantCulture);
    var rooms = primeDbContext.RoomsOccupied.Where(r => r.From <= parsedDate && r.To >= parsedDate);

    return Ok(rooms);
}

Here I’m passing date in dd-MM-yyyy format and list all occupied rooms at the given date. Here is the result.

It uses a vwRoomsOccupied and executes a SQL query with all filters applied. We can take a look in SQL Server Profiler at the SQL that was executed.

Note that in this example we are using only dates with no time and it works fine. However, if you would like to compare dates with time as well, you would need to use a slightly different approach.

Summary

Entity Framework Core 5 can handle views flawlessly. You need to configure it in your DbContext class and specify that a specific entity will be mapped to the view. When that’s done, you can use a DbSet as you wish and all filters will be applied directly into generated SQL.

What’s more, you can handle adding or updating the view with EF Core migrations, which means that all work required can be done with EF Core.

All code mentioned here can be found on my GitHub, feel free to experiment with it.

Cheers!