Improved view support

[ilmax]

This is a meta-issue covering the different aspects of support for mapping to and using views in EF Core. Different parts of this support will be done at different times, so while this issue may be in the Backlog, the individual issues below contain the status for various aspects of view support.

• Query from view with key - this already works; for queries a view with a key is handled the same way as a table. Just define an entity type in the normal way.
• Query Types: Model types that do not require identity #9290
• RevEng: Support Views #1679
• Support for querying objects without keys #1862
• Support raw SQL queries without defining an entity type for the result #10753
• Support updatable views #20013
• Materialised views #20858
• Map inserts, updates, and deletes (CUD operations) to stored procedures #245
• Create views from Migrations
  • Create database views in Migrations using SQL generated from a LINQ projection #465,
  • Create database views in Migrations using SQL specified in the model #14537
• Scaffold views into SQL definitions on the model #20940
• To vs From methods: Proposal to rationalize ToTable, ToQuery, ToView, FromSql, and other related methods #17270
• Support separation of query and update mapping #15671
• Allow owned entities on a keyless entity type #17890
• Improve documentation for using database views EntityFramework.Docs#480

Please consider voting 👍 for one of the issues above rather than for this overall issue, since that will provide more specific and actionable feedback about the relative importance of different aspects of view support.

---

Original issue text:

Hi, I have prototyped a really basic (or even less) view support through part of the EF Core stack, currently my prototype is able to

• Map entity to a View via fluent api
• Create a view via migrations
• Read and materialize values backed by views
• Avoid updating entities if backed by views

As stated above, this is a really naive approach and I know there are a tons of unsupported features at the moment, anyway I really would like to improve this further and I would be glad to discuss implementation details with the EF team and the community.

I would like to know if the EF team are interested in this contributions.
Thanks, Max.

[rowanmiller]

@ilmax - At this early stage of the project this isn't something we want to take just yet (as we're still heavily iterating on the core of the product). You could do this as an external add-on to EF and we would definitely be open to considering taking it a little further down the road.

[ilmax]

Hi @rowanmiller, does the team is interested in such contribution? I would like to be able to query views with EF, and would love to give my contribution on this feature.

If this is something the team can be interested in, I would like to start a discussion around the needed changes and the general direction to take.

Regards, Max.

[milutinovici]

I agree with Max, this would be an excellent feature. It would be even cooler if we could use linq to define indexed views.

[rogerhendriks]

Without view support EF Core is almost useless as, I'm sorry to say, the query generator does not have enough intelligence for supporting high performant business applications with 10 or more joins, unions and complex "group by" statements on applications with millions of records. And some databases just all ready have good views on board. ICT work is often about modernizing gui's on old databases.
And we don't want to use raw sql to be able to get a 1-to-1 mapping of views/viewmodels. Please do support views and updatable views by -self defining- keys in metadata as we now have to use 6.x isnull/nullif and 'editing the edmx after each update' workarounds.

[staff0rd]

@ilmax care to share how you are mapping views and generating migrations for them?

[ilmax]

@staff0rd my prototype was build against early EF Core version, since ef was in the early days at the time, I abandoned the prototype, waiting for EF Core to RC.
I think that now the majority of EF building blocks are quite stable, so maybe I can start working on this and put together a prototype here on github.

[gdoron]

@ilmax any update, EF Core is RC already... :)
I would really be happy using this feature.

[ilmax]

@gdoron Since EF Core is a completely new rewrite, over the time a lot of parts have changed so my POC is not working anymore, I plan to work on this as soon as EF Core reaches RTM

[gdoron]

@ilmax I think since EF is RC already there won't be as much changes as when it was Alpha/beta.

But regardless, once you contributed the code and it was merged, it's in the repository, and if the EF team will do a breaking change that break the view generation (not a likely scenario since RC), they will have to fix it, so I wouldn't be too much worry about it.

[rowanmiller]

BTW in the meantime you can just tell EF that the view is a table. The limitations of this approach are that you need to give EF some properties to use as a key, and migrations won't create a view for you. You can also use raw SQL to select from a view context.Blogs.SqlQuery("SELECT * FROM dbo.TopBlogsView").

[rowanmiller]

