Hi
I am trying to write a query to report a count of Clients grouped by the status of the Clients' most recent Referral. Each client Can have one or more Referrals.
In EF Core, quering from DBContext this can be achieved with a statement like this:
var query = _context.Clients()
.Select(g => _context.ClientReferrals
.OrderByDescending(p => p.Id)
.FirstOrDefault(p => p.ClientId == g.Id))
.GroupBy(g => g.ReferralStatusTypeId)
.Select(g => new
{
ReferralStatusTypeId = g.Key,
ClientCount = g.Count()
});
Which produces a corresponding SQL something similar to this:
SELECT [t0].ReferralStatusTypeId, ClientCount = count(*)
FROM
Client AS p
LEFT JOIN
(
SELECT *
FROM
(
SELECT *, ROW_NUMBER() OVER(PARTITION BY p0.ClientId ORDER BY p0.Id DESC) AS row
FROM [ClientReferral] AS [p0]
) AS [t]
WHERE [t].[row] <= 1
) AS t0 ON p.Id = t0.ClientId
group by [t0].ReferralStatusTypeId
How can I achieve the same result querying from the repositories ?
I tried this:
var query = _clientRepository.GetAll()
.Select(g => _clientReferralRepository.GetAll()
.OrderByDescending(p => p.Id)
.FirstOrDefault(p => p.ClientId == g.Id))
.GroupBy(g => g.ReferralStatusTypeId)
.Select(g => new
{
ReferralStatusTypeId = g.Key,
ClientCount = g.Count()
});
But get this stack trace:
System.InvalidCastException: Unable to cast object of type 'Microsoft.EntityFrameworkCore.Query.SqlExpressions.ScalarSubqueryExpression' to type 'System.Linq.Expressions.ConstantExpression'.
at Microsoft.EntityFrameworkCore.Query.RelationalShapedQueryCompilingExpressionVisitor.RelationalProjectionBindingRemovingExpressionVisitor.GetProjectionIndex(ProjectionBindingExpression projectionBindingExpression)
at Microsoft.EntityFrameworkCore.Query.RelationalShapedQueryCompilingExpressionVisitor.RelationalProjectionBindingRemovingExpressionVisitor.VisitExtension(Expression extensionExpression)
at System.Linq.Expressions.ExpressionVisitor.VisitBinary(BinaryExpression node)
at System.Dynamic.Utils.ExpressionVisitorUtils.VisitBlockExpressions(ExpressionVisitor visitor, BlockExpression block)
at System.Linq.Expressions.ExpressionVisitor.VisitBlock(BlockExpression node)
at System.Linq.Expressions.ExpressionVisitor.VisitLambda[T](Expression1 node) at Microsoft.EntityFrameworkCore.Query.RelationalShapedQueryCompilingExpressionVisitor.VisitShapedQueryExpression(ShapedQueryExpression shapedQueryExpression) at Microsoft.EntityFrameworkCore.Query.ShapedQueryCompilingExpressionVisitor.VisitExtension(Expression extensionExpression) at Microsoft.EntityFrameworkCore.Query.QueryCompilationContext.CreateQueryExecutor[TResult](Expression query) at Microsoft.EntityFrameworkCore.Storage.Database.CompileQuery[TResult](Expression query, Boolean async) at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileQueryCore[TResult](IDatabase database, Expression query, IModel model, Boolean async) at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass12_01.<ExecuteAsync>b__0()
at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQueryCore[TFunc](Object cacheKey, Func1 compiler) at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.ExecuteAsync[TResult](Expression query, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.ExecuteAsync[TResult](Expression expression, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable1.GetAsyncEnumerator(CancellationToken cancellationToken)
at System.Runtime.CompilerServices.ConfiguredCancelableAsyncEnumerable1.GetAsyncEnumerator() at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable1 source, CancellationToken cancellationToken)
at CTS.Tenants.Dashboard.TenantDashboardAppService.GetClientSummary() in C:\Users\Jamie\Source\Repos\CTS_8.1.0\CTS\aspnet-core\src\CTS.Application\Tenants\Dashboard\TenantDashboardAppService.cs:line 137
at lambda_method(Closure , Object )
at Microsoft.Extensions.Internal.ObjectMethodExecutorAwaitable.Awaiter.GetResult()
at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.AwaitableObjectResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments)
at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeActionMethodAsync>g__Awaited|12_0(ControllerActionInvoker invoker, ValueTask`1 actionResultValueTask)
at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.
Thanks Jamie
Hi
Just a heads up for anyone moving to EF Core 3.0, you might need to refactor some of your queries that use Include for 1:N relations due to performance issues. I first noticed this issue with a query that went from sub second response time to >15s response time.
With EF 2.2, fetching queryable resulted in multiple queries. In EF Core 3.0, a single query is generated, with many joins, which can result in a "Cartesian Explosion".
The solution appears to be to split the original query up into multiple queries and let EF fixup the relations. Tracking must be done to accomplish this, so AsNoTracking() cannot be used with this approach.
Appreciate if the support team has any info/recommendations for handling this scenario that they are able to share.
Jamie
According to MS: https://docs.microsoft.com/en-us/ef/core/querying/related-data Caution
Since version 3.0.0, each Include will cause an additional JOIN to be added to SQL queries produced by relational providers, whereas previous versions generated additional SQL queries. This can significantly change the performance of your queries, for better or worse. In particular, LINQ queries with an exceedingly high number of Include operators may need to be broken down into multiple separate LINQ queries in order to avoid the cartesian explosion problem.
Additional references:
Significant Query Slowdown When Using Multiple Joins Due To Changes In 3.0 #18022 https://github.com/dotnet/efcore/issues/18022
20x slowdown in gigantic query after updating to EF Core 3 compared to 2.2 https://github.com/dotnet/efcore/issues/18017
EF Core 3.0 .Include does not work as expected and Super Slow https://stackoverflow.com/questions/58677119/ef-core-3-0-include-does-not-work-as-expected-and-super-slow
Hi
I have a requirement to support enquries & reports for complex derived data.
I would not typically store this info but due to performance issues around deriving all of this data on the fly, I have decided to persist the data.
My initial approach was to create some event handlers for entity create/update/delete and derive the data on these events. I don't want to impact the end-user performance and thought it might be beneficial to derive this data using a background job and I still want the entity event handlers to trigger the updates,
In reviewing how the background job is used for other purposes (i.e. UserCollectedDataPrepareJob), the Background job is defined in the application service layer. However, this project is not visible (understandbly) from the Domain Service layer in which the event handlers are defined and so I am unable to enqueue the background job.
Is it advisable to create the background job in the domain layer? It seems to work OK but perhaps its not best practice? Is there another suggested approach? I don't want to cause any issues by setting the background job up in the domain layer and appreciate any guidance you can provide around my approach.
I suppose one obvious solution would be to write the events to a new table that is then polled by a background worker to process the entries but it would be nice to leverage the existing event data/worker approach.
Kind Regards Jamie
Hi
I realise this probably isn't the right forum but its probably the best one.
We are not necessarily looking for developers but a solution.
Does anyone have an ASP.NET Zero Job Board/Matching Solution (or similar solution) or experience with developing one?
If so, please contact me.
Thanks Jamie [email protected]
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:
CREATE VIEW [dbo].[vwMyView]
AS
SELECT Id FROM Clients
public class MyQuery : IEntity<int>
{
public int Id { get; set; }
public bool IsTransient()
{
return true;
}
}
public virtual DbQuery<MyQuery> MyQueries { get; set; }
modelBuilder.Query<MyQuery>().ToView("vwMyView");
public class MyQueryAppService : MyAppServiceBase
{
private readonly IRepository<MyQuery, int> _myQueryRepository;
public MyQueryAppService(IRepository<MyQuery, int> myQueryRepository)
{
_myQueryRepository = myQueryRepository;
}
public async Task<PagedResultDto<MyQueryDto>> GetMyQuery()
{
...
}
}
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
Hello, Just wondering if anyone knows how to install the gdiplus.dll library in the windows server core docker container (more specifically the microsoft/aspnetcore container)?
This library is required to export the excel spreadsheet for the Audit logs. I have been able to install this on linux with the apt-get however I don't seem to be having success on windows.
Note that the windows server core docker container does not have a GUI, it is terminal only.
Thanks.
Hello, I am trying to get the Timezones working on Abp when running on a Linux system (MacOS High Sierra and aspnetcore linux docker image [https://hub.docker.com/r/microsoft/aspnetcore/] latest for LInux amd64).
I am able to select a time zone from the dropdown list (under the /app/admin/tenantSettings), however once any time zone is selected I am no longer able to load the application.
As you can see in the logs it tries to convert America/Guatemala to an Iana timezone (which is odd since this is not a Windows timezone). If I put in a Windows Time Zone Id instead it tried to load it from the system which fails since it is not running on Windows.
Also note that the entires in the Timezone combobox appear to duplicate (since it is only showing the DisplayText which does not include the Value). Ie:
Default [UTC]
Hawaii-Aleutian Standard Time
Hawaii-Aleutian Standard Time
Marquesas Time
Alaska Standard Time
Pacific Standard Time
Pacific Standard Time
....
Error in logs:
NFO 2018-03-28 13:38:42,951 [4 ] soft.AspNetCore.Hosting.Internal.WebHost - Request starting HTTP/1.1 GET http://localhost:22742/AbpUserConfiguration/GetAll application/json
INFO 2018-03-28 13:38:42,951 [4 ] pNetCore.Cors.Infrastructure.CorsService - Policy execution successful.
INFO 2018-03-28 13:38:42,952 [4 ] uthentication.JwtBearer.JwtBearerHandler - Successfully validated the token.
INFO 2018-03-28 13:38:42,953 [4 ] uthentication.JwtBearer.JwtBearerHandler - AuthenticationScheme: Bearer was successfully authenticated.
INFO 2018-03-28 13:38:42,963 [4 ] pNetCore.Cors.Infrastructure.CorsService - Policy execution successful.
INFO 2018-03-28 13:38:42,965 [4 ] ore.Mvc.Internal.ControllerActionInvoker - Executing action method Abp.AspNetCore.Mvc.Controllers.AbpUserConfigurationController.GetAll (Abp.AspNetCore) with arguments ((null)) - ModelState is Valid
ERROR 2018-03-28 13:38:43,642 [93 ] Mvc.ExceptionHandling.AbpExceptionFilter - Unable to map America/Guatemala to iana timezone.
System.Exception: Unable to map America/Guatemala to iana timezone.
at Abp.Timing.Timezone.TimezoneHelper.WindowsToIana(String windowsTimezoneId) in D:\Github\aspnetboilerplate\src\Abp\Timing\Timezone\TimezoneHelper.cs:line 44
at Abp.Web.Configuration.AbpUserConfigurationBuilder.<GetUserTimingConfig>d__22.MoveNext() in D:\Github\aspnetboilerplate\src\Abp.Web.Common\Web\Configuration\AbpUserConfigurationBuilder.cs:line 243
--- 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 Abp.Web.Configuration.AbpUserConfigurationBuilder.<GetAll>d__13.MoveNext() in D:\Github\aspnetboilerplate\src\Abp.Web.Common\Web\Configuration\AbpUserConfigurationBuilder.cs:line 68
--- 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 Abp.AspNetCore.Mvc.Controllers.AbpUserConfigurationController.<GetAll>d__2.MoveNext() in D:\Github\aspnetboilerplate\src\Abp.AspNetCore\AspNetCore\Mvc\Controllers\AbpUserConfigurationController.cs:line18
--- 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 lambda_method(Closure , Object )
at Microsoft.Extensions.Internal.ObjectMethodExecutorAwaitable.Awaiter.GetResult()
at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.<InvokeActionMethodAsync>d__12.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.ControllerActionInvoker.<InvokeNextActionFilterAsync>d__10.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.Rethrow(ActionExecutedContext context)
at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.<InvokeInnerFilterAsync>d__14.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__23.MoveNext()
INFO 2018-03-28 13:38:43,657 [93 ] etCore.Mvc.Internal.ObjectResultExecutor - Executing ObjectResult, writing value Microsoft.AspNetCore.Mvc.ControllerContext.
INFO 2018-03-28 13:38:43,661 [93 ] ore.Mvc.Internal.ControllerActionInvoker - Executed action Abp.AspNetCore.Mvc.Controllers.AbpUserConfigurationController.GetAll (Abp.AspNetCore) in 697.844ms
INFO 2018-03-28 13:38:43,671 [93 ] soft.AspNetCore.Hosting.Internal.WebHost - Request finished in 720.002ms 500 application/json; charset=utf-8
Abp Nuget Package Versions: 3.3.0
Hi,
Please help me to extend my license. I have a developer who is dead in the water because I am unable to upgrade my license through the website.
When I click the link to upgrade I get the following message: 500 ERROR! You must have a Discount Code !
I have the regular discount license and need more than three developer seats.
I know this isn't the best place to be asking for help but I've emailed the support address a couple of times starting two days ago and have not heard anything back yet.
Please help me get this sorted.
Many thanks Jamie
I'm loving the product by the way. We just want to be able to use it more!