Why this query return System.InvalidOperationException

Question

I want to count students based on some condition

return _context.Students.Where(x => 
          (x.Batch1 > 0 || x.Batch2 > 0) && 
           x.Received == 0 || 
           (DateTime.Now.Subtract(x.DateOfReg).Days >= 30 && 
          x.Received < 2000 && x.Payment >= 2000) || 
          (DateTime.Now.Subtract(x.DateOfReg).Days >= 120 && x.Due != 0)).Count();

but that return these errors -

System.InvalidOperationException: The LINQ expression 'DbSet<Student>()
    .Where(s => s.Batch1 > 0 || s.Batch2 > 0 && s.Received == 0 || DateTime.Now.Subtract(s.DateOfReg).Days >= 30 && s.Received < 2000 && s.Payment >= 2000 || DateTime.Now.Subtract(s.DateOfReg).Days >= 120 && s.Due != 0)' could not be translated. Additional information: Translation of method 'System.DateTime.Subtract' failed. If this method can be mapped to your custom function, see https://go.microsoft.com/fwlink/?linkid=2132413 for more information.
Translation of method 'System.DateTime.Subtract' failed. If this method can be mapped to your custom function, see https://go.microsoft.com/fwlink/?linkid=2132413 for more information.
Translation of method 'System.DateTime.Subtract' failed. If this method can be mapped to your custom function, see https://go.microsoft.com/fwlink/?linkid=2132413 for more information.
Translation of method 'System.DateTime.Subtract' failed. If this method can be mapped to your custom function, see https://go.microsoft.com/fwlink/?linkid=2132413 for more information. 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 System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.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.Query.Internal.QueryCompiler.<>c__DisplayClass9_0`1.<Execute>b__0()
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func`1 compiler)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.Execute[TResult](Expression query)
   at System.Linq.Queryable.Count[TSource](IQueryable`1 source)
   at StudentManagement.Controllers.StudentController.CountByRoutine(Int32 batch, Int32 payment) in C:\Users\Nazmul\Box\Project\.NET CORE\StudentManagement\Controllers\StudentController.cs:line 256
   at lambda_method370(Closure , Object , Object[] )

I don't know why this happened. My condition seems correct to me. What am I missing? Thank you.

Answer 1

It's failed because LINQ can't translate C# method DateTime.Subtract to equivalent function in SQL.

You have to use method like DiffDays() or similar from System.Data.Entity.DbFunctionswhich are fully mapped to sql ones.