Efcore: RevEng: Support Views

[lajones]

Note: This issue is tracking a specific aspect of using EF Core with database views. See #827 for an overview of the areas where EF Core interacts with database views. The completion of this feature does not mean that every aspect of database view support has been implemented.

---

See also #5719, which is about support for tables without keys.

Part of #830. At the moment we generate code only from tables, not views. Need to include views but may have to consider what we want to use as the primary key.

[roji]

Just a note for whenever this gets tackled - PostgreSQL views are updatable in some cases, see the docs for when this is valid. I'm not sure if this kind of thing is supported in other databases, just want to make sure views in EFCore aren't approached as necessarily read-only entities.

[garethsuarez]

any chance that view mapping (which would also require stored proc mapping for insert/update/delete) could be a 2.x feature, or are we likely to have to wait for a 3.0 release?

[divega]

@garethsuarez the straightforward answer is that we don't know yet. We haven't even completed the planning for the next version after 2.0. We will take into account votes as well our best effort to understand the usefulness and the cost of each feature.

[tdjastrzebski]

Views can be updatable but the framework will never be able to 100% correctly guess the pk.
Hence, it may make more sense to generate (scaffold) views as regular entities; read-only until pk is defined. Does not it sound like a reasonable deal? Why a separate ViewType? Just to indicate it is read-only while with PK added it may not be? Keep it simple. Not to mention that sql server views (tables too) really do not require pk to be updatable but ok, it is not a good practice.

[roji]

@tdjastrzebski the framework may not need to "guess" the PK - this information might be available in from the database itself, at least in some cases. I admit I'm not sure exactly what is possible and what PK information is exposed by PostgreSQL, but it's important to keep an open mind here with regards to what different databases support (rather than limiting things upfront).

When the times comes to spec this feature out I'd be happy to investigate PostgreSQL capabilities more.

[tdjastrzebski]

@roji I absolutely agree and that was my point. The framework, depending on the provider, may or may not be able to guess the PK. Even more, in some cases (providers) the difference between a view and a table may be blurry or simply table/view concept may not exist at all.

Therefore, I strongly advocate for implementing no special treatment for views.

It is an Entity Framework, not just (some) SQL Entity framework. Why not to simply retrieve views like tables but as read-only with no PK, until PK is manually defined.
In my developer life I have came across several data access frameworks, most of them overly complicated with that respect. Keep it simple please.

[ErikEJ]

@tdjastrzebski
no special treatment for views != Why not to simply retrieve views like tables but read-only with no PK, until PK is manually defined.

[roji]

I must say I tend to agree that technically the table/view distinction isn't necessary interesting or important. However, we should keep in mind that views are a standard relational database concept, so for users to understand and quickly use EF Core we should probably retain the terminology.

In other words, documentation and possibly some API sugar should probably contain "view", even if behind the scene it's just a table without a primary key (and which is therefore also read-only).

[tdjastrzebski]

@roji Again, I could not agree more. Documentation should use commonly understood terms.
However, I would be careful not to (over) design EF Core mainly towards (some) SQL server in mind.
Even for MS SQL Server views supporting "instead of" triggers the table/view distinction from practical perspective does not make much sense.

@ErikEJ: I am unclear about what you mean by this predicate. In my opinion views support could be simply enabled with "as-is" 2.0 functionality. Albeit I must admit, I have not followed EF Core development closely and I am not aware of all the constraints/dependencies so I could be wrong.
What I know, however, is that by 'fooling' EF Core to generate entities for my views (see #9854 for details) I get just what I need.

[tdjastrzebski]

And just a thought: if you really need to distinguish tables and views do so using marker interface only - the harmless way.

[bricelam]

@tdjastrzebski ViewType is just a fancy word we've been using to describe read-only entity types without a key. At the API level, they'll probably end up looking just like any other entity type.

[bricelam]

I imagine RevEng will generate a normal entity type with an additional configuration like .IsReadOnly() and no .HasKey() call.

[tdjastrzebski]

@bricelam Thank you for the explanation. I think ReadOnlyDbSet<T> may be another option.

[bricelam]

Oh I like that--it could hide .Add() and .Remove(). I'm sure @divega and @anpete have also considered it, but I'll cc them just in case.

[anpete]

Yep, working name is DbView<T> 😄

[tdjastrzebski]

@anpete, @bricelam Thanks, what matters is that I can continue rev-eng views as described in #9854 and I will not face major refactoring when views are better supported. Views are essential for some of the projects.

[fulte004]

Just a thought: the EntityFramework Reverse POCO Generator full .NET extension's t4 generation of views requires that a recognized ID column that is an int or a bigint (long) and is NOT NULL. The template generator allows a different ID naming convention to be defined within the .tt file. If it discovers such a column, it proceeds with the generation. If not, the view is skipped. Scaffold-DbContext generation could default to a NOT NULL integer column named "Id" unless an IDesignTimeServices class defines some other naming convention that the scaffold can look for. Plausible?

[we4sz]

@anpete What's the status on this, is it planned in any coming release 😄?

[ajcvickers]

@we4sz This issue is in the Backlog milestone. This means that it is not going to happen for the 2.1 release. We will re-assess the backlog following the 2.1 release and consider this item at that time. However, keep in mind that there are many other high priority features with which it will be competing for resources.

[Alleno]

Is there no way to specify a fake primary key column in the scaffold-dbcontext command? I was able to manually write out a data object for a view, including an annotated key, and it seems to be working fine, but it would be nice to not have to hand copy all the columns by specifying a key column in the CLI command

[fulte004]

You can fake an identity column in a view by adding the following to the top of your view's SELECT statement:
SELECT ISNULL(ROW_NUMBER() OVER (ORDER BY [dbo].[SomeTable].[SomeOrderbyColumn]), 0) AS [Id] , [OtherColumns] FROM [dbo].[SomeTable]
That will give you a unique column that EF can use. I'm not sure scaffold-dbcontext will buy this without a primary key constraint, which view's don't have.

[timeshifter]

Any word on when the scaffolding function might be able to import views as read-only? I just ran headfirst into this, and it's actually kind of a big problem...

[ajcvickers]

@timeshifter This issue is in the Backlog milestone. This means that it is not going to happen for the 2.1 release. We will re-assess the backlog following the 2.1 release and consider this item at that time. However, keep in mind that there are many other high priority features with which it will be competing for resources.

[timeshifter]

No views, no procs, no functions, no clean way of handling multiple result sets..... guys, this is unacceptable. This is nowhere close to minimum viable product. This is basic stuff that's simply absent, for no apparent reason.

[fulte004]

@timeshifter I'm using views and stored procedures with EF Core 2.0 (and I could use functions, just don't need them for what I'm building), you'll just have to build the entity classes and their fluent mappings yourself, which I'm sure you knew already. If you're building a production application right now like I am, as a workaround for scaffolding views, you can leverage MS Excel or a similar spreadsheet that has a CONCATENATE function to quickly build the text to put in class files (I have boilerplate strings that will concatenate together the entire text of the Entity.cs files). You do need a macro (one example can be found here: ), to concatenate rows together so you can just copy your Excel-created content and paste it into your entity class files. As an alternative, you could create a separate Full .NET class library and use the EntityFramework Reverse POCO Code First Generator, which will scaffold everything you want. Then all you have to do is copy or file link the files you need into your .NET Core 2.0 project and apply whatever minor refactoring might be necessary for EF Core. (It's much easier to update your classes if you update your database this way.)

You can run absolutely anything you need using either EntityName.FromSql() or Database.ExecuteSqlCommandAsync() to run SQL queries, which I recommend scripting in something like .txt or .sql files and compiling them into your project as resources that you can easily retrieve with a GetQuery function like:
private string GetQuery (string resource) { var assembly = Assembly.GetEntryAssembly (); var resourceStream = assembly.GetManifestResourceStream (resource); string query; using (var reader = new StreamReader (resourceStream)) { query = reader.ReadToEnd (); } return query; }

While the scaffolding is still immature, EF Core 2.0 can handle probably 98% of what you need it to do out of the box and the other 2% you can probably find simple code or database workarounds for so that you're at 100%. Example of the 2%: Say you want to have more properties on your entity to merge in data from another data source like a web api. Using the fluent .Ignore() directive will cause EF to completely ignore those columns (not always desirable, like if you're using Breeze Server, which derives its JSON metadata from the active EF model). Instead of using .Ignore(), create a view that the DbContext will read from on your IQueryable API method so that you have your query baked into EF's select code, instead of its own:

string query = GetQuery ("myexampleproject.resources.files.myselectquery.sql"); var results = DbContext.EntityName.FromSql (query, whereParam1, whereParam2,...)

Your query can just use a null cast as the type you need (e.g CAST(NULL AS nvarchar(255)) AS [ColumnName]) in your SQL script. The above even works if you're using .Include to load navigation properties on the the query! Not sure how helpful this was, and I do understand your frustration, but maybe this gives you some ideas. Cheers!

[roji]

Add providers-fyi? I occasionally search for stuff I need to react it with providers-fyi and provider-beware, so it's useful to have them on any issues which may require provider attention.

[irowbin]

@ajcvickers Is there any way to generate Views from Ef Core CLI as a workground? Thanks.

[ajcvickers]

@irowbin If by "Ef Core CLI" you mean the "dotnet ef" command line tools, then I am not aware of any workaround.

[MikeDempseyFL]

For scaffolding purposes is it not true that a provider can treat a view exactly the same way as a table - except that it will not have any indexes, FKs or a PK?
[This is what I was planning to implement in our provider]
With rather more logic I think we could even copy the PK [and maybe FKs] from the underlying table if it was just a simple select from a single table. [Otherwise it would be read only]

However when it comes to Migrations there would need to be something in the DatabaseTable type to let us know this is a view not a table... otherwise it would be created as a table.

[jwooley]

Since this is now punted to 3.0, should the 2.2 roadmap be updated (https://github.com/aspnet/Announcements/issues/308). I would comment there, but feedback has been turned off on that page.

[divega]

Thanks @jwooley. I have posted an update to the announcement.

[iSatishYadav]

I was using Visual Studio _Add_-> _Controller_ for scaffolding but was unable to scaffold database views.
Here's a hackish but working work-around.

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: https://blog.satishyadav.com/How-to-Scaffold-Controllers-with-database-views-to-EF-Core-2-1/

[bricelam]

Blocked on #14194

[bricelam]

Design Notes

Providers will return DatabaseTable objects for both tables and views. If the DatabaseTable doesn't have a PrimaryKey, we'll map it as a "query type" (or whatever they become). We could also consider using a required alternate key as the primary key if one exists.

This implementation will also cover #5719

[ErikEJ]

Interested in a PR for the SQL Server scaffolder? Or is it too early?

[bricelam]

@ErikEJ Sure! AFAIK, entityBuilder.HasNoKey() is working now.

[bricelam]

After you get SQL Server working, SQLite might be as simple as changing this line:

https://github.com/aspnet/EntityFrameworkCore/blob/1dfb651393d9b5ac66ad73d5d0ae6a2866be0e2a/src/EFCore.Sqlite.Core/Scaffolding/Internal/SqliteDatabaseModelFactory.cs#L158

To use "type" IN ('table', 'view') instead.

[ErikEJ]

Great, it looks like SQL Server will be slightly more complex

[roji]

@bricelam and others, entity and query types have been merged, but should we consider maintaining some sort of trace of the table/view distinction in the scaffolded DatabaseModel? After all it's a real database distinction that's currently getting lost. I'm not quite sure where such a distinction would actually inform the scaffolding process, but I'm looking at DatabaseModel as a, well, model of the database...

[ErikEJ]

@roji View "Tables" have no primary key

[roji]

@ErikEJ Oracle does seem to support the concept of a primary key on views: https://stackoverflow.com/questions/11667508/how-to-add-primary-key-to-view/11667651#11667651

I can also at least imagine some form of updatable views which would also expose the primary key from an underlying table (no idea whether this is actually a reality).

The existence or lack of a primary key is clearly the important factor in the EF Core model - but I'm not 100% sure it should also be that way in the reverse-engineered database model.

[ajcvickers]

@roji We could scaffold ToView for them, even when not strictly necessary.

[fulte004]

I put this out there FWIW:
Simon Hughes' EntityFramework Reverse POCO Generator reverse engineers views, with the requirement that views provide a unique, non-null identity column, which I've used
ROW_NUMBER() OVER( ORDER BY (SELECT 1) ) AS TableID
to achieve for views that did not contain a table primary key.
The Reverse POCO Generator is able to identify when a view does not have a key that would uniquely identify an entity, so how is he accomplishing this for EF 6?
Support for EF Core is on his list of future enhancements, so he's already planning on implementing it for his project.

[tdjastrzebski]

It seems to me things get more complicated than it really need to be.

1. Entity Framework should NOT try be become a database framework.
2. Tables and views are database concepts. Distinction between those two, IMHO, does not make sense in EF.
3. In entity framework one would only expect collections of entities - some read-only and some updatable with read-only properties and updatable properties, with keys and without them.

[fulte004]

Perhaps I should clarify what I wrote: My reference to "EntityFramework Reverse POCO Generator reverse engineering views" was a _very poorly worded statement_ on my part. It technically does not "reverse engineer" a view. It scaffolds an Entity class based on a database view just as it would a table. It does require that a view definition possess something perceivable as a primary key, where each entity populated would have a unique identity.

In no way was I asserting a belief that EF Core should be a database framework. :)

[svengeance]

RE: Not being a database framework.. didn't we kind of lose that separation with code-first databases? If we were only concerned about a one-way translation of a database model to C# it might be a bit more understandable to drop the differences between Tables and Views. In the interest of extensibility for future code-first approaches (say, scaffolding Views straight from code), it might make sense to preserve some sort of differentiation.

[roji]

@tdjastrzebski

Entity Framework should NOT try be become a database framework.
Tables and views are database concepts. Distinction between those two, IMHO, does not make sense in EF.

I'm not exactly sure what you consider to be a "database framework", but it seems like according to this logic, EF Core would not know anything about, say, indexes, nor allow users to set them up... As a general rule EF Core does try to support setting up database-specific concepts and features, and in some cases even concepts that are specific to one specific database system.

[tdjastrzebski]

@roji Indexes are not limited to database at all. The concept is quite ancient. Perhaps Egyptians used them.
My point is this project seems to put too much focus on database support, trying to adopt database concepts too far. It is a matter of mindset. Hard to deny DB is all over the place. DbContext class, Scaffold-DbContext etc. instead of EfContext.
How does that relay to views support? IMHO there should be no views specific support. It does not make sense. In reality, even SQL Server views can updatable (with or without triggers) while tables can be read-only (e.g. most columns calculated). Attempt to support all such scenarios using separate object types is doomed to fail.

[roji]

Indexes are not limited to database all. The concept is quite ancient. Perhaps Egyptians used them.

😃 That's probably true, although if that's you criterion I'm sure there are many other concepts here which could be said to have been used by the Egyptians.

But the point is that indexes mean nothing to EF Core itself - it does not affect it in any way. The only reason it is aware of indexes is so that they can get created at the database, and EF Core obviously needs to be able to handle that.

The distinction between tables and views could be seen as somewhat similar. EF Core doesn't necessarily care about them, but rather about certain characteristics such as whether a primary key is defined or read-onlyness vs. updatability, which don't always correspond to the table/view split across different database systems. But aside from those characteristics, the SQL standard and relational databases do define the concept of tables and views, and in at least some cases it's going to be important to EF Core users to specify that distinction so that the desired object type gets created at the database.

[tdjastrzebski]

@roji That all depends if this product is meant to be Relational Database Framework or Entity Framework. The difference may seem subtle yet it is profound.
But even than tables often do not have primary key and all this distinction between tables and views becomes useless and IMHO should be removed before it is not too late as it only clutters otherwise good design. Anyway, it is just my 3 cents, but I have been dealing with such frameworks since COM ADO.

[ajcvickers]

@tdjastrzebski EF Core is not intended to fully abstract away database concepts. Instead it is intended to provide common concepts and patterns for data access, while still exposing relational or database-specific concepts and patterns so they can be used when they make sense. So, in essence, I agree with your statement that the difference is "subtle yet profound" and EF Core is quite definitely what you refereed to as a "Relational Database Framework", although it's not just about relational databases. At this point "Entity Framework" is just a brand.

[roji]

It's also worth pointing out that EF Core does distinguish between general-purpose data access, to non-relational data stores (e.g. in-memory provider, CosmosDB provider), and specific support for relational databases (SQL). All support for the latter can be found in Microsoft.EntityFrameworkCore.Relational, which is also published as a separate nuget. For example, all components generating SQL are part of relational (and it can definitely be said that SQL is a database-specific concept).

So within the specific support for relational databases, relational-specific concepts do seem to make sense. However, the non-relational parts of EF Core are "unpolluted" by these in any way.

[tdjastrzebski]

@roji, @ajcvickers My point is in modern relational databases functional distinction between tables and views is blurry. Hence, such distinction does not make sense in a framework aimed at providing abstraction layer. What makes sense instead are read-write and read-only entity sets.
It has nothing to do with indexes or any other objects needed mostly 'under the hood' to build queries and optimize performance. Perhaps direct DB object mapping helps code-first but let's be honest, code-first is and always will be best suited for simple demos.

[divega]

@tdjastrzebski I really appreciate your feedback. It seems you and I agree on a few points, although not on all :smile:

but let's be honest, code-first is and always will be best suited for simple demos.

I am going to assume for now that this isn’t true, that EF and EF Core are very popular O/RMs successfully used by a large number of .NET developers, and that many of those customers are successfully using the code first approach, because this is more in line with data I have.

Tables and views are database concepts. Distinction between those two, IMHO, does not make sense in EF.

I think it is fair to say that EF Core tries to abstract common aspects of databases to enable customers to write code that queries and persists data in terms of entities, typically __using__ a DbContext.
This abstraction is achieved with help from two-way mappings between database objects and entities, which are associated with a DbContext. I think it is natural and expected that the APIs that are used (typically in code that is part of the DbContext) to define those mappings often refer to database concepts.

My point is in modern relational databases __functional__ distinction between tables and views is blurry.

I think it is true that at the database level tables and views can be __used__ pretty much interchangeably (views may be more often read-only or have no keys, but we know that is not universally true, and I think we are all mostly in agreement that conflating these concepts was a bad idea).

The actual difference between tables and views is in how they are defined: while tables represent named storage areas with a specific schema, views are named _virtual tables_ based on queries over other tables or views.

In alignment with all of this, our plan for EF Core 3.0 is that entities configured to map to views will have the exact same query and persistence capabilities as entities mapped to tables. The only difference between ToView and ToTable will be in what migrations and EnsureCreated will do with them:

• If you use ToTable, a table will be created

• If you use ToView, a table will not be created.

Ideally EF Core migrations and EnsureCreated should have the capability to create views instead, but for now that is not the case. We are missing migrations operations that represent DDL for views, and an API to associate a defining query with the view. Given this limitation, migrations and EnsureCreated will have to simply ignore (not try to create any DDL) for any database object introduced to the model with ToView.

__And this is where I have my actual concerns with ToView:__

If someday we want to add the capability to create database views, an argument providing a defining query for the view would be required, but is it ok to just implicitly ignore the object if it isn’t provided?

I think the connection between the ToView method and the behavior (database object is to be ignored by migrations and EnsureCreated) becomes too implicit and obscure. E.g.:

• The behavior isn't conveyed at all by the name of the API, so it is not discoverable.

• Once customers learn how it works, they could start calling ToView for tables in the database for which they don’t want EF Core to ever produce any DDL.

That might work, but would it really be ok? It reminds me of the kind of thing that happens when we make it too easy to conflate orthogonal concepts, e.g. when customers started trying to use the Query

[smitpatel]

Note: Verify generated file content and E2E experience and close the issue.

[ErikEJ]

Thanks, @smitpatel, I will also try to verify manually from the daily build.

[bricelam]

@smitpatel @ErikEJ Can this be closed?

[ErikEJ]

@smitpatel @bricelam Confirmed that this works as desired with preview6 and AdventureWorks2014:

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

            entity.ToTable("vVendorWithContacts", "Purchasing");

[abrantie1z]

Kudus EF Core Team. Much Love from Ghana! We appreciate your efforts.

[abrantie1z]

However there are errors:

i scaffolded a single view using reverse engineering which worked like charm. i went through the DBContext class and it had the HasNoKey() method. but when i try to get records front the view, i get this error.

the codes i wrote to get the records from the view is:

[ajcvickers]

@garethsuarez @abrantie1z What version of EF Core are you using?

[abrantie1z]

@ajcvickers I am using Microsoft.EntityFrameworkCore.Tools 3.0.0-preview6.19304.10

[ErikEJ]

@abrantie1z Preview 6 is buggy... Wait for preview 7

[danobri]

Is there a way to continue to only scaffold tables in EF Core 3.0? I don't see a switch to enable / disable view scaffolding in the documentation.

[ErikEJ]

@danobri No, currently you must specify the tables to scaffold on the command line, our use a tool like EF Core Power Tools, that makes this simpler for you.

[danobri]

Thanks for confirming. Would be great to have a switch on Scaffold-DbContext to opt in or out of view scaffolding. This is essentially a breaking change for our project, as we have been doing database first with Scaffold-DbContext -force since EF Core v1, and handling views manually given the lack of support for them.