How to add Sql Server View in existing Database Context in EntityFramework Core

user_11307592 0
Viewed 875

Good day every one I have created an Asp.Net core MVC application and have created the DB context successfully using Database First approach. Now I have created a SQL Server View. I want to add this View (only view) to the existing DBContext now. Please guide me how to scaffold only this view into DB context so that I could have model class for this.

"connectionString": "Data Source=TestServer;Initial Catalog=TestDB;user=TestUser;password=TestPassword;"

and already esisted Model classes are in Models folder.

I will appreciate your quick reply.

asp.net-core
2 Answers

Now I have created a SQL Server View. I want to add this View (only view) to the existing DBContext now. Please guide me how to scaffold only this view into DB context so that I could have model class for this.

You can try to specify the name of your view in -Tables parameter like below, which would help create entity and map to that view.

Scaffold-DbContext "connection_string_here" Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models -Tables "MyView1" -ContextDir Context -Context MyDbContext -Force

To scaffold types for view(s), you can refer to the following docs:

user_67516340

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