search

FirebirdSQL / NETProvider

Firebird ADO.NET Data Provider
https://www.firebirdsql.org/en/net-provider/
Other
152 stars 63 forks source link

EF Core IS NULL, FALSE, TRUE wrongly processed in SELECT Clause #1088

Closed niklasxulls closed 1 year ago

niklasxulls commented 1 year ago

It is not possible to use or query for null values within the select, which is highly problematic when working with .net 6 nullable reference types. Especially when working with AutoMapper EF QueryExtensions which extends the current query IQueryable in a similar way (this extension helps mapping related entities on database level, with minimal configuration. e.g directly mapping entities to dtos without extra code in the query itself):

Imagine my model looks something similar to this:

int? MyNavigationPropKey { get; set; }
OtherType MyNavigationProp { get; set; }

Now I am creating a query looking like this: 

_context.MyType.Select(t => new { hasOtherType = MyNavigationProp != null }).ToList();

This will generate the following SQL Code:
 SELECT "m"."MyNavigationPropKey" IS NULL AS "hasOtherType" FROM "MyType" m

Funnily enough, if I change my Code to the following

_context.MyType.Select(t => new { hasOtherType = MyNavigationProp == null ? false : true }).ToList();

it will affect the above statement in the following way:

 SELECT CASE WHEN  "m"."MyNavigationPropKey" IS NULL THEN FALSE ELSE TRUE END AS "hasOtherType" FROM "MyType" m

None of the above variants is valid, both throw an exception. It seems like IS NULL as well as keywords like TRUE and FALSE may not be used without being wrapped in an SELECT. (I am using Firebird 2.5 with Dialect 3).

The stacktrace looks as the follows (the column 23 is not correct, since in the statement I provided in the issue I simplified the names)

FirebirdSql.Data.FirebirdClient.FbException (0x80004005): Dynamic SQL Error SQL error code = -104 Token unknown - line 1, column 23 IS  ---> FirebirdSql.Data.Common.IscException: Dynamic SQL Error SQL error code = -104 Token unknown - line 1, column 23 IS    at FirebirdSql.Data.Client.Managed.IResponseExtensions.HandleResponseException(IResponse response)    at FirebirdSql.Data.Client.Managed.Version10.GdsDatabase.ReadResponseAsync(CancellationToken cancellationToken)    at FirebirdSql.Data.Client.Managed.Version11.GdsStatement.PrepareAsync(String commandText, CancellationToken cancellationToken)    at FirebirdSql.Data.Client.Managed.Version11.GdsStatement.PrepareAsync(String commandText, CancellationToken cancellationToken)    at FirebirdSql.Data.FirebirdClient.FbCommand.PrepareAsync(Boolean returnsSet, CancellationToken cancellationToken)    at FirebirdSql.Data.FirebirdClient.FbCommand.PrepareAsync(Boolean returnsSet, CancellationToken cancellationToken)    at FirebirdSql.Data.FirebirdClient.FbCommand.ExecuteCommandAsync(CommandBehavior behavior, Boolean returnsSet, CancellationToken cancellationToken)    at FirebirdSql.Data.FirebirdClient.FbCommand.ExecuteReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)    at FirebirdSql.Data.FirebirdClient.FbCommand.ExecuteReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)    at FirebirdSql.Data.FirebirdClient.FbCommand.ExecuteDbDataReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)    at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)    at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)    at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.AsyncEnumerator.InitializeReaderAsync(AsyncEnumerator enumerator, CancellationToken cancellationToken)    at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.AsyncEnumerator.MoveNextAsync() FirebirdSql.Data.FirebirdClient.FbException (0x80004005): Dynamic SQL Error SQL error code = -104 Token unknown - line 1, column 23 IS  ---> FirebirdSql.Data.Common.IscException: Dynamic SQL Error SQL error code = -104 Token unknown - line 1, column 23 IS    at FirebirdSql.Data.Client.Managed.IResponseExtensions.HandleResponseException(IResponse response)    at FirebirdSql.Data.Client.Managed.Version10.GdsDatabase.ReadResponseAsync(CancellationToken cancellationToken)    at FirebirdSql.Data.Client.Managed.Version11.GdsStatement.PrepareAsync(String commandText, CancellationToken cancellationToken)    at FirebirdSql.Data.Client.Managed.Version11.GdsStatement.PrepareAsync(String commandText, CancellationToken cancellationToken)    at FirebirdSql.Data.FirebirdClient.FbCommand.PrepareAsync(Boolean returnsSet, CancellationToken cancellationToken)    at FirebirdSql.Data.FirebirdClient.FbCommand.PrepareAsync(Boolean returnsSet, CancellationToken cancellationToken)    at FirebirdSql.Data.FirebirdClient.FbCommand.ExecuteCommandAsync(CommandBehavior behavior, Boolean returnsSet, CancellationToken cancellationToken)    at FirebirdSql.Data.FirebirdClient.FbCommand.ExecuteReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)    at FirebirdSql.Data.FirebirdClient.FbCommand.ExecuteReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)    at FirebirdSql.Data.FirebirdClient.FbCommand.ExecuteDbDataReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)    at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)    at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)    at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.AsyncEnumerator.InitializeReaderAsync(AsyncEnumerator enumerator, CancellationToken cancellationToken)    at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.AsyncEnumerator.MoveNextAsync()
[00:37:44] fail: GetCasesQuery[0] Dynamic SQL Error SQL error code = -104 Token unknown - line 1, column 23 IS
cincuranet commented 1 year ago

That's expected. Minimum required Firebird version for EF Core provider is 3.0. You can get away with 2.5, but you'll not be able to use all features from EF Core/mapping.