EF Core query #8442

AlderCove created 4 years ago

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] &lt;= 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 =&gt; _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

9 Answer(s)