search

PomeloFoundation / Pomelo.EntityFrameworkCore.MySql

Entity Framework Core provider for MySQL and MariaDB built on top of MySqlConnector
MIT License
2.68k stars 381 forks source link

Query mysql with System.Linq.Expression Not operator throw exception #1289

Closed andiyuliandi26 closed 3 years ago

andiyuliandi26 commented 3 years ago

Steps to reproduce

I build an linq expression use System.Linq.Expression with operator Not,

var generatedExpression = Expression.Not(expression);

this is my DBContext Config 

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    var environment = Environment.GetEnvironmentVariable("ASPNETCORE_ENVIRONMENT");

    IConfigurationRoot configuration = new ConfigurationBuilder()
        .SetBasePath(AppDomain.CurrentDomain.BaseDirectory)
        .AddJsonFile("appsettings.json", optional: false, reloadOnChange: false)
        .AddJsonFile($"appsettings.{environment}.json", optional: true, reloadOnChange: false)
        .Build();

    optionsBuilder.UseMySql(
        configuration.GetConnectionString("LenDbConnection"),
        ServerVersion.AutoDetect(configuration.GetConnectionString("LenDbConnection")),
        mySqlOptions => mySqlOptions
            .CharSetBehavior(CharSetBehavior.NeverAppend)
            .EnableIndexOptimizedBooleanColumns())
        .EnableSensitiveDataLogging()
        .EnableDetailedErrors();
}

The issue

in .Net Core 3.1 with Pomelo.EntityFrameworkCore.MySql version 3.2.4 the expression work properly. when I upgrade to .Net 5 with Pomelo.EntityFrameworkCore.MySql version 5..0.0-alpha.2 the linq expression with Not operator doesn't work and throw an exception. I don't know this issue is on EFCore 5 or on this Pomelo.EntityFrameworkCore.MySql.

