Scaffold-DbContext SQL Views ? ASP NET CORE 3.1

Question

I need your help. I read on the web that there has never been a proper way to import views from sql into our asp.net core project. Do you know if in version 3.1 you can do ? If so, how? For tables I use the "scaffold-DbContext" command. Thank you very much!

Answer 1

It is possible to scaffold a view. Just use -Tables the way you would to scaffold a table, only use the name of your view. E.g., If the name of your view is ‘vw_inventory’, then run this command in the Package Manager Console (substituting your own information for "My..."):

PM> Scaffold-DbContext "Server=MyServer;Database=MyDatabase;user id=MyUserId;password=MyPassword" Microsoft.EntityFrameworkCore.SqlServer -OutputDir Temp -Tables vw_inventory

This command will create a model file and context file in the Temp directory of your project. You can move the model file into your models directory (remember to change the namespace name). You can copy what you need from the context file and paste it into the appropriate existing context file in your project.

Note: If you want to use your view in an integration test using a local db, you'll need to create the view as part of your db setup. If you’re going to use the view in more than one test, make sure to add a check for existence of the view. In this case, since the SQL ‘Create View’ statement is required to be the only statement in the batch, you’ll need to run the create view as dynamic Sql within the existence check statement. Alternatively you could run separate ‘if exists drop view…’, then ‘create view’ statements, but if multiple tests are running concurrently, you don’t want the view to be dropped if another test is using it. Example:

  void setupDb() {
    ...
    SomeDb.Command(db => db.Database.ExecuteSqlRaw(CreateInventoryView()));
    ...
  }
  public string CreateInventoryView() => @"
  IF OBJECT_ID('[dbo].[vw_inventory]') IS NULL
    BEGIN EXEC('CREATE VIEW [dbo].[vw_inventory] AS
       SELECT ...')
    END";

This is a helpful link. It describes adding the code sections by hand (as Nouman mentioned) instead of scaffolding them: https://learn.microsoft.com/en-us/ef/core/modeling/keyless-entity-types?tabs=fluent-api

Answer 2

Although you cannot use scaffold-DbContext for database view but you can still use SQL View in your .Net Core project.

Suppose you have the following SQL View

CREATE VIEW [dbo].[v_MyTableResult] AS

SELECT 
    SELECT Id, Name FROM MyTable

GO

Create a new Model class based on the result set of SQL View.

public class MyTableModel
{
    public int Id { get; set; }
    public string Name { get; set; }
}

In the database context class, introduce property for the Model.

public virtual DbSet<MyTableModel> MyTableResults { get; set; }

In OnModelCreating method, configure model properties

    modelBuilder.Entity<MyTableModel>(entity =>
    {
        entity.HasNoKey();

        entity.Property(e => e.Id)
            .HasColumnName("Id");

        entity.Property(e => e.Name)
            .HasColumnName("Name");
    });

Finally, in the method where you need results from the view, call FromSqlRaw on DbSet property

    var myTableResults =
        this.MyDbContext.MyTableResults.FromSqlRaw(
            "SELECT * FROM dbo.v_MyTableResult").ToList();