Tag Archives: view

Widoki w Entity Framework Core 5

Widok w kontekście baz danych jest wirtualną tabelą opartą na zestawie wyników uzyskanych przez wykonanie zapytaniem SQL. Są one zwykle używane jako obiekty tylko do odczytu, które są zoptymalizowane pod kątem dostarczania danych dla danego scenariusza. Entity Framework Core 5 obsługuje widoki, a w tym artykule pokażę, jak to działa.

Dodanie widoku

Pierwszą rzecz jaką musimy zrobić, to dodać widok do bazy danych. Najlepszym sposobem na to jest dodanie migracji bazy danych z odpowiednim kodem SQL. Zacznijmy od dodania migracji za pomocą polecenia narzędzia globalnego EF Core:

dotnet ef migrations add vwGuestArrivals

To polecenie wygeneruje migrację, w której możemy umieścić nasz SQL. Zobaczmy, jak to może wyglądać:

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");
    }
}

Ten widok przedstawia zajęte pokoje, które możemy filtrować według daty. Tego rodzaju dane mogą być przydatne na przykład podczas planowania konserwacji i prac remontowych.

Pobieranie danych z widoku

W Entity Framework Core 5 widoki mogą być reprezentowane jako zwykła kolekcja DbSet. W moim przypadku, aby zmapować wszystkie kolumny widoku, musimy stworzyć model RoomOcupied, który wygląda tak:

[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; }
}

Teraz musimy dodać DbSet do mojego PrimeDbContext i musimy skonfigurować nasz model, aby RoomsOccupied był wykonywany na konkretnym widoku. Zobaczmy, jak można to osiągnąć:

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");
            });
    }
}

Jak widać, jest to normalna kolekcja DbSet, na który można filtrować jak jak chcemy. Jest jednak drobiazg, który wyróżnia tę kolekcję. Zwróć uwagę, że konfigurujemy encję RoomOccupied tak, aby nie miała klucza. W ten sposób nie musimy mieć klucza w wyniku, ale oznacza to również, że byłby to model tylko do odczytu.

Obecnie Entity Framework Core 5 nie obsługuje aktualizowania widoku, chociaż jest to możliwe w bazie danych SQL Server. Możesz jednak zdefiniować widok, który posiada klucz. Pamiętaj tylko, aby usunąć HasNoKey w konfiguracji i atrybut [Keyless] w encji.

Użyjmy kodu, który właśnie napisaliśmy. Aby to zrobić w najprostszy możliwy sposób, dodałem metodę do mojego interfejsu API ASP.NET Core. Oto jak to wygląda:

[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);
}

Tutaj podaję datę w formacie dd-MM-rrrr i pytam o wszystkie pokoje zajęte w tym dniu. Oto wynik.

Używam vwRoomsOccupied i wykonuje zapytanie SQL z zastosowanymi wszystkimi filtrami. W SQL Server Profiler możemy spojrzeć na SQL, który został wykonany.

Zauważ, że w tym przykładzie używamy tylko dat bez czasu i wszystko działa dobrze. Jeśli jednak chcesz porównać również daty z czasem, musisz zastosować nieco inne podejście.

Podsumowanie

Entity Framework Core 5 bezbłędnie obsługuje widoki. Musisz go skonfigurować w swojej klasie DbContext i określić, że określona jednostka zostanie zamapowana na widok. Gdy to zrobisz, możesz użyć DbSet, jak chcesz, a wszystkie filtry zostaną zastosowane bezpośrednio do wygenerowanego kodu SQL.

Co więcej, możesz obsłużyć dodawanie lub aktualizowanie widoku za pomocą migracji EF Core, co oznacza, że wszystkie wymagane prace można wykonać za pomocą EF Core.

Cały pokazany tutaj kod można znaleźć na moim GitHubie, możesz spokojnie z nim eksperymentować, zachętam!

 

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!