Currently we are porting our backend to .NET6, using the latest available version (9.16.1434), and have encountered a regression.
Here is the error:
Code: Select all
Microsoft.EntityFrameworkCore.Database.Command[20102]
Failed executing DbCommand (11ms) [Parameters=[], CommandType='Text', CommandTimeout='0']
SELECT "c"."Name", (
SELECT MAX("i0"."id")
FROM "Item" "i0"
CROSS JOIN "Colors" "c1"
WHERE ("c1"."Id" = (
SELECT MAX("c2"."Id")
FROM "Colors" "c2")) AND (DBMS_LOB.COMPARE("c"."Name", "c1"."Name") = 0)) "Id"
FROM "Item" "i"
CROSS JOIN "Colors" "c"
WHERE "c"."Id" = (
SELECT MAX("c2"."Id")
FROM "Colors" "c0")
GROUP BY "c"."Name"
Microsoft.EntityFrameworkCore.Query[10100]
An exception occurred while iterating over the results of a query for context type 'efcore_demo.devart_bad_sql_translation_net6+MyDbContext'.
Devart.Data.Oracle.OracleException (0x80004005): ORA-00904: "c2"."Id": invalid identifier
at Devart.Data.Oracle.cp.c(Int32 A_0)
at Devart.Data.Oracle.ds.a(Int32 A_0)
at Devart.Data.Oracle.ds.e5(Int32 A_0, bx A_1)
at Devart.Data.Oracle.OracleCommand.InternalExecute(CommandBehavior behavior, IDisposable disposable, Int32 startRecord, Int32 maxRecords, Boolean nonQuery)
at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior, Boolean nonQuery)
at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
at Devart.Data.Oracle.Entity.ao.by(CommandBehavior A_0)
at Devart.Common.Entity.cs.ExecuteDbDataReader(CommandBehavior behavior)
at Devart.Data.Oracle.Entity.ao.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.ExecuteReader()
at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReader(RelationalCommandParameterObject parameterObject)
at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.InitializeReader(Enumerator enumerator)
at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.<>c.<MoveNext>b__19_0(DbContext _, Enumerator enumerator)
at Microsoft.EntityFrameworkCore.Storage.NonRetryingExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.MoveNext()
Devart.Data.Oracle.OracleException (0x80004005): ORA-00904: "c2"."Id": invalid identifier
at Devart.Data.Oracle.cp.c(Int32 A_0)
at Devart.Data.Oracle.ds.a(Int32 A_0)
at Devart.Data.Oracle.ds.e5(Int32 A_0, bx A_1)
at Devart.Data.Oracle.OracleCommand.InternalExecute(CommandBehavior behavior, IDisposable disposable, Int32 startRecord, Int32 maxRecords, Boolean nonQuery)
at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior, Boolean nonQuery)
at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
at Devart.Data.Oracle.Entity.ao.by(CommandBehavior A_0)
at Devart.Common.Entity.cs.ExecuteDbDataReader(CommandBehavior behavior)
at Devart.Data.Oracle.Entity.ao.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.ExecuteReader()
at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReader(RelationalCommandParameterObject parameterObject)
at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.InitializeReader(Enumerator enumerator)
at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.<>c.<MoveNext>b__19_0(DbContext _, Enumerator enumerator)
at Microsoft.EntityFrameworkCore.Storage.NonRetryingExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.MoveNext()Code: Select all
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Infrastructure;
using Microsoft.EntityFrameworkCore.Metadata.Builders;
using Microsoft.EntityFrameworkCore.Storage;
using Microsoft.Extensions.Logging;
namespace efcore_demo
{
class Program
{
static void Main(string[] args)
{
test_devart_bad_sql_translation_net6();
Console.WriteLine("Hello World!");
}
private static void test_devart_bad_sql_translation_net6()
{
var seeder = new devart_bad_sql_translation_net6.Seeder();
seeder.Seed();
var logic = new devart_bad_sql_translation_net6.Logic();
var dtos = logic.ExecuteQuery();
}
}
internal class devart_bad_sql_translation_net6
{
public class Logic
{
public IList<Dto> ExecuteQuery()
{
using (var dbContext = new MyDbContext())
{
return (from i in dbContext.Items
from c in dbContext.Colors
where c.Id.Equals((
from c2 in dbContext.Colors
select c2.Id).Max())
group i by c.Name into g
select new Dto { Name = g.Key, Id = g.Max(x => x.Id) })
.ToList();
}
}
}
public class Dto
{
public int Id { get; set; }
public string Name { get; set; }
public string Colors { get; set; }
}
public class Seeder
{
public void Seed()
{
using (var dbContext = new MyDbContext())
{
var rdc = dbContext.Database.GetService<IRelationalDatabaseCreator>();
if (rdc.Exists())
rdc.EnsureDeleted();
rdc.EnsureCreated();
if (!dbContext.Items.Any())
{
for (int i = 0; i < 10; i++)
{
var rand = new Random();
int cat = rand.Next() % 3;
int colors = rand.Next() % 4;
var item = new Item { CategoryId = cat, Name = $"Item {i}" };
for (int c = 0; c <= colors; c++)
{
item.AddColor(c);
}
dbContext.Items.Add(item);
}
}
if (!dbContext.Categories.Any())
{
for (int i = 0; i < 3; i++)
{
var category = new Category { Name = $"Category {i}" };
dbContext.Categories.Add(category);
}
}
dbContext.SaveChanges();
}
}
}
public class Item
{
private readonly List<Color> _colors = new List<Color>();
public IReadOnlyCollection<Color> Colors => _colors;
public int Id { get; set; }
public int CategoryId { get; set; }
public string Name { get; set; }
internal void AddColor(int i)
{
var color = new Color { Name = $"Color {i}" };
_colors.Add(color);
}
}
public class Color
{
public int ItemId { get; private set; }
public virtual Item Item { get; private set; }
public int Id { get; set; }
public string Name { get; set; }
}
public class Category
{
public int Id { get; set; }
public string Name { get; set; }
}
public class MyDbContext : DbContext
{
public static readonly ILoggerFactory loggerFactory = LoggerFactory.Create(builder =>
{
builder.AddConsole();
});
public DbSet<Item> Items { get; set; }
public DbSet<Color> Colors { get; set; }
public DbSet<Category> Categories { get; set; }
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.ApplyConfiguration(new ItemConfiguration());
modelBuilder.ApplyConfiguration(new CategoryConfiguration());
}
private const string DEVART_LICENSE = "xxx";
private readonly string ORACLE_CONNECTION = $"yyy; license key={DEVART_LICENSE};";
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder
.UseLoggerFactory(loggerFactory)
.EnableSensitiveDataLogging()
//.UseSqlite("Data Source=efcoredemo.db");
.UseOracle(ORACLE_CONNECTION);
}
}
public class ItemConfiguration : IEntityTypeConfiguration<Item>
{
public void Configure(EntityTypeBuilder<Item> builder)
{
builder.ToTable("Item");
builder.HasKey(o => o.Id);
builder.Property(t => t.Id).HasColumnName("id").ValueGeneratedOnAdd();
builder.Property(t => t.CategoryId).HasColumnName("categoryId");
builder.Property(t => t.Name).HasColumnName("name");
}
}
public class ColorConfiguration : IEntityTypeConfiguration<Color>
{
public void Configure(EntityTypeBuilder<Color> builder)
{
builder.ToTable("Color");
builder.HasKey(o => o.Id);
builder.Property(t => t.Id).HasColumnName("id").ValueGeneratedOnAdd();
builder.Property(t => t.Name).HasColumnName("name");
builder.HasOne(s => s.Item)
.WithMany(s => s.Colors)
.HasForeignKey(s => s.ItemId);
}
}
public class CategoryConfiguration : IEntityTypeConfiguration<Category>
{
public void Configure(EntityTypeBuilder<Category> builder)
{
builder.ToTable("Category");
builder.HasKey(o => o.Id);
builder.Property(t => t.Id).HasColumnName("id").ValueGeneratedOnAdd();
builder.Property(t => t.Name).HasColumnName("name");
}
}
}
}
