EF Core query #8442 | Support Center

AlderCove created 6 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)

0

maliming created 6 years ago
Support Team

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

Markdown is supported

Copy & paste or drag & drop images (max 30 MB per image)
0

AlderCove created 6 years ago

ANZ 8.1 EF Core 3.1

Markdown is supported

Copy & paste or drag & drop images (max 30 MB per image)
0

maliming created 6 years ago
Support Team

hi aldercove

Can you share classes of Clients and ClientReferrals entities?

Markdown is supported

Copy & paste or drag & drop images (max 30 MB per image)

AlderCove created 6 years ago

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

0

maliming created 6 years ago
Support Team

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

Markdown is supported

Copy & paste or drag & drop images (max 30 MB per image)

AlderCove created 6 years ago

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

0

maliming created 6 years ago
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

Markdown is supported

Copy & paste or drag & drop images (max 30 MB per image)
0

AlderCove created 6 years ago

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

Markdown is supported

Copy & paste or drag & drop images (max 30 MB per image)
0

jur.porras created 6 years ago

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

Markdown is supported

Copy & paste or drag & drop images (max 30 MB per image)

Have an answer to this question? Log in and write your answer.