This issue has been previously reported as #255 and is still present in Oracle.EntityFrameworkCore 8.21.121
when schema is explicitly specified in OnModelCreating, using function mapping results in incorrectly generated query - schema is placed before table (it should be inside).
The generated (incorrect) query:
SELECT "t"."Id", "t"."Data"
FROM "DEV3".table("TestFunction"()) "t"
should be instead:
SELECT "t"."Id", "t"."Data"
FROM table("DEV3"."TestFunction"()) "t"
reproducible example (.net core 8.0):
// Program.cs
var builder = WebApplication.CreateBuilder(args);
builder.Services.AddDbContext<TestContext>(
opt => opt.UseOracle(builder.Configuration
.GetConnectionString("DefaultConnection")),
contextLifetime: ServiceLifetime.Scoped);
var app = builder.Build();
using var scope = app.Services.CreateScope();
var ctx = scope.ServiceProvider.GetService<TestContext>()!;
var result = ctx.TestFunction().ToList(); // generates incorrect query which produces ORA-00903
app.Run();
// model + context
public class TestTable
{
public int Id { get; set; }
public string Data { get; set; }
}
public class TestContext : DbContext
{
public TestContext(DbContextOptions<TestContext> options)
: base(options)
{
}
public DbSet<TestTable> TestTables { get; set; }
public IQueryable<TestTable> TestFunction() => FromExpression(() => TestFunction());
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.HasDefaultSchema("DEV3"); // this causes the issue - when schema is not specified, the query is generated correctly
modelBuilder.Entity<TestTable>(builder =>
{
builder.HasKey(b => b.Id);
});
modelBuilder.HasDbFunction(typeof(TestContext).GetMethod(nameof(TestFunction)))
.HasName("TestFunction");
}
}
DB can be created using ef.core migrations
dotnet ef migrations add IniticalCreate
dotnet ef database update
function definition in Oracle:
CREATE OR REPLACE TYPE "TestType" AS OBJECT (
"Id" NUMBER,
"Data" NVARCHAR2
);
/
CREATE OR REPLACE TYPE "TestTableType" AS TABLE OF UserRecordType;
/
CREATE OR REPLACE FUNCTION "TestFunction"()
RETURN "TestTableType" PIPELINED
IS
BEGIN
FOR "tt" IN (
SELECT * FROM "TestTables"
) LOOP
PIPE ROW("TestType"(
"tt"."Id",
"tt"."Data"
));
END LOOP;
RETURN;
END;
Thanks @nfdavi for reporting! I was able to reproduce your error and have filed a bug (36218521) to have the dev team review the issue and provide a fix.
This issue has been previously reported as #255 and is still present in
Oracle.EntityFrameworkCore 8.21.121
when schema is explicitly specified in
OnModelCreating
, using function mapping results in incorrectly generated query - schema is placed beforetable
(it should be inside).The generated (incorrect) query:
should be instead:
reproducible example (.net core 8.0):
DB can be created using ef.core migrations
function definition in Oracle: