efcore: Query Types: Model types that do not require identity
Note: The feature tracked in this issue could help with using EF Core with database views. However, the feature is not limited to database views and its completion would not mean that every aspect of database view support has been implemented. See #827 for an overview of the areas where EF Core interacts with database views.
We want to enable modeling types that exist independently of entities but have no keys (and therefore are not updatable) as first class objects in EF Core models.
Relationship between query types and other features:
- We consider this a stepping stone to enable ad-hoc mapping for arbitrary types (https://github.com/aspnet/EntityFramework/issues/1862)
- The plan may involve enabling “defining queries”, originally described in https://github.com/aspnet/EntityFramework/issues/3932 for these types, because query types are not necessarily mapped directly to database tables or views.
- We could also have the ability to introduce a “null” defining query for query types that will only be used in ad-hoc queries. Note that defining queries (including null defining queries) could later be enabled for entity types, but we need a way to map CUD operations.
- One thing query types can help with is as the result type for Table-Valued Functions (covered by https://github.com/aspnet/EntityFramework/issues/4319). If we enable some kind of first class mapping of TVFs or even for TVFs using in
FromSql()or defining query, having result types that don’t need to have keys makes the feature much more useful. - Query types are related to owned types conceptually. You could think about them as owned types that don’t happen to have an owner 😄 (hence no identity, hence no ability to update)
- Query types are different but somewhat related to the idea of supporting database views. In the past when this has been brought up it was usually about mapping database views to entity types (e.g. https://github.com/aspnet/EntityFramework/issues/827, https://github.com/aspnet/EntityFramework/issues/1679) but if we have query types in the model then one could choose to map them to views in the database, and that would also be more flexible because database views don’t necessary have keys and are not necessarily updatable.
- It could also be related to supporting the definition of views and the creation of views in migrations described at https://github.com/aspnet/EntityFramework/issues/465.
Alternative names for the feature
We can consider renaming the feature to something else if we find a better name. So far the list is short:
- View types
- Structural types
About this issue
- Original URL
- State: closed
- Created 7 years ago
- Reactions: 28
- Comments: 43 (12 by maintainers)
I vote for this feature. I am using EF Core 2.0 to load results from a view I’m using as part of a complex search. The view does not have a primary key.
In order to satisfy Entity Frameworks primary key requirement I lied and told EF that a column is a primary key. EF produced the result-set and all looked OK at first, but I found that when the “key” column contained duplicates - EF had replaced all subsequent rows with an exact copy of the first row for each “key” value!!! There was no warning, no error! OK, I did lie about the key, so I suppose EF has the right to lie back to me.
My eventual workaround for this was to manufacture an ID column using ROW_NUMBER() and use that column as the key. So, Entity Framework seems needy requiring this key definition.
I vote to have a View Type (or possibly call it a Report type or a Read-Only type). This entity type would not care about keys or change tracking, so it could be simple and light-weight.
@anpete Will we still need to define a DbQuery with Stored Procedures?
I was hoping for something like FromSql (FromQuery) without the DbSet to map to POCO classes
The lack of views support is still a show stopper for me as my solutions heavily depend on them. Please also consider that SQL Server views can be updatable. No matter how views support is (going to be) implemented please at least make sure that code can be quickly fixed to allow for updates. Eg. adding PK (HasKey/[Key]) is sufficient to allow for basic updates
@anpete Just so I get this right: So
modelBuilder.Query<T>is basically the same thing asmodelBuilder.Entity<T>except that the configured type does not have the restrictions entities have (e.g. having a table and identity)? That’s great! 😃Tables/Views distinction makes no sense. Drop this concept please. Even in SQL Server tables can be read-only while views can be updatable. EF Framework was going to be universal entity framework, not just DB entity framework. In my view here EF Framework Team goes wrong direction. My proposition: scaffold entities for all SQL tables and views. Those with no PK will remain read-only. See #9854 for what currently I need to do to accomplish exactly what I need. I understand the concern may be error-proneness and product quality perceived by the beginners but versatility, simplicity and functionality shall prevail.
@smitpatel, @emmielewis I created https://github.com/aspnet/EntityFramework.Docs/pull/682/.
@tdjastrzebski Again, EF Core does not care whether you are mapping entity types to database tables or updatable views. Scaffolding does not support views, but you can easily hand write entity types and map them perfectly well to views (via ToTable) - Yes, ToTable is somewhat confusing here, but the way to think about this is that it is simply allows you to configure the name of the database object that EF will use when generating SQL.
Additionally, in 2.1 we are adding Query Types, which allow you to define model types that do not have identity. These can also be mapped to tables or views in the database but are never tracked and are therefore essentially read-only.
@alexzaytsev-newsroomly Expect docs to accompany the release. For now, you can take a look at our tests:
https://github.com/aspnet/EntityFrameworkCore/blob/dev/src/EFCore.Specification.Tests/TestModels/Northwind/NorthwindContext.cs https://github.com/aspnet/EntityFrameworkCore/blob/dev/src/EFCore.Relational.Specification.Tests/TestModels/Northwind/NorthwindRelationalContext.cs https://github.com/aspnet/EntityFrameworkCore/blob/dev/src/EFCore.Specification.Tests/Query/SimpleQueryTestBase.QueryTypes.cs
@renbud Then how updatable views should be treated? I suggest treating them exactly the same way as tables after when PK is specified and that usually has to be done ‘manually’. In consequence there are two types of DB views which have to be handled differently.
It just seems freaky to be lacking support for such a fundamental thing this far down the line. We gave up, and we use LLBLGen Pro, as that supports Views in its .NET Core 2 Runtime Framework. https://www.llblgen.com/ Now we have automatic context, high performance, and even automatic generation of DTO objects from a database-first perspective when we want it (and yes, we have diagrams to understand entity relationships) It might not be the most user friendly system to use (that’s an understatement) but it’s incredibly powerful, fast, and reliable. Oh, and did I mention it supports views in .Net core?