Hi
I am trying to utilize a Query Type (vs. Entity Type) to query the database using a view. I am having an issue with injecting the domain repository into the application service. I was hoping that I could treat the Query Type just as though it was a domain entity and perhaps that's not possible and I need to reconsider my approach.
These are the basic steps I followed:
- Created view "vwMyView" on the database (note: a simple view used as example for simplicity).
CREATE VIEW [dbo].[vwMyView]
AS
SELECT Id FROM Clients
- Created a corresponding query domain class:
public class MyQuery : IEntity<int>
{
public int Id { get; set; }
public bool IsTransient()
{
return true;
}
}
- Added DBQuery property in my DBContext (tried with both DbQuery + OnModelCreating):
public virtual DbQuery<MyQuery> MyQueries { get; set; }
modelBuilder.Query<MyQuery>().ToView("vwMyView");
- Created my application service class:
public class MyQueryAppService : MyAppServiceBase
{
private readonly IRepository<MyQuery, int> _myQueryRepository;
public MyQueryAppService(IRepository<MyQuery, int> myQueryRepository)
{
_myQueryRepository = myQueryRepository;
}
public async Task<PagedResultDto<MyQueryDto>> GetMyQuery()
{
...
}
}
- When I call the service, I get the exception:
ERROR 2018-08-31 16:13:34,156 [6 ] Mvc.ExceptionHandling.AbpExceptionFilter - Can't create component 'MyApp.MyQueries.MyQueriesAppService' as it has dependencies to be satisfied.
'MyApp.MyQueries.MyQueriesAppService' is waiting for the following dependencies:
- Service 'Abp.Domain.Repositories.IRepository`2[[MyApp.MyQueries.MyQuery, MyApp.Core, Version=5.0.0.0, Culture=neutral, PublicKeyToken=null],[System.Int32, System.Private.CoreLib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e]]' which was not registered.
Castle.MicroKernel.Handlers.HandlerException: Can't create component 'MyApp.MyQueries.MyQueriesAppService' as it has dependencies to be satisfied.
'MyApp.MyQueries.MyQueriesAppService' is waiting for the following dependencies:
- Service 'Abp.Domain.Repositories.IRepository`2[[MyApp.MyQueries.MyQuery, MyApp.Core, Version=5.0.0.0, Culture=neutral, PublicKeyToken=null],[System.Int32, System.Private.CoreLib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e]]' which was not registered.
at Castle.MicroKernel.Handlers.DefaultHandler.AssertNotWaitingForDependency()
at Castle.MicroKernel.Handlers.DefaultHandler.ResolveCore(CreationContext context, Boolean requiresDecommission, Boolean instanceRequired, Burden& burden)
at Castle.MicroKernel.Handlers.DefaultHandler.Resolve(CreationContext context, Boolean instanceRequired)
at Castle.MicroKernel.DefaultKernel.ResolveComponent(IHandler handler, Type service, IDictionary additionalArguments, IReleasePolicy policy)
at Castle.MicroKernel.DefaultKernel.Castle.MicroKernel.IKernelInternal.Resolve(Type service, IDictionary arguments, IReleasePolicy policy)
at Castle.Windsor.MsDependencyInjection.ScopedWindsorServiceProvider.GetServiceInternal(Type serviceType, Boolean isOptional) in D:\Github\castle-windsor-ms-adapter\src\Castle.Windsor.MsDependencyInjection\ScopedWindsorServiceProvider.cs:line 55
at Microsoft.AspNetCore.Mvc.Controllers.ControllerFactoryProvider.<>c__DisplayClass5_0.<CreateControllerFactory>g__CreateController|0(ControllerContext controllerContext)
at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.<InvokeInnerFilterAsync>d__13.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Microsoft.AspNetCore.Mvc.Internal.ResourceInvoker.<InvokeNextExceptionFilterAsync>d__24.MoveNext()
Perhaps I can't use the Abp Repository with Query Types and need to create my own Custom Repository?
Here are some related links: <a class="postlink" href="https://docs.microsoft.com/en-us/ef/core/modeling/query-types">https://docs.microsoft.com/en-us/ef/cor ... uery-types</a> <a class="postlink" href="https://msdn.microsoft.com/en-us/magazine/mt847184.aspx">https://msdn.microsoft.com/en-us/magazine/mt847184.aspx</a>
Any assistance appreciated.
Thanks Jamie ASP.NET Core & Angular ASP.NET Zero 5.5.0
6 Answer(s)
-
0
ABP registers repositories based on DbSets. Try:
public virtual DbSet<MyQuery> MyQueries { get; set; } -
0
Hi Aaron
Thank you for your response.
If I use DbSet, EF core will try and generate a table when I add the next migration. Since I am trying to query a view, I want to avoid that.
It looks like EF Core 2.1 supports DbQuery for Views along with the model builder .Query<>().ToView(). Do you know if there is support for these techniques with ASP.NET Zero or ABP ? Or plans to add support?
Or, do I need to use a custom repository to query from a view? Or is there another recommended approach - like manually registering the view model to be injected into the application service?
Thanks Jamie
-
0
hey! you can take a look at this article which explains how to execute a view <a class="postlink" href="https://aspnetboilerplate.com/Pages/Documents/Articles/Using-Stored-Procedures,-User-Defined-Functions-and-Views/index.html">https://aspnetboilerplate.com/Pages/Doc ... index.html</a>
-
0
Thanks, I've got it working through a custom repository.
I was wishfully thinking that I might be able to use a DbQuery in my DbContext and the Fluent API toView method to map my query type to a view.
After a bit more digging, it looks like support would need to be added to the ABP & ASP.NET Zero frameworks. I'm not sure exactly how that would look but perhaps a new repository interface or support added for the existing IRepository.
I think this would greatly simplify the use of views, which could also then be incorporated into EF Core & LINQ queries and allow the results to be mapped to domain or view classes.
-
0
AspNet Zero uses Entity Framework which is a wide spectrum object-relational mapping framework. A view is a custom option for Microsoft SQL Server. So there's no View thing in all databases. That's why it should be custom development for the end-developer. For your case the custom repository is the way to go! Thanks for the feedback ;)
-
1
I get that a view is custom option for Microsoft SQL server but the EF Query Type (vs. Entity Type) is not designed specifically for views, it also supports queries from tables with no need for a unique entity id.
Here's a summary: <a class="postlink" href="https://docs.microsoft.com/en-us/ef/core/modeling/query-types">https://docs.microsoft.com/en-us/ef/cor ... uery-types</a>
Compare query types to entity types Query types are like entity types in that they:
Can be added to the model either in OnModelCreating or via a "set" property on a derived DbContext. Support many of the same mapping capabilities, like inheritance mapping and navigation properties. On relational stores, they can configure the target database objects and columns via fluent API methods or data annotations. However, they are different from entity types in that they:
Do not require a key to be defined. Are never tracked for changes on the DbContext and therefore are never inserted, updated or deleted on the database. Are never discovered by convention. Only support a subset of navigation mapping capabilities - Specifically: They may never act as the principal end of a relationship. They can only contain reference navigation properties pointing to entities. Entities cannot contain navigation properties to query types. Are addressed on the ModelBuilder using the Query method rather than the Entity method. Are mapped on the DbContext through properties of type DbQuery<T> rather than DbSet<T> Are mapped to database objects using the ToView method, rather than ToTable. May be mapped to a defining query - A defining query is a secondary query declared in the model that acts a data source for a query type. Usage scenarios
Some of the main usage scenarios for query types are:
Serving as the return type for ad hoc FromSql() queries. Mapping to database views. Mapping to tables that do not have a primary key defined. Mapping to queries defined in the model.
Mapping to database objects Mapping a query type to a database object is achieved using the ToView fluent API. From the perspective of EF Core, the database object specified in this method is a view, meaning that it is treated as a read-only query source and cannot be the target of update, insert or delete operations. <ins>However, this does not mean that the database object is actually required to be a database view - It can alternatively be a database table that will be treated as read-only</ins>. Conversely, for entity types, EF Core assumes that a database object specified in the ToTable method can be treated as a table, meaning that it can be used as a query source but also targeted by update, delete and insert operations. In fact, you can specify the name of a database view in ToTable and everything should work fine as long as the view is configured to be updatable on the database.
So, I think EF Query Type is an EF feature and not specific to SQL server and seems pretty wide-spectrum to me.
Jamie
