3

I found this interesting question on stackoverflow about mapping a view and working with it in EF Core.
According to its answer, with previous EF Core versions wasn't possible to automatically map views.

But what now? According to the Entity Framework 2.1 Roadmap,

An EF Core model can now include query types. Unlike entity types,
query types do not have keys defined on them and cannot be inserted,
deleted or updated (i.e. they are read-only), but they can be returned
directly by queries. Some of the usage scenarios for query types are:

  • Mapping to views without primary keys
  • (...)

So the question is: is it possible to automatically scaffold a db context and map its views (like we do for a normal scaffold-dbcontext with an existing database)? If yes, does anyone know how?

Or the only way is still creating them manually as Sampath Kaliyamurthy said in this answer for a previous EF Core version?

Deadpool
  • 1,031
  • 3
  • 19
  • 35
  • 1
    Have you tried to do any of these things? – Brad Jun 13 '18 at 10:19
  • @Brad yes, but views aren't mapped automatically. I've got a workaround which consists in renaming the view, create a table from that view, scaffold the DB and then delete the table and rename the view but I wonder if there was something more "immediate" and "2.1-native" (I will add this workaround to the question as an edit) – Deadpool Jun 13 '18 at 10:22
  • 1
    For sure EF Core 2.1 adds [Query Types](https://learn.microsoft.com/en-us/ef/core/modeling/query-types) which can be mapped to a db view using `ToView` fluent API. I don't know if there is scaffolding support though. – Ivan Stoev Jun 14 '18 at 09:48
  • 1
    @IvanStoev Thanks for the info, didn't know that! I don't think scaffolding supports it yet, I will give a try as soon as possible and I'll tell you! – Deadpool Jun 15 '18 at 06:54

2 Answers2

4

WORKAROUND SOLUTION:

I found a workaround which consists in:

  1. Rename the view (e.g. from "MyView", to "My_View")
  2. Create a table from that view with a select * into table from view query, and name it like the view initial name (eg. the table will be named "MyView")
  3. Set in the table a primary Key
  4. Scaffold the DB, so you get all the classes and the correct mapping
  5. Remove from the mapping descriptor the HasKey property
  6. Delete the table from the Database and rename the view with its initial name (so, from "My_View" to "MyView" again)

This will make the view works like a normal scaffolded table, you will get all the correct mappings and everything.
The only difference of course is that you won't be able to insert data in it or editing it.
But it's ok since if you want to work with views, you know this from the beginning!

But

CONSIDER THIS QUESTION STILL OPENED...

...since despite of this workaround, I still wonder if there's something more "immediate" and "2.1-native", introduced with ef 2.1 as they said in the roadmap.

So any help and/or answer will be such as appreciate!

Deadpool
  • 1,031
  • 3
  • 19
  • 35
  • 3
    This is a LOT of work if you have multiple views! Not to mention you'd have to go through the this process every time you make changes to the database and want to scaffold the database again. The workaround I've been using is a partial class for the views. Still not ideal, but requires little work after scaffolding again. I'm looking for a better solution myself. – Naner Apr 02 '19 at 14:49
  • 1
    Hi @Naner, thanks for your comment! Please, feel free to leave an answer for this question explaining the solution/workaround you will find most suitable, due to the increasing number of views in this question I think it will be useful for a lot of people – Deadpool Apr 02 '19 at 16:28
4

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 instead of scaffolding them: https://learn.microsoft.com/en-us/ef/core/modeling/keyless-entity-types?tabs=fluent-api

NLandis
  • 199
  • 3
  • 8