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

Improved view support #827

Open
ilmax opened this issue Oct 8, 2014 · 40 comments

Comments

Projects
None yet
@ilmax
Copy link
Contributor

commented Oct 8, 2014

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 for types without keys, which may be mapped to views - issue #9290
  • Reverse engineer/scaffold types from views in a database - issue #1679
  • Support for objects without keys which may be mapped to views or tables without keys - issue #1862
  • Query for arbitrary types without those types being explicitly mapped - issue #10753
  • Updates using stored procedures - issue #245
  • Create views from Migrations - issue #465
  • Improve documentation for using database views - aspnet/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

This comment has been minimized.

Copy link
Member

commented Oct 10, 2014

@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.

@rowanmiller rowanmiller added this to the Backlog milestone Oct 10, 2014

@ilmax

This comment has been minimized.

Copy link
Contributor Author

commented Apr 17, 2015

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

This comment has been minimized.

Copy link

commented May 6, 2015

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

This comment has been minimized.

Copy link

commented May 10, 2015

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

This comment has been minimized.

Copy link

commented Sep 19, 2015

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

@ilmax

This comment has been minimized.

Copy link
Contributor Author

commented Sep 19, 2015

@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

This comment has been minimized.

Copy link

commented Feb 13, 2016

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

@ilmax

This comment has been minimized.

Copy link
Contributor Author

commented Feb 15, 2016

@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

This comment has been minimized.

Copy link

commented Feb 15, 2016

@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

This comment has been minimized.

Copy link
Member

commented Feb 16, 2016

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

This comment has been minimized.

Copy link
Member

commented Feb 16, 2016

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

This comment has been minimized.

Copy link

commented Feb 16, 2016

@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

This comment has been minimized.

Copy link
Member

commented Feb 16, 2016

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

This comment has been minimized.

Copy link

commented May 17, 2016

@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

This comment has been minimized.

Copy link
Member

commented May 17, 2016

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

@gdoron

This comment has been minimized.

Copy link

commented May 17, 2016

@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

This comment has been minimized.

Copy link

commented May 18, 2016

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

@vcannaday

This comment has been minimized.

Copy link

commented Oct 26, 2016

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

This comment has been minimized.

Copy link

commented Dec 25, 2016

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

@AxelAndersen

This comment has been minimized.

Copy link

commented Jan 28, 2017

Do you have any release date for supporting views?

@shaulbehr

This comment has been minimized.

Copy link

commented Jan 28, 2017

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

@rowanmiller

This comment has been minimized.

Copy link
Member

commented Jan 30, 2017

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

This comment has been minimized.

Copy link

commented Feb 21, 2017

When can we hope to see it done?

@ErikEJ

This comment has been minimized.

Copy link
Contributor

commented Feb 21, 2017

@BillieBishop Did you see #4561 ?

@danobri

This comment has been minimized.

Copy link

commented Mar 11, 2017

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).

@divega divega changed the title View support Improved view support May 4, 2017

@ArgusMagnus

This comment has been minimized.

Copy link

commented Jun 5, 2017

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

Me too

@nhustak

This comment has been minimized.

Copy link

commented Aug 3, 2017

I'll add to the 'I require views' and 'it's about useless to me without it'

@rnanjala

This comment has been minimized.

Copy link

commented Aug 23, 2017

i have installed entiry core 2.0 version and it's still not supporting views. any timeline as when we can use views using entitycore?

@ajcvickers

This comment has been minimized.

Copy link
Member

commented Aug 23, 2017

@nhustak @rnanjala @ArgusMagnus Can you be more specific about what you want with regards to "supporting views"--perhaps looking at #9290 as well. The reason I ask is that there are various ways that EF could interact with views, but taking two examples, the work to reverse engineer from views is very different from the work to query for data without keys, and both of these could be considered "view support".

@ajcvickers ajcvickers closed this Aug 23, 2017

@ajcvickers ajcvickers reopened this Aug 23, 2017

@nhustak

This comment has been minimized.

Copy link

commented Aug 23, 2017

@ajcvickers I did some googling and I'm fine with using a fromSQL to get data from my views. I was able to add them to my context, albeit one of them I had to fake a primary key setup. I don't have a particular view issue at this point in time - I can't really say what I was expecting. Honestly I'm much more frustrated from not being able to inherit from a table model directly to a view model - but even that is more of an annoyance I worked around by creating a base model for them both to inherit from.

@nhustak

This comment has been minimized.

Copy link

commented Aug 23, 2017

@ajcvickers I like where #9290 is headed.

@rnanjala

This comment has been minimized.

Copy link

commented Aug 24, 2017

@ajcvickers in the old entity framework with webforms, when I create the edmx, i am able to access the veiew directly like database objects but in the entity core 2.0, I couldn't do that directly. One of the workarounds I found is exxecuting fromsql(). is there a way, I could access the view directly like we do with tables?

@rogerhendriks

This comment has been minimized.

Copy link

commented Aug 26, 2017

@ajcvickers If #9290 supports mapping to read-only views and TVF's we can using it in a more professional way then with fromSQL. I hope this will be in at first release? This and the groupby translation problems keeps us from starting full swing, so desparately waiting for 2.1 ;)
Maybe we need a seperate issue for Updatable views where you need to define a PK? Things can only become better as view support 6.x / edmx is not so good.

@Misiu

This comment has been minimized.

Copy link

commented Aug 29, 2017

@rogerhendriks I think updatable views could be done via stored procedure mapping #245.
I have a use case where I have table and view. View is basically SELECT * FROM TABLE but has some complex WHERE that filters rows in my view.
So ideally one could use view to read from database and stored procedures to do other operations (create,update,delete).

For example:

class BlogContext : DbContext
{
    public DbSet<Blog> Blogs { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Blog>()
            .ToView("active_blogs")
			.WithStoredProcedures(s =>
				s.Update(u => u.HasName("modify_blog")
					.Parameter(b => b.BlogId, "blog_id")
					.Parameter(b => b.Name, "blog_name")
					.Parameter(b => b.Url, "blog_url"))
				 .Delete(d => d.HasName("delete_blog"))
				 .Insert(i => i.HasName("insert_blog")));
    }
}

similar to EF6

@Misiu

This comment has been minimized.

Copy link

commented Nov 29, 2017

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

@ajcvickers

This comment has been minimized.

Copy link
Member

commented Nov 29, 2017

@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.

@MgSam MgSam referenced this issue Jun 8, 2018

Open

Support views #19

@eltiare

This comment has been minimized.

Copy link

commented Nov 6, 2018

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

@joswa888

This comment has been minimized.

Copy link

commented Apr 30, 2019

going 5 years.

@gsGabriel

This comment has been minimized.

Copy link

commented May 6, 2019

image

i still waiting :D

@jhirschdrg

This comment has been minimized.

Copy link

commented May 14, 2019

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.