Cześć chciałbym sobie przefiltrować a następnie pobrać dane z bazy tak jak tu:
return predicate
.And(true, () => company => !String.IsNullOrEmpty(searchCriteria.Keyword) ? searchCriteria.Keyword.Contains(company.Name) : true)
.Or(true, () => company => !String.IsNullOrEmpty(searchCriteria.Keyword) ? company.Employees.Any(x => x.FirstName == searchCriteria.Keyword) : true)
.Or(true, () => company => !String.IsNullOrEmpty(searchCriteria.Keyword) ? company.Employees.Any(x => x.LastName == searchCriteria.Keyword) : true)
.And(true, () => company => searchCriteria.EmployeeDateOfBirthFrom != null ? company.Employees.Any(x => x.DateOfBirth >= searchCriteria.EmployeeDateOfBirthFrom) : true)
.And(true, () => company => searchCriteria.EmployeeDateOfBirthTo != null ? company.Employees.Any(x => x.DateOfBirth <= searchCriteria.EmployeeDateOfBirthTo) : true)
.And(true, () => company => searchCriteria.EmployeeJobTitles != null ? company.Employees.Any(x=> searchCriteria.EmployeeJobTitles.Any(s=> s == x.ToString())) : true);
Tutaj filtruje a tutaj pobieram:
return await dbContext.Companies.Include(x => x.Employees).Where(predicate).ToListAsync();
I teraz dlaczego mam taki error:
System.InvalidOperationException: The LINQ expression 'DbSet<Employee>
.Where(e => EF.Property<Nullable<long>>((EntityShaperExpression:
EntityType: Company
ValueBufferExpression:
(ProjectionBindingExpression: EmptyProjectionMember)
IsNullable: False
), "Id") != null && EF.Property<Nullable<long>>((EntityShaperExpression:
EntityType: Company
ValueBufferExpression:
(ProjectionBindingExpression: EmptyProjectionMember)
IsNullable: False
), "Id") == EF.Property<Nullable<long>>(e, "CompanyId"))
.Any(e => __searchCriteria_EmployeeJobTitles_0
.Contains(e.ToString()))' 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 either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync(). See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.
at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.<VisitMethodCall>g__CheckTranslated|8_0(ShapedQueryExpression translated, <>c__DisplayClass8_0& )
at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.TranslateSubquery(Expression expression)
at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.VisitBinary(BinaryExpression binaryExpression)
at Microsoft.EntityFrameworkCore.SqlServer.Query.Internal.SqlServerSqlTranslatingExpressionVisitor.VisitBinary(BinaryExpression binaryExpression)
at System.Linq.Expressions.BinaryExpression.Accept(ExpressionVisitor visitor)
at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.Translate(Expression expression)
at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.TranslateExpression(Expression expression)
at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.TranslateLambdaExpression(ShapedQueryExpression shapedQueryExpression, LambdaExpression lambdaExpression)
at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.TranslateWhere(ShapedQueryExpression source, LambdaExpression predicate)
at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
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_0`1.<ExecuteAsync>b__0()
at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQueryCore[TFunc](Object cacheKey, Func`1 compiler)
at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func`1 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.EntityQueryable`1.GetAsyncEnumerator(CancellationToken cancellationToken)
at System.Runtime.CompilerServices.ConfiguredCancelableAsyncEnumerable`1.GetAsyncEnumerator()
at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable`1 source, CancellationToken cancellationToken)
at Test.Repository.Implementations.CompanyRepository.GetAllAsync(Expression`1 predicate) in C:\Users\michal.warmuz\Desktop\Task\Test.API\Test.Data\Test.Repository\Implementations\CompanyRepository.cs:line 41
at Test.BusinessService.Implementations.CompanyBusinessService.GetAllAsync(CompanySearchCriteria query) in C:\Users\michal.warmuz\Desktop\Task\Test.API\Test.Application\Test.BusinessService\Implementations\CompanyBusinessService.cs:line 56
at Test.ApplicationService.Implementations.CompanyService.GetAllAsync(CompanySearchCriteria query) in C:\Users\michal.warmuz\Desktop\Task\Test.API\Test.Application\Test.ApplicationService\Implementations\CompanyService.cs:line 59
at Test.API.Controllers.CompanyController.<>c__DisplayClass2_0.<<SearchCompany>b__0>d.MoveNext() in C:\Users\michal.warmuz\Desktop\Task\Test.API\Test.API\Controllers\CompanyController.cs:line 25
--- End of stack trace from previous location where exception was thrown ---
at Test.API.Controllers.Base.BaseController.Handle(Func`1 func) in C:\Users\michal.warmuz\Desktop\Task\Test.API\Test.API\Controllers\Base\BaseController.cs:line 23
at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.TaskOfIActionResultExecutor.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.<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()
--- End of stack trace from previous location where exception was thrown ---
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.Authorization.AuthorizationMiddleware.Invoke(HttpContext context)
at Microsoft.AspNetCore.Authentication.AuthenticationMiddleware.Invoke(HttpContext context)
at Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware.Invoke(HttpContext context)
Wchodziłem w ten artykuł na dkoach i tam jest napisane że to przez to że te zapytanie jest mało wydajne i przez to EF Core je nie obsługuje czy dobrze zrozumiałem ? Jak można to naprawić ?
Z góry bardzo dziękuje za pomoc: