| Bug #106520 | InvalidOperationException in SqlNullabilityProcessor when using StartsWith | ||
|---|---|---|---|
| Submitted: | 20 Feb 2022 8:10 | Modified: | 29 Apr 2022 10:45 |
| Reporter: | aliuddin abd rauf | Email Updates: | |
| Status: | Duplicate | Impact on me: | |
| Category: | Connector / NET | Severity: | S1 (Critical) |
| Version: | 8.0.28 | OS: | Windows (Windows 10) |
| Assigned to: | CPU Architecture: | x86 | |
| Tags: | .Net 6. EF Core | ||
[20 Feb 2022 8:11]
aliuddin abd rauf
Here's the full exception message:
InvalidOperationException Unhandled expression '[Microsoft.EntityFrameworkCore.Query.SqlExpressions.SqlConstantExpression] COLLATE utf8mb4_bin' of type 'MySql.EntityFrameworkCore.Query.Expressions.Internal.MySQLCollateExpression' encountered in 'SqlNullabilityProcessor'. at Microsoft.EntityFrameworkCore.Query.SqlNullabilityProcessor.VisitCustomSqlExpression(SqlExpression sqlExpression, Boolean allowOptimizedExpansion, Boolean& nullable)
at Microsoft.EntityFrameworkCore.Query.SqlNullabilityProcessor.Visit(SqlExpression sqlExpression, Boolean allowOptimizedExpansion, Boolean preserveNonNullableColumns, Boolean& nullable)
at Microsoft.EntityFrameworkCore.Query.SqlNullabilityProcessor.Visit(SqlExpression sqlExpression, Boolean allowOptimizedExpansion, Boolean& nullable)
at Microsoft.EntityFrameworkCore.Query.SqlNullabilityProcessor.Visit(SqlExpression sqlExpression, Boolean& nullable)
at Microsoft.EntityFrameworkCore.Query.SqlNullabilityProcessor.VisitSqlFunction(SqlFunctionExpression sqlFunctionExpression, Boolean allowOptimizedExpansion, Boolean& nullable)
at Microsoft.EntityFrameworkCore.Query.SqlNullabilityProcessor.Visit(SqlExpression sqlExpression, Boolean allowOptimizedExpansion, Boolean preserveNonNullableColumns, Boolean& nullable)
at Microsoft.EntityFrameworkCore.Query.SqlNullabilityProcessor.Visit(SqlExpression sqlExpression, Boolean allowOptimizedExpansion, Boolean& nullable)
at Microsoft.EntityFrameworkCore.Query.SqlNullabilityProcessor.Visit(SqlExpression sqlExpression, Boolean& nullable)
at Microsoft.EntityFrameworkCore.Query.SqlNullabilityProcessor.VisitSqlFunction(SqlFunctionExpression sqlFunctionExpression, Boolean allowOptimizedExpansion, Boolean& nullable)
at Microsoft.EntityFrameworkCore.Query.SqlNullabilityProcessor.Visit(SqlExpression sqlExpression, Boolean allowOptimizedExpansion, Boolean preserveNonNullableColumns, Boolean& nullable)
at Microsoft.EntityFrameworkCore.Query.SqlNullabilityProcessor.VisitSqlBinary(SqlBinaryExpression sqlBinaryExpression, Boolean allowOptimizedExpansion, Boolean& nullable)
at Microsoft.EntityFrameworkCore.Query.SqlNullabilityProcessor.Visit(SqlExpression sqlExpression, Boolean allowOptimizedExpansion, Boolean preserveNonNullableColumns, Boolean& nullable)
at Microsoft.EntityFrameworkCore.Query.SqlNullabilityProcessor.VisitSqlBinary(SqlBinaryExpression sqlBinaryExpression, Boolean allowOptimizedExpansion, Boolean& nullable)
at Microsoft.EntityFrameworkCore.Query.SqlNullabilityProcessor.Visit(SqlExpression sqlExpression, Boolean allowOptimizedExpansion, Boolean preserveNonNullableColumns, Boolean& nullable)
at Microsoft.EntityFrameworkCore.Query.SqlNullabilityProcessor.VisitSqlBinary(SqlBinaryExpression sqlBinaryExpression, Boolean allowOptimizedExpansion, Boolean& nullable)
at Microsoft.EntityFrameworkCore.Query.SqlNullabilityProcessor.Visit(SqlExpression sqlExpression, Boolean allowOptimizedExpansion, Boolean preserveNonNullableColumns, Boolean& nullable)
at Microsoft.EntityFrameworkCore.Query.SqlNullabilityProcessor.Visit(SqlExpression sqlExpression, Boolean allowOptimizedExpansion, Boolean& nullable)
at Microsoft.EntityFrameworkCore.Query.SqlNullabilityProcessor.Visit(SelectExpression selectExpression)
at Microsoft.EntityFrameworkCore.Query.SqlNullabilityProcessor.Visit(TableExpressionBase tableExpressionBase)
at Microsoft.EntityFrameworkCore.Query.SqlNullabilityProcessor.Visit(SelectExpression selectExpression)
at Microsoft.EntityFrameworkCore.Query.SqlNullabilityProcessor.Process(SelectExpression selectExpression, IReadOnlyDictionary`2 parameterValues, Boolean& canCache)
at Microsoft.EntityFrameworkCore.Query.RelationalParameterBasedSqlProcessor.ProcessSqlNullability(SelectExpression selectExpression, IReadOnlyDictionary`2 parametersValues, Boolean& canCache)
at Microsoft.EntityFrameworkCore.Query.RelationalParameterBasedSqlProcessor.Optimize(SelectExpression selectExpression, IReadOnlyDictionary`2 parametersValues, Boolean& canCache)
at Microsoft.EntityFrameworkCore.Query.Internal.RelationalCommandCache.GetRelationalCommand(IReadOnlyDictionary`2 parameters)
at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.InitializeReader(DbContext _, Boolean result)
at MySql.EntityFrameworkCore.Storage.Internal.MySQLExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.MoveNext()
at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
at Backend_DotNet.DBAccess.DBA_Student.SelectStudentsEF(EFCoreQueryAttrDto eFCoreQueryAttr) in E:\UITM\UITM Internship One Stop Center\Backend-DotNet\DBAccess\DBA_Student.cs:line 205
at Backend_DotNet.Services.Implementation.ManageStudentServiceImpl.GetStudentList(EFCoreQueryAttrDto eFCoreQueryAttr) in E:\UITM\UITM Internship One Stop Center\Backend-DotNet\Services\Implementation\ManageStudentServiceImpl.cs:line 122
Custom message:query data: {"Comparers":[{"PropName":"name","Value":"abu","Operation":3}],"Orderer":{"PropName":"name","Order":-1},"OffSet":0,"TotalRow":2,"AndComparer":true}
[9 Mar 2022 4:17]
Huang Alvin
Same issue here.
Here is my code:
await _dbCtx.Institutionbank
.FirstOrDefaultAsync(i =>
i.InstitutionId == _krCtx.InstitutionId
&& i.AccountNo.TrimStart('0') == header.AccountNo.Data.TrimStart('0')
'TrimStart' cause exception:
[Microsoft.EntityFrameworkCore.Query.SqlExpressions.SelectExpression+ConcreteColumnExpression]' of type 'MySql.EntityFrameworkCore.Query.Expressions.Internal.MySQLComplexFunctionArgumentExpression' encountered in 'SqlNullabilityProcessor'.'
EF core 6 docs say Trim is supported: https://docs.microsoft.com/en-us/ef/core/what-is-new/ef-core-6.0/whatsnew#:~:text=RTRIM-,S...
[29 Apr 2022 10:45]
MySQL Verification Team
Hello, Thank you for the bug report. Imho this is duplicate of Bug #103436, please see Bug #103436. Regards, Ashwini Patil
Description: Dear mysql teams, thanks for reading this report. I'm developing a .Net 6 system using EF Core with database first, and use the scaffold command to generate the db class. So here's the problem, when I tried to add some filter for the string field, like example bellow, using var context = new SchoolContext(); var res = context.UserStudents.where(s => s.name.Contains("john")); it's still working good. but when i try change the method "Contains" and use "StartsWith" or "EndsWith", it will trigger the exception like bellow, InvalidOperationException Unhandled expression '[Microsoft.EntityFrameworkCore.Query.SqlExpressions.SqlConstantExpression] COLLATE utf8mb4_bin' of type 'MySql.EntityFrameworkCore.Query.Expressions.Internal.MySQLCollateExpression' encountered in 'SqlNullabilityProcessor'. at Microsoft.EntityFrameworkCore.Query.SqlNullabilityProcessor.VisitCustomSqlExpression(SqlExpression sqlExpression, Boolean allowOptimizedExpansion, Boolean& nullable) So this error make my application not able to filter the string using "StartsWith" or "EndsWith". How to repeat: To repeat this, just use the efcore and linq functional syntax and use the string "StartsWith" method in the "where" linq method. Suggested fix: I don't have any idea for this, sorry.