Efcore: Query Types: Model types that do not require identity

[divega]

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 :smile: (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

[pmiddleton]

Table valued functions can return an arbitrary type. Usually this result is then cross applied with other parts of the query, but it can just be selected. I need a way to map this type and I think this feature could help with that.

[divega]

@pmiddleton that is exactly right. I will add it to the list or related features.

[MarcoLoetscher]

In this context, you should also support temporary tables.

[tdjastrzebski]

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]

@tdjastrzebski Can you elaborate on what isn't working for you? You can already map entity types to database views and it will work fine (provided the views are updatable) - We make no distinction between views and tables when generating SQL during query or update.

This feature is not really about database views, but about being able to model key-less structural types.

[tdjastrzebski]

@anpete I assumed views were not supported at all since entity classes were not generated - at least not using Scaffold-DbContext. Maybe there should be a switch added?

[tdjastrzebski]

I registered new issue #9854, duplicate of #1679

[tdjastrzebski]

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.

[Vasim-DigitalNexus]

RE: Those with no PK will remain read-only

@tdjastrzebski, however, there can be read-only views and stored procedures that "do" return data with PKs; IMHO using the PK to derive (read-only/write access) may not be the best option; perhaps this would work for you #1862

[tdjastrzebski]

@Vasimovic correct, this may not be 100% reliable option. Similarly, scaffolding will perhaps never be 100% correct with that respect simply because new providers will be implemented with functionality we cannot even predict yet. So why bother with special view classes? Just so the developer does not get exception when he/she tries what is not supported?
If so, ReadOnlyDbSet is another option which I think might be viable. Those who know what they are doing can change ReadOnlyDbSet to DbSet derived from ReadOnlyDbSet and voilà.

[jsobell]

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?

[renbud]

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.

[tdjastrzebski]

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

[renbud]

@tdjastrzebski ReadOnlyDbSet as you suggested seems the way to go.
It may not be just for views. Could also be for stored procedures, or ad-hoc queries.
There can be many use-cases for read-only access.

[tdjastrzebski]

@renbud I meant more how to reasonably decide during reverse engineering which DbSet should be ReadOnly since it may not be straight forward.

[renbud]

@tdjastrzebski I thought either

a) The lack of a Key property would cause the DbSet to behave as ReadOnly
or
b) A new explicit ReadOnly property on DbSet
or
c) The developer explicitly asks for a ReadOnlyDbSet e.g.

namespace ContosoUniversity.Data
{
    public class SchoolContext : DbContext
    {
        public SchoolContext(DbContextOptions<SchoolContext> options) : base(options)
        {
        }

        public DbSet<Course> Courses { get; set; }
    ...
    public ReadOnlyDbSet<MySearchResultView> SearchResults { get; set; }
    ...
    }
}

I think any of these would work for me, although possibly (c) seems the cleanest.

[Vasim-DigitalNexus]

@renbud yes (c) or (b) would be better not to mention that having a read-only DB set would be useful.

However, that does not solve the primary key issue which may or may not exist in the model and the fact that you have to add (view/read-only) models to the DbSet

I think this would be better #1862; Mapping Arbitrary Types. The primary key is optional or treated as a regular column, and there is no need to add models to the DbSet's (there could be thousands of read-only models, adding them to a DbSet would add unnecessary overhead)

[renbud]

@Vasimovic I agree #1862 captures my use case. If ad-hoc sql could be mapped to an arbitrary type in a read-only manner then I would be happy. Essentially I am just looking for an easy way to capture reports output into a Dot Net collection.

[tdjastrzebski]

@Vasimovic While I am typically looking for updatable views to be handled exactly like tables.

[Vasim-DigitalNexus]

@tdjastrzebski I understand, there are two issues here, updatable views (should be bound to DbSet) and mapping of arbitrary types (not bound to DbSet)

[alexzaytsev-newsroomly]

It's good to see some work done on this. Would also be great if the documentation explaining how to use query types is provided somewhere!

[anpete]

@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

[anpete]

Created #10753 to track the remaining work here.

[Vasim-DigitalNexus]

@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

