Allow to customize an alias in model binding

[alexandis]

I've updated to EFCore 6.0 and now a lot of my queries are broken. The reason is that a lot of properties have the name which is a reserved word for our DB (I was not aware). Let's say:

builder.Entity<MyProperty>(b =>
{
    b.ToTable("MY_TABLE", CoreDbProperties.DbSchema);
    b.Property(x => x.Number).HasColumnName("C_NUMBER").IsRequired();
    ...
}

When Linq constructs the query - it names aliases after a property name and this breaks DB query.

To avoid nightmare with renaming tons of properties and doing regression testing afterwards, I suggest to make an alias customizable, like this:

builder.Entity<MyEntity>(b =>
{
    b.ToTable("MY_TABLE", CoreDbProperties.DbSchema);
    b.Property(x => x.Number).HasColumnName("C_NUMBER").HasAlias("MyNumber").IsRequired();
    ...
}

[roji]

@alexandis can you provide the full SQL being generated here? Also, which database are you running against?

EF Core quotes aliases generated in SQL, which should avoid them being recognized as reserved words.

[alexandis]

@roji The DB is Oracle. The generated SQL contains no quotes - they are stripped by injector:

public class RemoveQuotesInterceptor : DbCommandInterceptor
{
    public override InterceptionResult<DbDataReader> ReaderExecuting(DbCommand command, CommandEventData eventData, InterceptionResult<DbDataReader> result)
    {
        return base.ReaderExecuting(RemoveQuotes(command), eventData, result);
    }

    public override ValueTask<InterceptionResult<DbDataReader>> ReaderExecutingAsync(DbCommand command, CommandEventData eventData, InterceptionResult<DbDataReader> result,
        CancellationToken cancellationToken = new CancellationToken())
    {
        return base.ReaderExecutingAsync(RemoveQuotes(command), eventData, result, cancellationToken);
    }

    // and so on
}

Quotes stripping is done intentionally: the reason for that is that Oracle is a case-sensitive DB and we already had DB before creating code (all objects are in uppercase). As a result, if quotes are preserved - we have issue with CASE mismatching (table names, column names, etc.) - so DB objects are not found when referenced by Linq-generated queries.

SELECT ... FROM MD_CA_FND_TRAF_SIGN t1 JOIN ...
ORDER BY t.S_NAME,
         --t1.Number, -- breaks the query!
         --t1.Version, -- breaks the query!
         t1.C_FND_ADD

[roji]

@alexandis are you saying that the Oracle EF Core provider does add quotes and you've added a special interceptor in your application to strip them? If so, that is definitely not something you should be doing - quoting is important for various reasons and should not be removed.

A better way to deal with this is to simply tell EF Core what the correct names of the tables and columns are in the database: here are the docs for doing this for tables, and here they are for columns. You can use bulk configuration to apply changes to all your tables/columns in one piece of code instead of for each one.

[alexandis]

@roji I took a look at the code to refresh my memory and now I can see the stripping was made not because of our tables (we use fluent model binding and everything is OK there), but because our solution is built on top of ABP framework - ABP framework has C# migration scripts for THEIR tables (not a fluent as ours) and camel-style case is hardcoded inside:

            migrationBuilder.CreateTable(
                name: "PayPlans",
                columns: table => new
                {
                    Id = table.Column<Guid>(type: "guid", nullable: false),
                    ....
                }
                ...
            }

Despite this, according to our DB settings (or because Oracle ALWAYS work with upper-case - not sure, I am not DB expert), on executing the migration scripts, the tables (and the column names inside) are created in UPPERCASE when running the given migration script, but LINQ generates a query in camel-case, though... As a result of this mismatch, we are getting errors like this:

ORA-00942: table or view does not exist ("PayPlans", because the table is created as "PAYPLANS" and column names are in upper-case too).

We can't make them adjust their script for our case. We could rewrite it completely, of course. But it would take a while each time we upgrade the framework.

BTW, we haven't met the issues with stripping quotes so far - this one with aliases is the only one now...

However if you are saying there is no other way out, but fixing the model binding - we may close this issue.

[roji]

@alexandis I'm still not clear on the issue...

Regardless of how your tables get created (by ABP, with external C# migration scripts or anything else), at the end of the day you have an Oracle database with tables and columns - ready to use - and EF Core needs to generate SQL identifiers which match them. So I think you need to look at making EF Core emit the right identifiers (i.e. in uppercase, or whatever is needed) by configuring your model properly, rather than attempting to strip quoting via an interceptor (or adjusting anything with ABP). That may mean telling EF Core that all your table and column names are in uppercase (if so, you may be interested in https://github.com/efcore/EFCore.NamingConventions, which has a specific mode for that).

If you provide more detail on exactly what's going on, I may be able to help more.

[alexandis]

I can give an example:

        var query = from j in dbContext.Jobs.Include(x => x.MeasureConverter)
                    join p in dbSet
                    on new { j.TenantId, JobId = (long?)j.Id } equals new { p.TenantId, p.JobId }
                    into packs
                    from pack in packs.DefaultIfEmpty()
                    select new Pack(pack == null ? 0 : pack.Id)
                    {
                        TenantId = pack == null ? j.TenantId : pack.TenantId,
                        InputDataPath = pack == null ? null : pack.InputDataPath
                    };

produces the following SQL (it does not give aliases to the first two properties, even the alias names could be easily figured out from the bound column names in model binding, TenantId => "C_TENANT", Id => "N_PACK"):

      SELECT CASE
          WHEN ("e0"."N_PACK" IS NULL) OR ("e0"."C_TENANT" IS NULL) THEN 0
          ELSE "e0"."N_PACK"
      END, CASE
          WHEN ("e0"."N_PACK" IS NULL) OR ("e0"."C_TENANT" IS NULL) THEN "e"."C_TENANT"
          ELSE "e0"."C_TENANT"
      END, "e0"."S_PATH_DATA", "e0"."S_PATH_STD", ...
      FROM "ET_LA_JOB" "e" LEFT JOIN ...

instead of expected

      SELECT CASE
          WHEN ("e0"."N_PACK" IS NULL) OR ("e0"."C_TENANT" IS NULL) THEN 0
          ELSE "e0"."N_PACK"
      END "N_PACK", CASE
          WHEN ("e0"."N_PACK" IS NULL) OR ("e0"."C_TENANT" IS NULL) THEN "e"."C_TENANT"
          ELSE "e0"."C_TENANT"
      END "C_TENANT", "e0"."S_PATH_DATA", "e0"."S_PATH_STD", ...
      FROM "ET_LA_JOB" "e" LEFT JOIN ...

Thus, if I do not reference the column names by aliases - I cannot use the corresponding properties (pack.Id, pack.TenantId) in the filtering, etc...

[roji]

@alexandis I'm not clear on what problem you're encountering exactly... Please open a new issue with a minimal, runnable code sample that shows what you're trying to do and what's not working.