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

Question

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.

Hi @ShakilAhmed, any updates about this case?

Fei Han
– Fei Han

2021-03-10 07:07:18 +00:00
Commented Mar 10, 2021 at 7:07 — Fei Han
– Fei Han, Commented Mar 10, 2021 at 7:07

Fei Han · Accepted Answer · 2021-03-08 06:05:48Z

1

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:

answered Mar 8, 2021 at 6:05

Fei Han

28.1k1 gold badge35 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Seddik MoeZ · Accepted Answer · 2021-03-05 18:18:20Z

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

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related