Entity That Belongs To An Organization Unit - get items for Entity in the user's OUs including their child OUs #9920

Hi

We are using latest asp.net core jQuery.

i have an entity that belongs to an organization unit. I want to get all records related to the organizational units of the user and his children

We have tried below code as explained here https://aspnetboilerplate.com/Pages/Documents/Zero/Organization-Units

	    [UnitOfWork]
		public virtual async Task<List<Colaborador>> GetColaboradoresForUserIncludingChildOusAsync()
		{
			var user = await _userManager.GetUserByIdAsync((long)AbpSession.UserId);
			var organizationUnits = await _userManager.GetOrganizationUnitsAsync(user);
			var organizationUnitCodes = organizationUnits.Select(ou => ou.Code).ToList();			

			var filteredColaboradores = from colaborador in _colaboradorRepository.GetAll()
						join organizationUnit in _lookup_organizationUnitRepository.GetAll() on colaborador.OrganizationUnitId equals organizationUnit.Id
						where organizationUnitCodes.Any(code => organizationUnit.Code.StartsWith(code))						
						select colaborador;

			return await filteredColaboradores.ToListAsync();
		}

And getting this error

System.InvalidOperationException: The LINQ expression 'DbSet<Colaborador>() .Where(c => __ef_filter__p_0 || !(((ISoftDelete)c).IsDeleted) && __ef_filter__p_1 || (Nullable<int>)((IMustHaveTenant)c).TenantId == __ef_filter__CurrentTenantId_2) .Join( inner: DbSet<OrganizationUnit>() .Where(o => __ef_filter__p_3 || !(((ISoftDelete)o).IsDeleted) && __ef_filter__p_4 || ((IMayHaveTenant)o).TenantId == __ef_filter__CurrentTenantId_5), outerKeySelector: c => c.OrganizationUnitId, innerKeySelector: o => o.Id, resultSelector: (c, o) => new TransparentIdentifier<Colaborador, OrganizationUnit>( Outer = c, Inner = o )) .Where(ti => __organizationUnitCodes_0 .Any(code => code == "" || ti.Inner.Code != null && code != null && ti.Inner.Code.StartsWith(code)))' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information. at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.<VisitMethodCall>g__CheckTranslated|15_0(ShapedQueryExpression translated, <>c__DisplayClass15_0& ) at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression) at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression) 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__DisplayClass9_01.<Execute>b__0() at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func1 compiler) at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.Execute[TResult](Expression query) at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable1.GetEnumerator() at System.Collections.Generic.List1..ctor(IEnumerable1 collection) at System.Linq.Enumerable.ToList[TSource](IEnumerable1 source) at Tecsmart.Epm.Colaboradores.ColaboradoresAppService.GetAllForLoadResult(DataSourceLoadOptions loadOptions) in C:\Desenvolvimento\epm-web\aspnet-core\src\Tecsmart.Epm.Application\Colaboradores\ColaboradoresAppService.cs:line 333 at Abp.Authorization.AuthorizationInterceptor.InternalInterceptAsynchronous[TResult](IInvocation invocation) at Abp.Domain.Uow.UnitOfWorkInterceptor.InternalInterceptAsynchronous[TResult](IInvocation invocation) at Abp.Domain.Uow.UnitOfWorkInterceptor.InternalInterceptAsynchronous[TResult](IInvocation invocation) at Abp.Auditing.AuditingInterceptor.InternalInterceptAsynchronous[TResult](IInvocation invocation) at Abp.Auditing.AuditingInterceptor.InternalInterceptAsynchronous[TResult](IInvocation invocation) at Abp.Runtime.Validation.Interception.ValidationInterceptor.InternalInterceptAsynchronous[TResult](IInvocation invocation) at Tecsmart.Epm.Web.Areas.App.Controllers.ColaboradoresController.GetAllColaboradores(DataSourceLoadOptions loadOptions) in C:\Desenvolvimento\epm-web\aspnet-core\src\Tecsmart.Epm.Web.Mvc\Areas\App\Controllers\ColaboradoresController.cs:line 64 at lambda_method2847(Closure , Object ) at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.AwaitableObjectResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments) at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeActionMethodAsync>g__Logged|12_1(ControllerActionInvoker invoker) at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeNextActionFilterAsync>g__Awaited|10_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted) at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Rethrow(ActionExecutedContextSealed context) at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted) at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeInnerFilterAsync>g__Awaited|13_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted) at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeNextExceptionFilterAsync>g__Awaited|25_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted) at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Rethrow(ExceptionContextSealed context) at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted) at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeNextResourceFilter>g__Awaited|24_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted) at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Rethrow(ResourceExecutedContextSealed context) at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted) at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeFilterPipelineAsync>g__Awaited|19_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted) at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Logged|17_1(ResourceInvoker invoker) at Microsoft.AspNetCore.Routing.EndpointMiddleware.<Invoke>g__AwaitRequestTask|6_0(Endpoint endpoint, Task requestTask, ILogger logger) at Microsoft.AspNetCore.Localization.RequestLocalizationMiddleware.Invoke(HttpContext context) at Microsoft.AspNetCore.Authorization.Policy.AuthorizationMiddlewareResultHandler.HandleAsync(RequestDelegate next, HttpContext context, AuthorizationPolicy policy, PolicyAuthorizationResult authorizeResult) at Microsoft.AspNetCore.Authorization.AuthorizationMiddleware.Invoke(HttpContext context) at IdentityServer4.Hosting.IdentityServerMiddleware.Invoke(HttpContext context, IEndpointRouter router, IUserSession session, IEventService events, IBackChannelLogoutService backChannelLogoutService) at IdentityServer4.Hosting.MutualTlsEndpointMiddleware.Invoke(HttpContext context, IAuthenticationSchemeProvider schemes) at Microsoft.AspNetCore.Authentication.AuthenticationMiddleware.Invoke(HttpContext context) at IdentityServer4.Hosting.BaseUrlMiddleware.Invoke(HttpContext context) at Abp.AspNetZeroCore.Web.Authentication.JwtBearer.JwtTokenMiddleware.<>c__DisplayClass0_0.<<UseJwtTokenMiddleware>b__0>d.MoveNext() --- End of stack trace from previous location --- at Abp.AspNetZeroCore.Web.Authentication.JwtBearer.JwtTokenMiddleware.<>c__DisplayClass0_0.<<UseJwtTokenMiddleware>b__0>d.MoveNext() --- End of stack trace from previous location --- at Microsoft.AspNetCore.Authentication.AuthenticationMiddleware.Invoke(HttpContext context) at Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware.Invoke(HttpContext context) HEADERS ======= Accept: application/json, text/javascript, /; q=0.01 Accept-Encoding: gzip, deflate, br Accept-Language: pt-BR,pt;q=0.9,en;q=0.8,en-GB;q=0.7,en-US;q=0.6 Cache-Control: no-cache Connection: close Cookie: cookieconsent_status=dismiss; Abp.TenantId=2; .AspNetCore.Antiforgery.ZdUzRfrtN3M=CfDJ8K4i4vTg1sRJnzUwhyiExud2nGq0961iaK0mgg1SmMEJiYYBD8LhtorMEKBsYpYXZ_cvQZCFogWBdrx6Mf4cYWVpCfyIcHa7gfAQ6QdsRne7U5H4Ekqc_B3DmURBASA-woxBBhc76fiy9X82wrtLKGU; idsrv.session=52FB02639B6D413739FB8F0E21A947A7; XSRF-TOKEN=CfDJ8K4i4vTg1sRJnzUwhyiExufF4E7Nzp-78DGnn4v8StigUCzRsy-LOwwvIxhlsI_dMQk5mQU8ryrEPyGtj5LqsFsKmIMcfl-KRO79HoXHpuvHK-yOA9deYlsdG-t850hmBv7NGBkUV4t8PEDQPvQ813lshKSKo2lHqkP5Xp_JqV8ruHUeRAqttaLWr-ZkzWRcHQ; .AspNetCore.Identity.Application=CfDJ8K4i4vTg1sRJnzUwhyiExucjIx7zn0gnokcX44K7wIvbZBvZeXomMbper6A-mm0OjlSaZe9doP6r-JN10HLRlWCFfO89Dr4a7RXrvgcPW73DYHWsRnt34j4dmGKr7uPPNNHQiuxWfZ7tP3qtgUxlEB1GYJr0PZCFrPlOwoE3G0WEegnb-dbAxn_pV3rCpuU7WFLJkB6wbOlhzM54c6GtLm0Zh6Hcs9PHvV5SLtU6-wn55CdfqWtW3I_Eoe5NyRRctyBLpr6Tjy7tmvytNwirBvXpjokWRRTPhGhygJQlHiYUJZKPQbaY6M1oc48NYdMEk9v97AMm444zcHhEZKL0cNeRDA5XojbAtkienHqA48-nBMtTp5GQd82dJwJ4NBipTN26CY2XF3PdPXrn_qF-Bw2JL7EQ1O9d51J15MNPQ3_SmTi6w234GyN_UmLnLGTnzUWdlPJjUwulVnVFOTkJjvJF_fi61e3Hq-DrxehMRH6Yzp_RfWPuXyAQcMHFvSxDWrA7RdsgUz3GaXm0nklNPsrL_PW9aoeRzQYyqIuYlnOnG6m2yr7bhCzNfFL0KGI83XMWAj80YUfAKjI0CLpki2fOkm3CHcZHhSsetLyxsRf2iGCyESeB9DKPl08YvkueRXO1Q6Obk-wYXAxJ0Ft-FNu5_9NNPnE6WsnNRrxhDn34Jl3mP80eHCmxd4FerRhRVZovVaR04QhrValtV3fwHqwrQjOFAsLMAOfqk9KGLMEm4clisL4UbyKMXhNvRhfD02vteWXl-yTppmxoL7w30hf8M1Qww7l5jfxx1F0v38J4TppFNy3euQ2_IsDis6JaOa-49w0I-Yq-v1s7Hom4Yew7AiUL87fRT3ZqzJsC0Fqv0bPD9bykP9Cy3B6AWucTrt_XADTIH9RWc8Dnoh7IROQVEVHjCTOzcQuJ-C_ZbaBGRbrpq6Yqj8m-yIjWuwgAtTvNQy3RETQs_abzxjd9bVWblffFSNWsGU_8XslhapetmIkydSGQw9L8vMg5TL43hMYx6OAxCa8pmAidYTdTOnwF6r5QbUEQTysfGOzJbYdi8nGSD81Jx-qpWgzzGqW_tArIggPdczAn9Xo2hmilvnI; kt_aside_menu=33.599998474121094 Host: localhost:44302 Pragma: no-cache Referer: https://localhost:44302/App/Colaboradores User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/87.0.4280.88 Safari/537.36 Edg/87.0.664.60 x-xsrf-token: CfDJ8K4i4vTg1sRJnzUwhyiExufF4E7Nzp-78DGnn4v8StigUCzRsy-LOwwvIxhlsI_dMQk5mQU8ryrEPyGtj5LqsFsKmIMcfl-KRO79HoXHpuvHK-yOA9deYlsdG-t850hmBv7NGBkUV4t8PEDQPvQ813lshKSKo2lHqkP5Xp_JqV8ruHUeRAqttaLWr-ZkzWRcHQ x-requested-with: XMLHttpRequest sec-fetch-site: same-origin sec-fetch-mode: cors sec-fetch-dest: empty