Activities of "AlderCove"

Hi @mk2software

Your use case for Candidates isn't clear to me.

1. What is the relationship between a candidates and a customer/tenant?
2. Is a candidate for a specific customer/tenant? Or more than one tenant?
3. Should all candidates be managed within an instance of a single tenant (i.e. Candidate tenant)?
4. Is each candidate effectively its own tenant?

Can you describe your application a bit more to provide some more context.

Thanks Jamie

Hi @ismcagdas

It took a bit of refactoring, but we are all good.

Thanks

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

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

Hi maliming,

Here are the relevant bits.

Client:

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

        public List<ClientReferral> 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

ANZ 8.1 EF Core 3.1

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 @maliming

I haven't seen any issues yet with the zero built-in code, just some of our own more complex joins.

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

We integrated the SSRS report viewer with our Angular 7 Asp.Net core project. I'm not sure it would qualify as best practice, but it was a simple approach.

The approach used an iframe that referenced a local IIS website running a simple Asp.net application with the embedded report viewer control. This enabled reports to be run and viewed in the angular front end and required very little programming.

Our initial implementation utilized the SSRS web service to list the reports in the front end. We have since implemented a Report entity that holds the definition of the report, including a reference to the SSRS path and a permission name, so that it can be secured.