Working with views in Entity Framework Core 5

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!

 

14 thoughts on “Working with views in Entity Framework Core 5

  1. Greg

    Thanks for this! Nice explanation of the new way views are handled. Just what I was looking for, and I just bought your book 🙂

    Reply
  2. Martin

    Great! There’s a way to create the view automatically when creating the database from code first? That would be awesome

    Reply
    1. Michał Białecki Post author

      Hi @Martin,
      Unfortunately, there is no way to do it in EF Core 5. Maybe in the next release!

      Reply
  3. Javad

    Thanks for this!
    I have a problem
    I am using Database First, after updating DbContext by scaffold-dbcontext.
    RoomsOccupied property removed from DbContext, what is the solution to this problem?

    Reply
    1. Michał Białecki Post author

      Hey Javad. Sadly scaffolding has its limitations. In EF Core 5 it only works for tables and it will override existing changes. As RoomsOccupied is a view, it will be removed from DbContext.

      Reply
      1. javad

        Thank you for your answer
        I can create another partial class called DbContext and put all the views in it, so that the view properties are not removed after the Dbcontext update?

        Reply
        1. Michał Białecki Post author

          Yeah, it’s a good idea. On the other hand, scaffolding DbContext is not something you would do on a daily basis. I would rather do it once and add all tables that you need. And keep tracking in Git, to be safe that you can roll back at any time.

          Reply
  4. Piotr

    Hi Michał

    a quick question have you tried to use View in the select with include(providing you added navigation property)?
    eg:
    var x = dbcontext.Set().Include(m=>m.navigationPropertyToMyView)

    Reply
    1. Michał Białecki Post author

      Sorry, haven’t tried that 🙂
      That would mean, that you would need to have relationships in a view, but I don’t think it’s the best approach. A view is already something you would like to present as a materialized object from DB.

      Reply
  5. Scott Trapp

    Great post! I know it’s been a while since you wrote this. Do you know if Views are tracked with EFCore? Since you cannot update the records retreieved views with EFCore, I assume you they are not, but I cannot find a definitive answer.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *