Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

RevEng: Support Views #1679

Closed
lajones opened this issue Feb 20, 2015 · 58 comments

Comments

@lajones
Copy link
Member

commented Feb 20, 2015

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

This comment has been minimized.

Copy link
Member

commented May 27, 2016

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

This comment has been minimized.

Copy link

commented Jul 10, 2017

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

This comment has been minimized.

Copy link
Member

commented Jul 11, 2017

@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

This comment has been minimized.

Copy link

commented Sep 19, 2017

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

This comment has been minimized.

Copy link
Member

commented Sep 21, 2017

@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

This comment has been minimized.

Copy link

commented Sep 21, 2017

@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

This comment has been minimized.

Copy link
Contributor

commented Sep 21, 2017

@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

This comment has been minimized.

Copy link
Member

commented Sep 21, 2017

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

This comment has been minimized.

Copy link

commented Sep 21, 2017

@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

This comment has been minimized.

Copy link

commented Sep 21, 2017

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

@bricelam

This comment has been minimized.

Copy link
Member

commented Sep 25, 2017

@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

This comment has been minimized.

Copy link
Member

commented Sep 25, 2017

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

@tdjastrzebski

This comment has been minimized.

Copy link

commented Sep 25, 2017

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

@bricelam

This comment has been minimized.

Copy link
Member

commented Sep 25, 2017

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

This comment has been minimized.

Copy link

commented Sep 25, 2017

Yep, working name is DbView<T> 😄

@roji

This comment has been minimized.

Copy link
Member

commented May 3, 2019

@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

This comment has been minimized.

Copy link
Member

commented May 3, 2019

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

@fulte004

This comment has been minimized.

Copy link

commented May 3, 2019

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

This comment has been minimized.

Copy link

commented May 3, 2019

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

This comment has been minimized.

Copy link

commented May 3, 2019

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

This comment has been minimized.

Copy link

commented May 3, 2019

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

This comment has been minimized.

Copy link
Member

commented May 3, 2019

@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

This comment has been minimized.

Copy link

commented May 4, 2019

@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

This comment has been minimized.

Copy link
Member

commented May 4, 2019

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

This comment has been minimized.

Copy link

commented May 4, 2019

@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

This comment has been minimized.

Copy link
Member

commented May 4, 2019

@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

This comment has been minimized.

Copy link
Member

commented May 4, 2019

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

This comment has been minimized.

Copy link

commented May 5, 2019

@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

This comment has been minimized.

Copy link
Member

commented May 6, 2019

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

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<T> API with tables that they wanted to treat as read-only, even if they had primary keys defined.

ErikEJ added a commit to ErikEJ/EntityFramework that referenced this issue May 7, 2019

smitpatel added a commit that referenced this issue May 7, 2019

@smitpatel

This comment has been minimized.

Copy link
Contributor

commented May 7, 2019

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

@ErikEJ

This comment has been minimized.

Copy link
Contributor

commented May 8, 2019

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

@bricelam

This comment has been minimized.

Copy link
Member

commented May 9, 2019

@smitpatel @ErikEJ Can this be closed?

@ErikEJ

This comment has been minimized.

Copy link
Contributor

commented Jun 15, 2019

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

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

            entity.ToTable("vVendorWithContacts", "Purchasing");
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.