[anpete]

@Vasimovic No, you don't need a DbQuery property on your context (if that's what you mean). You _do_ need to register the query type in OnModelCreating, via modelBuilder.Query<T>() - This is what #10753 is about, i.e. allowing the query type to be added to the model lazily, but it is really only a small piece of the puzzle. Of course, the ModelBuilder configuration is often required anyway because it is where you perform any other mapping configuration for the query type. E.g. calling ToTable, ToColumn etc. In this way, Query Types are better than what we had in EF6 because they have more mapping capabilities, such as inheritance and outbound nav props.

You can use FromSql in the same way as with entity types. E.g. context.Query<OrderView>().FromSql("...").

[poke]

@anpete Just so I get this right: So modelBuilder.Query<T> is basically the same thing as modelBuilder.Entity<T> except that the configured type does not have the restrictions entities have (e.g. having a table and identity)? That’s great! :)

[samuelan]

The 2.1.0 does not seem to be available from the nuget.org prerelease source feed within Visual Studio 2017. Do I have to build from source if I want to try QueryType now?

[poke]

@samuelan Prerelease builds are only available on myget, as described here. So you should probably try the version from the aspnetcore-dev feed

[samuelan]

Thanks, @poke . Will try it out.

[emmielewis]

@anpete I don't see how this will work without having a baseline DBSet object that allows primary key. For me, this requirement should mean I can query a view as read only and not require a DBSet object to do so. I am unable to do that in the 2.1 version. I am working with existing database views and I am thinking I just don't understand the full functionality here. Any ideas?

[anpete]

@emmielewis Are you using the nightly builds? What is not working exactly?

[emmielewis]

@anpete - I am using build 2.1.0-preview2-t000. I looked at your example and set it up in a similar fashion but your example is using a DBSet object to build a query. It works when I do that. In my case, I have an existing view that I want to represent as an object and don't want to use the DBSet object (avoid primary key requirement) in order to create a query. How do I use DBQuery with an existing database view without any primary key requirements at all? Do I have to use a DBSet object to build this in EF?

[anpete]

@emmielewis You have hit a quirk of our APIs here 😄 You map a Query Type to a view by using ToTable. E.g. modelBuilder.Query<AT_DetailView>().ToTable("ccc.dddddd");

[emmielewis]

@anpete - Ok. I think I got confused from the example. I just saw your note above that says "ToTable" is a bit confusing. I am trying this out now.

[emmielewis]

@anpete - Thank you! That works great for me!

[anpete]

@emmielewis Hard to say. Is there a lot of data? It uses the same code paths as regular entity queries and so perf. should be the same.

[emmielewis]

@anpete - I got it resolved. This is working great. Thank you!

[emmielewis]

modelBuilder.Query().ToTable("TestView", "TestSchema") is not working in the https://www.nuget.org/packages/Microsoft.EntityFrameworkCore/2.1.0-preview2-final in Nuget. It looks like you all changed the name to ToView. Can you all update your documentation with this information?

[smitpatel]

@emmielewis - QueryType.ToTable API has been renamed to QueryType.ToView in 2.1.0-preview2

[emmielewis]

Yep. I just noticed that. Can you all update your documentation with this information? https://docs.microsoft.com/en-us/ef/core/modeling/query-types

[smitpatel]

cc: @divega

[divega]

@smitpatel, @emmielewis I created https://github.com/aspnet/EntityFramework.Docs/pull/682/.

[elrDevelopment]

I have had similar problems. I want to return a model or type that is not in the DBContext. So, I return it as Json from SQL 2016:

Add a table to the database with a key (int or guid) and a string value

public class JsonReturn
{
public int Key {get;set;}
public string Return {get; set;}
}

I then use that model with a FromSql statement
var dbReturn = _db.JsonReturn.FromSql($"dbo.Csp_StoredProc {param1}, {param2}").FirstorDefault();

then I parse the Json out into the ViewModel or DIO that I need.

List<T> lp = JsonConvert.DeserializeObject<List<T>>(json);

This would work with views as well would it not? Even an update statement.

Am I just looking at this to simply?