Exception message:
Stack trace:
 ---> MySqlConnector.MySqlException (0x80004005): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NOT (LOWER(`h`.`NoTiket`) LIKE '%200001%')) & ((`h`.`RecordStatus` = 'A') & ((`h' at line 3
 ---> MySqlConnector.MySqlException (0x80004005): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NOT (LOWER(`h`.`NoTiket`) LIKE '%200001%')) & ((`h`.`RecordStatus` = 'A') & ((`h' at line 3
   at MySqlConnector.Core.ServerSession.ReceiveReplyAsyncAwaited(ValueTask`1 task) in /_/src/MySqlConnector/Core/ServerSession.cs:line 817
   at MySqlConnector.Core.ResultSet.ReadResultSetHeaderAsync(IOBehavior ioBehavior) in /_/src/MySqlConnector/Core/ResultSet.cs:line 49
   at MySqlConnector.MySqlDataReader.ActivateResultSet(CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlDataReader.cs:line 118
   at MySqlConnector.MySqlDataReader.CreateAsync(CommandListPosition commandListPosition, ICommandPayloadCreator payloadCreator, IDictionary`2 cachedProcedures, IMySqlCommand command, CommandBehavior behavior, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlDataReader.cs:line 436
   at MySqlConnector.Core.CommandExecutor.ExecuteReaderAsync(IReadOnlyList`1 commands, ICommandPayloadCreator payloadCreator, CommandBehavior behavior, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/Core/CommandExecutor.cs:line 60
   at MySqlConnector.MySqlCommand.ExecuteReaderAsync(CommandBehavior behavior, IOBehavior ioBehavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlCommand.cs:line 311
   at MySqlConnector.MySqlCommand.ExecuteDbDataReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken) in /_/src/MySqlConnector/MySqlCommand.cs:line 304
   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(DbContext _, Boolean result, CancellationToken cancellationToken)
   at Pomelo.EntityFrameworkCore.MySql.Storage.Internal.MySqlExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.AsyncEnumerator.MoveNextAsync()
   at Microsoft.EntityFrameworkCore.Query.ShapedQueryCompilingExpressionVisitor.SingleAsync[TSource](IAsyncEnumerable`1 asyncEnumerable, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.ShapedQueryCompilingExpressionVisitor.SingleAsync[TSource](IAsyncEnumerable`1 asyncEnumerable, CancellationToken cancellationToken)

Further technical details

MySQL version: 8.0.22 Operating system: Windows 10 Pomelo.EntityFrameworkCore.MySql version: 5.0.0-alpha.2 Microsoft.AspNetCore.App version: .Net 5.0.101 Microsoft.EntityFrameworkCore version: 5.0.2

mguinness commented 3 years ago

Using ToQueryString() what is the SQL generated and what happens when you run it directly in MySQL Workbench? The query using & operator instead of AND looks problematic. Also compare it to the SQL generated in 3.2.4 version.

lauxjpn commented 3 years ago

@andiyuliandi26 In addition to what @mguinness requested, please post your full query/expression generation code.

andiyuliandi26 commented 3 years ago

image I try to debug the code, in that picture the linq expression already right, but when the query execute, operator "AND" transform to "&".

SELECT `h`.`ID`, `h`.`ApproversID`, `h`.`AssignedToID`, `h`.`AssignedToTimID`, `h`.`Attachment`, `h`.`CreatedBy`, `h`.`CreatedDate`, `h`.`Deskripsi`, `h`.`Judul`, `h`.`KaryawanPembuatID`, `h`.`LastModifiedBy`, `h`.`LastModifiedDate`, `h`.`NoTiket`, `h`.`PrioritasID`, `h`.`RecordStatus`, `h`.`StatusID`, `h`.`TipeTiketID`, `h`.`UKPembuatID`, `h`.`UKTujuanID`, `u`.`ID`, `u`.`CreatedBy`, `u`.`CreatedDate`, `u`.`DateFrom`, `u`.`DateTo`, `u`.`IsCurrent`, `u`.`KodeUK`, `u`.`LastModifiedBy`, `u`.`LastModifiedDate`, `u`.`LevelID`, `u`.`NamaUK`, `u`.`ParentID`, `u`.`RecordStatus`, `u`.`Singkatan`, `h0`.`ID`, `h0`.`CreatedBy`, `h0`.`CreatedDate`, `h0`.`Deskripsi`, `h0`.`IsActive`, `h0`.`Kode`, `h0`.`LastModifiedBy`, `h0`.`LastModifiedDate`, `h0`.`Nama`, `h0`.`RecordStatus`, `h1`.`ID`, `h1`.`CreatedBy`, `h1`.`CreatedDate`, `h1`.`Deskripsi`, `h1`.`IsActive`, `h1`.`KodeWarna`, `h1`.`LastModifiedBy`, `h1`.`LastModifiedDate`, `h1`.`Nama`, `h1`.`RecordStatus`, `k`.`ID`, `k`.`AbsensiGroupID`, `k`.`Agama`, `k`.`Aktif`, `k`.`CreatedBy`, `k`.`CreatedDate`, `k`.`DokKTP`, `k`.`DokNPWP`, `k`.`Etnis`, `k`.`Foto`, `k`.`GolonganDarah`, `k`.`Kelamin`, `k`.`LastModifiedBy`, `k`.`LastModifiedDate`, `k`.`NIK`, `k`.`NIKKTP`, `k`.`NPWP`, `k`.`Nama`, `k`.`NamaKecil`, `k`.`NoKartuKeluarga`, `k`.`NoPassport`, `k`.`RecordStatus`, `k`.`StatusPerkawinan`, `k`.`TanggalLahir`, `k`.`TempatLahir`, `k`.`TglKedaluwarsaKTP`, `k`.`TglKedaluwarsaPassport`, `k`.`ToleransiID`, `k`.`VendorNo`, `h2`.`ID`, `h2`.`CreatedBy`, `h2`.`CreatedDate`, `h2`.`Deskripsi`, `h2`.`IsActive`, `h2`.`LastModifiedBy`, `h2`.`LastModifiedDate`, `h2`.`Level`, `h2`.`Nama`, `h2`.`RecordStatus`, `u0`.`ID`, `u0`.`CreatedBy`, `u0`.`CreatedDate`, `u0`.`DateFrom`, `u0`.`DateTo`, `u0`.`IsCurrent`, `u0`.`KodeUK`, `u0`.`LastModifiedBy`, `u0`.`LastModifiedDate`, `u0`.`LevelID`, `u0`.`NamaUK`, `u0`.`ParentID`, `u0`.`RecordStatus`, `u0`.`Singkatan`, `h3`.`ID`, `h3`.`CreatedBy`, `h3`.`CreatedDate`, `h3`.`Deskripsi`, `h3`.`IsActive`, `h3`.`LastModifiedBy`, `h3`.`LastModifiedDate`, `h3`.`Nama`, `h3`.`RecordStatus`, `u1`.`ID`, `u1`.`CreatedBy`, `u1`.`CreatedDate`, `u1`.`FailedPasswordAttemptCount`, `u1`.`HasMobileDevice`, `u1`.`IsApproved`, `u1`.`IsLockedOut`, `u1`.`KaryawanID`, `u1`.`LastLockedOut`, `u1`.`LastLoggedIn`, `u1`.`LastModifiedBy`, `u1`.`LastModifiedDate`, `u1`.`LastPasswordChanged`, `u1`.`RecordStatus`, `u1`.`Token`, `u1`.`TokenExpired`, `u1`.`TokenIssued`, `u1`.`UserName`, `k0`.`ID`, `k0`.`AbsensiGroupID`, `k0`.`Agama`, `k0`.`Aktif`, `k0`.`CreatedBy`, `k0`.`CreatedDate`, `k0`.`DokKTP`, `k0`.`DokNPWP`, `k0`.`Etnis`, `k0`.`Foto`, `k0`.`GolonganDarah`, `k0`.`Kelamin`, `k0`.`LastModifiedBy`, `k0`.`LastModifiedDate`, `k0`.`NIK`, `k0`.`NIKKTP`, `k0`.`NPWP`, `k0`.`Nama`, `k0`.`NamaKecil`, `k0`.`NoKartuKeluarga`, `k0`.`NoPassport`, `k0`.`RecordStatus`, `k0`.`StatusPerkawinan`, `k0`.`TanggalLahir`, `k0`.`TempatLahir`, `k0`.`TglKedaluwarsaKTP`, `k0`.`TglKedaluwarsaPassport`, `k0`.`ToleransiID`, `k0`.`VendorNo`, `u2`.`ID`, `u2`.`CreatedBy`, `u2`.`CreatedDate`, `u2`.`FailedPasswordAttemptCount`, `u2`.`HasMobileDevice`, `u2`.`IsApproved`, `u2`.`IsLockedOut`, `u2`.`KaryawanID`, `u2`.`LastLockedOut`, `u2`.`LastLoggedIn`, `u2`.`LastModifiedBy`, `u2`.`LastModifiedDate`, `u2`.`LastPasswordChanged`, `u2`.`RecordStatus`, `u2`.`Token`, `u2`.`TokenExpired`, `u2`.`TokenIssued`, `u2`.`UserName`, `k1`.`ID`, `k1`.`AbsensiGroupID`, `k1`.`Agama`, `k1`.`Aktif`, `k1`.`CreatedBy`, `k1`.`CreatedDate`, `k1`.`DokKTP`, `k1`.`DokNPWP`, `k1`.`Etnis`, `k1`.`Foto`, `k1`.`GolonganDarah`, `k1`.`Kelamin`, `k1`.`LastModifiedBy`, `k1`.`LastModifiedDate`, `k1`.`NIK`, `k1`.`NIKKTP`, `k1`.`NPWP`, `k1`.`Nama`, `k1`.`NamaKecil`, `k1`.`NoKartuKeluarga`, `k1`.`NoPassport`, `k1`.`RecordStatus`, `k1`.`StatusPerkawinan`, `k1`.`TanggalLahir`, `k1`.`TempatLahir`, `k1`.`TglKedaluwarsaKTP`, `k1`.`TglKedaluwarsaPassport`, `k1`.`ToleransiID`, `k1`.`VendorNo`, `t`.`ID`, `t`.`CreatedBy`, `t`.`CreatedDate`, `t`.`KomponenID`, `t`.`LastModifiedBy`, `t`.`LastModifiedDate`, `t`.`RecordStatus`, `t`.`TiketID`, `t`.`ID0`, `t`.`CreatedBy0`, `t`.`CreatedDate0`, `t`.`Deskripsi`, `t`.`IsActive`, `t`.`LastModifiedBy0`, `t`.`LastModifiedDate0`, `t`.`Nama`, `t`.`ParentID`, `t`.`RecordStatus0`, `t0`.`ID`, `t0`.`Attachment`, `t0`.`CreatedBy`, `t0`.`CreatedDate`, `t0`.`Komentar`, `t0`.`LastModifiedBy`, `t0`.`LastModifiedDate`, `t0`.`RecordStatus`, `t0`.`TiketID`, `t0`.`UserID`, `t0`.`ID0`, `t0`.`CreatedBy0`, `t0`.`CreatedDate0`, `t0`.`FailedPasswordAttemptCount`, `t0`.`HasMobileDevice`, `t0`.`IsApproved`, `t0`.`IsLockedOut`, `t0`.`KaryawanID`, `t0`.`LastLockedOut`, `t0`.`LastLoggedIn`, `t0`.`LastModifiedBy0`, `t0`.`LastModifiedDate0`, `t0`.`LastPasswordChanged`, `t0`.`RecordStatus0`, `t0`.`Token`, `t0`.`TokenExpired`, `t0`.`TokenIssued`, `t0`.`UserName`, `t0`.`ID1`, `t0`.`AbsensiGroupID`, `t0`.`Agama`, `t0`.`Aktif`, `t0`.`CreatedBy1`, `t0`.`CreatedDate1`, `t0`.`DokKTP`, `t0`.`DokNPWP`, `t0`.`Etnis`, `t0`.`Foto`, `t0`.`GolonganDarah`, `t0`.`Kelamin`, `t0`.`LastModifiedBy1`, `t0`.`LastModifiedDate1`, `t0`.`NIK`, `t0`.`NIKKTP`, `t0`.`NPWP`, `t0`.`Nama`, `t0`.`NamaKecil`, `t0`.`NoKartuKeluarga`, `t0`.`NoPassport`, `t0`.`RecordStatus1`, `t0`.`StatusPerkawinan`, `t0`.`TanggalLahir`, `t0`.`TempatLahir`, `t0`.`TglKedaluwarsaKTP`, `t0`.`TglKedaluwarsaPassport`, `t0`.`ToleransiID`, `t0`.`VendorNo`
FROM `HD_Tiket` AS `h`
INNER JOIN `UnitKerja` AS `u` ON `h`.`UKTujuanID` = `u`.`ID`
INNER JOIN `HD_TipeTiket` AS `h0` ON `h`.`TipeTiketID` = `h0`.`ID`
LEFT JOIN `HD_PrioritasTiket` AS `h1` ON `h`.`PrioritasID` = `h1`.`ID`
INNER JOIN `Karyawan` AS `k` ON `h`.`KaryawanPembuatID` = `k`.`ID`
INNER JOIN `HD_StatusTiket` AS `h2` ON `h`.`StatusID` = `h2`.`ID`
INNER JOIN `UnitKerja` AS `u0` ON `h`.`UKPembuatID` = `u0`.`ID`
LEFT JOIN `HD_Tim` AS `h3` ON `h`.`AssignedToTimID` = `h3`.`ID`
LEFT JOIN `User` AS `u1` ON `h`.`AssignedToID` = `u1`.`ID`
LEFT JOIN `Karyawan` AS `k0` ON `u1`.`KaryawanID` = `k0`.`ID`
INNER JOIN `User` AS `u2` ON `h`.`ApproversID` = `u2`.`ID`
INNER JOIN `Karyawan` AS `k1` ON `u2`.`KaryawanID` = `k1`.`ID`
LEFT JOIN (
    SELECT `h4`.`ID`, `h4`.`CreatedBy`, `h4`.`CreatedDate`, `h4`.`KomponenID`, `h4`.`LastModifiedBy`, `h4`.`LastModifiedDate`, `h4`.`RecordStatus`, `h4`.`TiketID`, `h5`.`ID` AS `ID0`, `h5`.`CreatedBy` AS `CreatedBy0`, `h5`.`CreatedDate` AS `CreatedDate0`, `h5`.`Deskripsi`, `h5`.`IsActive`, `h5`.`LastModifiedBy` AS `LastModifiedBy0`, `h5`.`LastModifiedDate` AS `LastModifiedDate0`, `h5`.`Nama`, `h5`.`ParentID`, `h5`.`RecordStatus` AS `RecordStatus0`
    FROM `HD_TiketKomponen` AS `h4`
    INNER JOIN `HD_Komponen` AS `h5` ON `h4`.`KomponenID` = `h5`.`ID`
    WHERE `h4`.`RecordStatus` = 'A'
) AS `t` ON `h`.`ID` = `t`.`TiketID`
LEFT JOIN (
    SELECT `h6`.`ID`, `h6`.`Attachment`, `h6`.`CreatedBy`, `h6`.`CreatedDate`, `h6`.`Komentar`, `h6`.`LastModifiedBy`, `h6`.`LastModifiedDate`, `h6`.`RecordStatus`, `h6`.`TiketID`, `h6`.`UserID`, `u3`.`ID` AS `ID0`, `u3`.`CreatedBy` AS `CreatedBy0`, `u3`.`CreatedDate` AS `CreatedDate0`, `u3`.`FailedPasswordAttemptCount`, `u3`.`HasMobileDevice`, `u3`.`IsApproved`, `u3`.`IsLockedOut`, `u3`.`KaryawanID`, `u3`.`LastLockedOut`, `u3`.`LastLoggedIn`, `u3`.`LastModifiedBy` AS `LastModifiedBy0`, `u3`.`LastModifiedDate` AS `LastModifiedDate0`, `u3`.`LastPasswordChanged`, `u3`.`RecordStatus` AS `RecordStatus0`, `u3`.`Token`, `u3`.`TokenExpired`, `u3`.`TokenIssued`, `u3`.`UserName`, `k2`.`ID` AS `ID1`, `k2`.`AbsensiGroupID`, `k2`.`Agama`, `k2`.`Aktif`, `k2`.`CreatedBy` AS `CreatedBy1`, `k2`.`CreatedDate` AS `CreatedDate1`, `k2`.`DokKTP`, `k2`.`DokNPWP`, `k2`.`Etnis`, `k2`.`Foto`, `k2`.`GolonganDarah`, `k2`.`Kelamin`, `k2`.`LastModifiedBy` AS `LastModifiedBy1`, `k2`.`LastModifiedDate` AS `LastModifiedDate1`, `k2`.`NIK`, `k2`.`NIKKTP`, `k2`.`NPWP`, `k2`.`Nama`, `k2`.`NamaKecil`, `k2`.`NoKartuKeluarga`, `k2`.`NoPassport`, `k2`.`RecordStatus` AS `RecordStatus1`, `k2`.`StatusPerkawinan`, `k2`.`TanggalLahir`, `k2`.`TempatLahir`, `k2`.`TglKedaluwarsaKTP`, `k2`.`TglKedaluwarsaPassport`, `k2`.`ToleransiID`, `k2`.`VendorNo`
    FROM `HD_TiketReply` AS `h6`
    INNER JOIN `User` AS `u3` ON `h6`.`UserID` = `u3`.`ID`
    INNER JOIN `Karyawan` AS `k2` ON `u3`.`KaryawanID` = `k2`.`ID`
    WHERE `h6`.`RecordStatus` = 'A'
) AS `t0` ON `h`.`ID` = `t0`.`TiketID`
WHERE ((`h`.`RecordStatus` = 'A') & NOT (LOWER(`h`.`NoTiket`) LIKE '%200001%')) & ((`h`.`RecordStatus` = 'A') & ((`h`.`CreatedBy` = 'andi.sugiarto') AND `h`.`CreatedBy` IS NOT NULL))
ORDER BY `h`.`ID`, `u`.`ID`, `h0`.`ID`, `h1`.`ID`, `k`.`ID`, `h2`.`ID`, `u0`.`ID`, `h3`.`ID`, `u1`.`ID`, `k0`.`ID`, `u2`.`ID`, `k1`.`ID`, `t`.`ID`, `t`.`ID0`, `t0`.`ID`, `t0`.`ID0`, `t0`.`ID1`

FYI, i dont know how to get SQL Generated in 3.2.4 version, because the method of .ToQueryString() not available on that version.

lauxjpn commented 3 years ago

I try to debug the code, in that picture the linq expression already right, but when the query execute, operator "AND" transform to "&".

That is because the Expression.And Method creates as bitwise AND operation:

Creates a BinaryExpression that represents a bitwise AND operation.

If you are building your expression tree by hand, than you want to call the Expression.AndAlso Method, which creates a conditional AND expression:

Creates a BinaryExpression that represents a conditional AND operation that evaluates the second operand only if the first operand evaluates to true.

andiyuliandi26 commented 3 years ago

aaaaah thank you very much @lauxjpn , it really work.. so the problem isn't in the library, but the expression. actually in .Net Core 3.1 i already use Expression.And with Expression.Not, there no problem like this. oke problem solved guys, thank's for your attention,

Gracias..