Page 1 of 2
EF Core 3.1 with nullable enum produces ORA-00904 invalid identifier code
Posted: Mon 30 Dec 2019 16:17
by 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
Re: EF Core 3.1 with nullable enum produces ORA-00904 invalid identifier code
Posted: Mon 06 Jan 2020 19:26
by Eric_08
Any update on this?
Re: EF Core 3.1 with nullable enum produces ORA-00904 invalid identifier code
Posted: Thu 09 Jan 2020 17:44
by 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)
Re: EF Core 3.1 with nullable enum produces ORA-00904 invalid identifier code
Posted: Thu 09 Jan 2020 19:56
by Shalex
We have reproduced the bug and will notify you when it is fixed.
Re: EF Core 3.1 with nullable enum produces ORA-00904 invalid identifier code
Posted: Wed 15 Jan 2020 15:14
by Eric_08
When can we expect the new build?
Re: EF Core 3.1 with nullable enum produces ORA-00904 invalid identifier code
Posted: Thu 16 Jan 2020 17:48
by 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.
Re: EF Core 3.1 with nullable enum produces ORA-00904 invalid identifier code
Posted: Tue 21 Jan 2020 15:05
by 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"
Re: EF Core 3.1 with nullable enum produces ORA-00904 invalid identifier code
Posted: Thu 23 Jan 2020 18:00
by Shalex
Thank you for your report. We are investigating the case.
Re: EF Core 3.1 with nullable enum produces ORA-00904 invalid identifier code
Posted: Thu 30 Jan 2020 15:09
by Sv01a
Any updates?
Re: EF Core 3.1 with nullable enum produces ORA-00904 invalid identifier code
Posted: Fri 31 Jan 2020 16:50
by 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.
Re: EF Core 3.1 with nullable enum produces ORA-00904 invalid identifier code
Posted: Sat 01 Feb 2020 09:19
by 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
Re: EF Core 3.1 with nullable enum produces ORA-00904 invalid identifier code
Posted: Thu 06 Feb 2020 10:51
by 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.
Re: EF Core 3.1 with nullable enum produces ORA-00904 invalid identifier code
Posted: Sat 15 Feb 2020 14:46
by Shalex
Re: EF Core 3.1 with nullable enum produces ORA-00904 invalid identifier code
Posted: Sat 15 Feb 2020 17:58
by 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
Re: EF Core 3.1 with nullable enum produces ORA-00904 invalid identifier code
Posted: Mon 17 Feb 2020 12:08
by 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.