EF Core 3.1 with nullable enum produces ORA-00904 invalid identifier code

[Eric_08]

Right after upgrading to .NET Core 3.1 and EF Core 3.1 with DevArt 9.10, I started getting ORA-00904 invalid identifier code.

Assume the following:

Code: Select all

public enum ColorType
{
    Red = 0,
    Blue = 1
}

public class SomeObject
{
    public int Id {get; set;}
    public ColorType? Color {get; set;} 
}

The above is a simple object with ColorType being nullable property. Assume that SomeObject is mapped to a table and the following EF Core query:

Code: Select all

 var query = await table1.AsNoTracking()
                    .Where(t => t.ColorType == ColorType.Blue).Select(h => h.Id).ToListAsync();

With the above code, I would get ORA-00904: "BLUE": invalid identifier with the following produced SQL:

Code: Select all

SELECT "g".ID
FROM <some_table> "g"
WHERE "g".Color = Blue

Notice that instead of using integers, the SQL query uses string literal of the enum, which of course is wrong. Strangely, I had used enum in other cases, and it worked correctly. The only difference was the nullable part, which in this case affects how the SQL is generated. Only happens in NET Core 3.1/EF Core 3.1. Works correctly in .NET Core 2.2/EF Core 2.4

[Eric_08]

Any update on this?

[kakone]

I'm trying dotConnect for Oracle with EF Core 3.1 and I'm also stuck with this bug.
I also noticed that there is the same bug with non nullable enum and .Contains method.
This code :

Code: Select all

var settingsIds = new ApplicationSettingId[] { ApplicationSettingId.SmtpHost, ApplicationSettingId.SmtpPort };
var applicationSettings = dbContext.ApplicationSettings.Where(s => settingsIds.Contains(s.Id)).Select(s => s.TextValue);

produces the SQL

Code: Select all

SELECT "a".TEXT_VALUE
FROM APPLICATION_CONFIGURATION "a"
WHERE "a".ID IN (SmtpHost, SmtpPort)

[Shalex]

We have reproduced the bug and will notify you when it is fixed.

[Eric_08]

When can we expect the new build?

[Shalex]

The bug with using nullable enum properties with default mapping to integers in .Where() conditions in EF Core 3.1 is fixed.

The internal build with the fix is available at https://download.devart.com/nuget_oracle_9_10_925.zip.

[Sv01a]

Same with Select:

Code: Select all

_context.Templates
	 .Select(p => new TemplateDto
		{
 			Id = p.Id,
 			Type = TemplateType.Template// <- Enum
 		})
	 .FirstOrDefaultAsync(cancellationToken);

Sql:

Code: Select all

SELECT :p__template_Id_0 Id, Template Type
      FROM Templates t
      WHERE t.Id = :p__template_Id_0
      FETCH FIRST 1 ROWS ONLY

Devart.Data.Oracle.EFCore="9.10.925"

[Shalex]

Thank you for your report. We are investigating the case.

[Sv01a]

Any updates?

[Shalex]

The bug with using nullable enum properties with default mapping to integers in .Select() clause in EF Core 3.1 is fixed: https://download.devart.com/nuget_oracle_9_10_935.zip.

[Sv01a]

unfortunately, i found another case:

Code: Select all

s.Type == Enum1.Val1 ? Enum2.Val1 :
    s.Type == Enum1.Val2 ? Enum2.Val2 :
    Enum2.Val3

Code: Select all

SELECT CASE
    WHEN k0.Type = 3 THEN Val1 
    ELSE CASE
        WHEN k0.Type = 2 THEN Val2 
        ELSE Val3
    END
END c

[Shalex]

The bug with generating a subselect within the CASE clause in EF Core 3 is fixed. We will provide the build with the fix in several days.

[Shalex]

Please download the internal build from https://download.devart.com/nuget_oracle_9_11_948.zip.

[Eric_08]

Just got the internal build, and so far, so good. Case statement issue also appears to be fixed, which is great. One minor thing I'm seeing is extra dependencies in the Devart.Data.Oracle package. It's now trying to include Microsoft.Windows.Compatibility package that includes all kinds of DLLs that previous package version didn't include. I hope it won't be included in the official version. Other than that, it looks good.

Thanks,
Eric

[Shalex]

One minor thing I'm seeing is extra dependencies in the Devart.Data.Oracle package. It's now trying to include Microsoft.Windows.Compatibility package that includes all kinds of DLLs that previous package version didn't include. I hope it won't be included in the official version.

We will investigate the question and notify you about the result.