But regardless, once you contributed the code and it was merged, it's in the repository, and if the EF team will do a breaking change that break the view generation (not a likely scenario since RC), they will have to fix it, so I wouldn't be too much worry about it.

This is true, but the feature needs to be completely implemented (including tests etc.) for us to take the code.

[gdoron]

@rowanmiller but how can I trick EF not to try and create the table (which is a view) in the migrations?
Manually remove the creation from the migration?

context.Blogs.SqlQuery("SELECT * FROM dbo.TopBlogsView").

This will work only if the view returns data from a single table, if it's (like in 99% of the times) a view that returns data from multiple composed tables, we'll get only the data from the Blogs, the columns from Users let's say will be ignored when deserialized.

in the meantime you can just tell EF that the view is a table

You reminded me an old jewish joke I heard 10 years ago. :)

[rowanmiller]

but how can I trick EF not to try and create the table (which is a view) in the migrations?
Manually remove the creation from the migration?

Yes, that is what you would need to do

[gdoron]

@rowanmiller I don't see VIEWs support in the roadmap at all.
Do you really consider it be in in such a low priority that all the stuff in the roadmap are more important than VIEWs or you simply forgot to mention it?

Thanks

[rowanmiller]

We just missed it off the list, I've added it to the roadmap.

[gdoron]

@rowanmiller You might want to add a link in the roadmap to this PR for how it can be done in the meanwhile: #4561 so people won't think there's absolutely no way of doing it.

Thanks

[rogerhendriks]

@rowanmiller great to see it's on the roadmap! Kutgw

[vcannaday]

Has this issue been targeted for a release yet?

We are currently having to maintain two database catalogs for code generation. We have one database that contains table schema only to represent the views that exist in our actual database.

Vance

[douglasg14b]

Happy this is on the roadmap, it's hard to justify EF Core usage without DBFunctions and views.

[AxelAndersen]

Do you have any release date for supporting views?

[shaulbehr]

For the record, I'll add a "Me, too!" comment here. Please can you prioritize View support?

[rowanmiller]

One of the big things folks want when working with views is to be able to query data into objects that are not entities (and therefore the view doesn't have to be normalized or have a key). We expect to at least make progress on that feature in 2.0 (#1862).

[BillieBishop]

When can we hope to see it done?

[ErikEJ]

@BillieBishop Did you see #4561 ?

[danobri]

I agree with many others here - view support is critically important - please try to get it in the next release. The lack of view support is especially painful given that there are also issues with doing projections on related objects - #5738 and #7131. Unless there is something I am missing - at this point the only efficient way to do a query that joins a bunch of entities and selects a minimal number of fields is to pass raw SQL (i.e. - SqlQuery function).

[ArgusMagnus]

For the record, I'll add a "Me, too!" comment here. Please can you prioritize View support?

Me too

[Misiu]

@ajcvickers any news about this? It is added to backlog, but I can't find any information about any progress.

[ajcvickers]

@Misiu When an issue is in the backlog it means that the EF team is intending to work on it at some point in the future, but is not actively working on it now--that's what the "backlog" is. For this issue specifically, there are several sub-issues, one of which is being worked on for 2.1--#9290. I believe the other sub-issues are also still in the backlog.

[eltiare]

I'm just here to add a "me too" to indicate that there is still interest in this feature.

[joswa888]

going 5 years.

[gsGabriel]

i still waiting :D

[jhirschdrg]

As i see this will be one of the new feature in ef core 3
https://docs.microsoft.com/en-us/ef/core/what-is-new/ef-core-3.0/features#reverse-engineering-of-database-views

[divega]

In 3.0 we have completed some of the most important aspects of this "aggregate issue" on database view support. Adding propose-close because I think its existence gets in the way of figuring out the relative priority of the remaining issues and even possibly of figuring out other important aspects of view support that are missing. As far as I know, all the remaining aspects that we know about are being tracked by its own independent issues in the backlog.

We could alternatively choose to make this a discussion issue, but I don't think those have proven very useful.

[mrlife]

After trying it out, #9290 covers views well.

[DarioN1]

Please, have a look there:

https://github.com/DarioN1/SPToCore

I have created a kind of scaffolder for Stored Procedure, I'm using it in a new project that uses an existing database with more than 300 stored procedures.