Hi,
Is it possible to write a linq to sql query that contains an entity string property (FullScarNumer) derived from another int property of the same entity (Scar number)
I would basically like to search for this FullScarNumber using the usual search by term and advance search fields. It seems that EF Core 3. 0 does not like this .
Entity properties
Search query. I tried String.Format also to see if it would work.
Is this possible with the new EF Core?
Error: ERROR 2020-04-28 11:25:47,036 [36 ] Mvc.ExceptionHandling.AbpExceptionFilter - The LINQ expression 'DbSet<Scar>
.Where(s => __ef_filter__p_0 || !(((ISoftDelete)s).IsDeleted))
.Join(
outer: DbSet<Category>,
inner: s => EF.Property<Nullable<int>>(s, "CategoryId"),
outerKeySelector: c => EF.Property<Nullable<int>>(c, "Id"),
innerKeySelector: (o, i) => new TransparentIdentifier<Scar, Category>(
Outer = o,
Inner = i
))
.LeftJoin(
outer: DbSet<ScarStatus>
.Where(s0 => __ef_filter__p_1 || !(((ISoftDelete)s0).IsDeleted)),
inner: s => EF.Property<Nullable<int>>(s.Outer, "StatusId"),
outerKeySelector: s0 => EF.Property<Nullable<int>>(s0, "Id"),
innerKeySelector: (o, i) => new TransparentIdentifier<TransparentIdentifier<Scar, Category>, ScarStatus>(
Outer = o,
Inner = i
))
.Where(s => False || s.Outer.Outer.Title.ToLower().Contains(__Trim_0) || string.Format(
format: "{0,00000}",
arg0: (object)s.Outer.Outer.ScarNumber).ToLower().Contains(__Trim_0) || s.Outer.Inner.Name.ToLower().Contains(__Trim_0) || s.Inner.Name.ToLower().Contains(__Trim_0))' 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.
System.InvalidOperationException: The LINQ expression 'DbSet<Scar>
.Where(s => __ef_filter__p_0 || !(((ISoftDelete)s).IsDeleted))
.Join(
outer: DbSet<Category>,
inner: s => EF.Property<Nullable<int>>(s, "CategoryId"),
outerKeySelector: c => EF.Property<Nullable<int>>(c, "Id"),
innerKeySelector: (o, i) => new TransparentIdentifier<Scar, Category>(
Outer = o,
Inner = i
))
.LeftJoin(
outer: DbSet<ScarStatus>
.Where(s0 => __ef_filter__p_1 || !(((ISoftDelete)s0).IsDeleted)),
inner: s => EF.Property<Nullable<int>>(s.Outer, "StatusId"),
outerKeySelector: s0 => EF.Property<Nullable<int>>(s0, "Id"),
innerKeySelector: (o, i) => new TransparentIdentifier<TransparentIdentifier<Scar, Category>, ScarStatus>(
Outer = o,
Inner = i
))
.Where(s => False || s.Outer.Outer.Title.ToLower().Contains(__Trim_0) || string.Format(
format: "{0,00000}",
arg0: (object)s.Outer.Outer.ScarNumber).ToLower().Contains(__Trim_0) || s.Outer.Inner.Name.ToLower().Contains(__Trim_0) || s.Inner.Name.ToLower().Contains(__Trim_0))' 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 Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.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__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.EntityFrameworkQueryableExtensions.ExecuteAsync[TSource,TResult](MethodInfo operatorMethodInfo, IQueryable1 source, Expression expression, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ExecuteAsync[TSource,TResult](MethodInfo operatorMethodInfo, IQueryable1 source, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.CountAsync[TSource](IQueryable`1 source, CancellationToken cancellationToken)
at Bowie.Framework.Portal.Scars.ScarsAppService.GetAll(GetAllScarsInput input) in C:\Users\Eamon\Source\Repos\SPG_QMS\Portal\aspnet-core\src\Bowie.Framework.Portal.Application\Scars\ScarsAppService.cs:line 101
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.
7 Answer(s)
-
0
I don't think EF Core can translate your expressions into SQL statements, and such expressions will not be supported in the future.
Mainly the
string.Formatmethod cannot be translated. -
0
This is what I was hoping I wouldn't hear. :( However, I think this is the case. EF Core 3.0 brought some breaking changes such as not allowing client side evaluation where it was unable to evaluate the where clause. Like this case.
If you have any workarounds it would be great, i would like to avoid saving the string property down to the database but this maybe the only option to search on it.
-
0
Hi,
Considering performance, I think it is better to store this information in database.
-
1
I think that's what we'll do. possibly let sql server generate it from the ScarNumber column.
-
0
Thanks for the feedback. Let us know if you face any problem.
-
0
Hi we now face this problem when running the test suite. All tests fail. "System.NotSupportedException : SQLite doesn't support computed columns."
Related to this I understand https://support.aspnetzero.com/QA/Questions/8404
-
0
Hi @jtallon
You can consider switching to sql server for your tests.
