Scaffold-DbContext for database views in EF Core 2.1 (Query Types)

Question

EF Core 2.1 has new feature - Query Types.

Some of the main usage scenarios for query types are:

• Serving as the return type for ad hoc FromSql() queries.
• Mapping to database views.
• Mapping to tables that do not have a primary key defined.
• Mapping to queries defined in the model.

I upgrade project to Core 2.1, but Scaffold-DbContext still does not generate database views. I have to use a special parameter or the Scaffold-DbContext does not support it?

Answer 1

Not supported in 2.1. See issue #1679.

Answer 2

Here's a hackish but working solution:

How to Scaffold Controllers with database views to EF Core 2.1

1. Create view in database.
2. Create a POCO with same structure as view.
3. Add a new Controller with POCO created in step#2 a. If key related error occurs, add a Key attribute on a column and then remove after scaffolding is completed.
4. A new property with DbSet<T> should have gotten added where T is the class created in step#2. Change DbSet to DbQuery.

5. In OnModelCreating method of DbContext, add following code:

   modelBuilder.Query<POCO from step#2>().ToView("Name of the view");
   

Source