Base solution for your next web application
Open Closed

EF Core query #8442

User avatar
0
AlderCove created

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

Go to accepted answer
9 Answer(s)
  • User Avatar
    0
    maliming created
    Support Team

    What is your zero version? What is your ef core version?

  • User Avatar
    0
    AlderCove created

    ANZ 8.1 EF Core 3.1

  • User Avatar
    0
    maliming created
    Support Team

    hi aldercove

    Can you share classes of Clients and ClientReferrals entities?

  • User Avatar
    0
    AlderCove created

    Hi maliming,

    Here are the relevant bits.

    Client:

        public class Client : FullAuditedEntity
    {
        public string Name { get; set; }

        public List&lt;ClientReferral&gt; Referrals { get; set; }
    }

    Client Referral:

        public class ClientReferral : FullAuditedEntity
    {
        public int ClientId { get; set; }

        public Client Client { get; set; }

        public int ReferralStatusTypeId { get; set; }

        public ReferralStatusType ReferralStatusType { get; set; }
    }

    Referral Status Type:

        public class ReferralStatusType : FullAuditedEntity
    {
        public string Code { get; set; }

        public string Name { get; set; }
    }

    Thanks Jamie

  • User Avatar
    0
    maliming created
    Support Team

    hi Is _context.Clients() a method? please share it code.

  • User Avatar
    0
    AlderCove created

    Sorry, should have read _context.Clients.

    I have it working now by grouping after the query is executed on the database.

            var query = _clientRepository.GetAll().Select(g => _clientReferralRepository.GetAll()
                                                                                    .OrderByDescending(p => p.Id)
                                                                                    .FirstOrDefault(p => p.ClientId == g.Id));

        var results = (await query.ToListAsync()).GroupBy(g => g.ReferralStatusTypeId)
                                    .Select(g => new
                                    {
                                        ReferralStatusTypeId = g.Key,
                                        ClientCount = g.Count()
                                    }).ToList();

    Ideally, the grouping could be done on the database side rather than in memory.

    Any thoughts?

    Thanks

  • User Avatar
    0
    maliming created
    Support Team

    As far as I know, there are some problems with the GroupBy method of ef core 3.x. You can group in memory.

    https://github.com/dotnet/efcore/issues?utf8=%E2%9C%93&q=is%3Aissue+is%3Aopen+GroupBy

  • User Avatar
    0
    AlderCove created

    Thanks for your help @maliming, I'll do that and follow the thread.

  • User Avatar
    0
    jur.porras created

    We just had updated our aspnet zero into 8.2

    After the update, majority of our Linq does not work anymore. Like said above EF 3.0 is the caused of it.

    Do you had a recommended approach/workaround on those query with GroupBy.?

Assignee
No one
Labels
None yet