Wrong query generated for mapped function when using schema

[nfdavi]

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;

[alexkeh]

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.