"ORA-00972: identifier is too long" on alias

[Hatles]

I'm migrating a project from EF core 2.1 to version 5.0. We are using an oracle database un version 11.2.
Some requests don't work anymore. The database limits symbols to 30 characters.

I have a scenario where I request an entity, include a collection of sub entities (one 2 many) and then include 2 child entities (many 2 one). The two last entities have a column with the same name with 30 characters.
The sql request generated has a left join with an alias that long 31 characters for the second "duplicated" column. Ef core seems to add a 0 at the end to make it unique.

When executing the query it results in a "ORA-00972: identifier is too long" error.

I'm using the Devart provider. I reproduce the same behavior with the Oracle provider in a simple project.
The Oracle provider says to use the MaxIdentifierLength attribute (https://docs.oracle.com/en/database/oracle/oracle-data-access-components/19.3.2/odpnt/EFCoreIdentifier.html) but it does nothing.

I found "AddToProjection" and "GenerateUniqueAlias" methods in the SelectExpression class (namespace Microsoft.EntityFrameworkCore.Query.SqlExpressions). Those methods add a counter at the end of a column name to make it unique, but there is no control over the generated alias length.

var query = modelContext.Tours
                    .Include(t => t.Order)
                    .ThenInclude(o => o.Partner)
                    .Include(t => t.Order)
                    .ThenInclude(o => o.Partner2)
                    .Take(10);

Exemple of generated query

-- p__p_0='10'
SELECT "t0".I_TOUR_ID, "t0".S_TOUR_REFERENCE, "t2".I_ORDER_ID, "t2".I_PARTNER2_ID, "t2".I_PARTNER_ID, "t2".S_ORDER_REFERENCE, "t2".I_TOUR_ID, "t2".I_PARTNER_ID0, "t2".S_PARTNER_LABEL, "t2".B_PARTNER_LONG_COLUMN_30_CHARS, "t2".I_PARTNER_PARENT_ID, "t2".I_PARTNER_ID1, "t2".S_PARTNER_LABEL0, "t2".B_PARTNER_LONG_COLUMN_30_CHARS0, "t2".I_PARTNER_PARENT_ID0
FROM (
    SELECT "t".I_TOUR_ID, "t".S_TOUR_REFERENCE
    FROM (
        SELECT "t".I_TOUR_ID, "t".S_TOUR_REFERENCE
        FROM MISTRAL_DEV.TOUR "t"
    ) "t"
    WHERE ROWNUM <= :p__p_0
) "t0"
LEFT JOIN (
    SELECT "t1".I_ORDER_ID, "t1".I_PARTNER2_ID, "t1".I_PARTNER_ID, "t1".S_ORDER_REFERENCE, "t1".I_TOUR_ID, "p".I_PARTNER_ID I_PARTNER_ID0, "p".S_PARTNER_LABEL, "p".B_PARTNER_LONG_COLUMN_30_CHARS, "p".I_PARTNER_PARENT_ID, "p0".I_PARTNER_ID I_PARTNER_ID1, "p0".S_PARTNER_LABEL S_PARTNER_LABEL0, "p0".B_PARTNER_LONG_COLUMN_30_CHARS B_PARTNER_LONG_COLUMN_30_CHARS0, "p0".I_PARTNER_PARENT_ID I_PARTNER_PARENT_ID0
    FROM MISTRAL_DEV.T_ORDER "t1"
    INNER JOIN MISTRAL_DEV.PARTNER "p" ON "t1".I_PARTNER_ID = "p".I_PARTNER_ID
    INNER JOIN MISTRAL_DEV.PARTNER "p0" ON "t1".I_PARTNER2_ID = "p0".I_PARTNER_ID
) "t2" ON "t0".I_TOUR_ID = "t2".I_TOUR_ID
ORDER BY "t0".I_TOUR_ID, "t2".I_ORDER_ID, "t2".I_PARTNER_ID0, "t2".I_PARTNER_ID1

"B_PARTNER_LONG_COLUMN_30_CHARS0" is 31 characters long

EF Core version: 5.0 (also tested in 3.1, same problem)
Database provider: Devart.Data.Oracle.EFCore 9.14.1382 / Oracle.EntityFrameworkCore 5.21.4
Target framework: .NET 5.0 (also tested in 3.1, same problem)
Operating system: Windows 10
IDE: Rider 2021.3

I'm including my test project for Oracle database. I will try to do the same with SqlServer as i think the error will be the same with the limit of 128 characters.
TestSimple.zip

[roji]

@Hatles this should be raised with Devart. EF Core has a facility for providers to specify their maximum identifier limit, here's how SQL Server defines 128: https://github.com/dotnet/efcore/blob/main/src/EFCore.SqlServer/Metadata/Conventions/SqlServerConventionSetBuilder.cs#L56. Devart should implement the same in their Oracle provider.

[ajcvickers]

@roji Reading the full issue here, this seems to be a case where we are not respecting the limit when uniquifying identifiers. So I don't think this is a provider issue.

[roji]

Sorry, missed that.

[Hatles]

After switching to the SqlServer provider and using the modelBuilder.Model.SetMaxIdentifierLength(30) in my DbContext, the result is the same, this parameter is just ignored.

Result query string in sql server:

DECLARE @__p_0 int = 10;

SELECT [t0].[I_TOUR_ID], [t0].[S_TOUR_REFERENCE], [t2].[I_ORDER_ID], [t2].[I_PARTNER2_ID], [t2].[I_PARTNER_ID], [t2].[S_ORDER_REFERENCE], [t2].[I_TOUR_ID], [t2].[I_PARTNER_ID0], [t2].[S_PARTNER_LABEL], [t2].[B_PARTNER_LONG_COLUMN_30_CHARS], [t2].[I_PARTNER_PARENT_ID], [t2].[I_PARTNER_ID1], [t2].[S_PARTNER_LABEL0], [t2].[B_PARTNER_LONG_COLUMN_30_CHARS0], [t2].[I_PARTNER_PARENT_ID0]
FROM (
    SELECT TOP(@__p_0) [t].[I_TOUR_ID], [t].[S_TOUR_REFERENCE]
    FROM [MISTRAL_DEV].[TOUR] AS [t]
) AS [t0]
LEFT JOIN (
    SELECT [t1].[I_ORDER_ID], [t1].[I_PARTNER2_ID], [t1].[I_PARTNER_ID], [t1].[S_ORDER_REFERENCE], [t1].[I_TOUR_ID], [p].[I_PARTNER_ID] AS [I_PARTNER_ID0], [p].[S_PARTNER_LABEL], [p].[B_PARTNER_LONG_COLUMN_30_CHARS], [p].[I_PARTNER_PARENT_ID], [p0].[I_PARTNER_ID] AS [I_PARTNER_ID1], [p0].[S_PARTNER_LABEL] AS [S_PARTNER_LABEL0], [p0].[B_PARTNER_LONG_COLUMN_30_CHARS] AS [B_PARTNER_LONG_COLUMN_30_CHARS0], [p0].[I_P
ARTNER_PARENT_ID] AS [I_PARTNER_PARENT_ID0]
    FROM [MISTRAL_DEV].[T_ORDER] AS [t1]
    INNER JOIN [MISTRAL_DEV].[PARTNER] AS [p] ON [t1].[I_PARTNER_ID] = [p].[I_PARTNER_ID]
    INNER JOIN [MISTRAL_DEV].[PARTNER] AS [p0] ON [t1].[I_PARTNER2_ID] = [p0].[I_PARTNER_ID]
) AS [t2] ON [t0].[I_TOUR_ID] = [t2].[I_TOUR_ID]
ORDER BY [t0].[I_TOUR_ID], [t2].[I_ORDER_ID], [t2].[I_PARTNER_ID0], [t2].[I_PARTNER_ID1]

The column with 31 characters is still there ([t2].[B_PARTNER_LONG_COLUMN_30_CHARS0]);

[Hatles]

Renaming the column with 30 characters to a column with 128 characters raise an error during sql execution in sql server too:

DECLARE @__p_0 int = 10;

SELECT [t0].[I_TOUR_ID], [t0].[S_TOUR_REFERENCE], [t2].[I_ORDER_ID], [t2].[I_PARTNER2_ID], [t2].[I_PARTNER_ID], [t2].[S_ORDER_REFERENCE], [t2].[I_TOUR_ID], [t2].[I_PARTNER_ID0], [t2].[S_PARTNER_LABEL], [t2].[B_PARTNER_LONG_COLUMN_128_CHARS_SSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSS], [t2].[I_PARTNER_PARENT_ID], [t2].[I_PARTNER_ID1], [t2].[S_PARTNER_LABEL0], [
t2].[B_PARTNER_LONG_COLUMN_128_CHARS_SSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSS0], [t2].[I_PARTNER_PARENT_ID0]
FROM (
    SELECT TOP(@__p_0) [t].[I_TOUR_ID], [t].[S_TOUR_REFERENCE]
    FROM [MISTRAL_DEV].[TOUR] AS [t]
) AS [t0]
LEFT JOIN (
    SELECT [t1].[I_ORDER_ID], [t1].[I_PARTNER2_ID], [t1].[I_PARTNER_ID], [t1].[S_ORDER_REFERENCE], [t1].[I_TOUR_ID], [p].[I_PARTNER_ID] AS [I_PARTNER_ID0], [p].[S_PARTNER_LABEL], [p].[B_PARTNER_LONG_COLUMN_128_CHARS_SSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSS], [p].[I_PARTNER_PARENT_ID], [p0].[I_PARTNER_ID] AS [I_PARTNER_ID1], [p0].[S_PARTNER_LABEL] AS [S_PART
NER_LABEL0], [p0].[B_PARTNER_LONG_COLUMN_128_CHARS_SSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSS] AS [B_PARTNER_LONG_COLUMN_128_CHARS_SSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSS0], [p0].[I_PARTNER_PARENT_ID] AS [I_PARTNER_PARENT_ID0]
    FROM [MISTRAL_DEV].[T_ORDER] AS [t1]
    INNER JOIN [MISTRAL_DEV].[PARTNER] AS [p] ON [t1].[I_PARTNER_ID] = [p].[I_PARTNER_ID]
    INNER JOIN [MISTRAL_DEV].[PARTNER] AS [p0] ON [t1].[I_PARTNER2_ID] = [p0].[I_PARTNER_ID]
) AS [t2] ON [t0].[I_TOUR_ID] = [t2].[I_TOUR_ID]
ORDER BY [t0].[I_TOUR_ID], [t2].[I_ORDER_ID], [t2].[I_PARTNER_ID0], [t2].[I_PARTNER_ID1]

Unhandled exception. Microsoft.Data.SqlClient.SqlException (0x80131904): The identifier that starts with 'B_PARTNER_LONG_COLUMN_128_CHARS_SSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSS' is too long. Maximum length is 128.
The identifier that starts with 'B_PARTNER_LONG_COLUMN_128_CHARS_SSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSSS' is too long. Maximum length is 128.
   at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at Microsoft.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
   at Microsoft.Data.SqlClient.SqlDataReader.get_MetaData()
   at Microsoft.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean isAsync, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String method)
   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at Microsoft.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
   at Microsoft.Data.SqlClient.SqlCommand.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(DbContext _, Boolean result)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.MoveNext()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at TestSimple.Program.Main(String[] args) in C:\Dev\Star Service\mistral-3.0\TestSimple\Program.cs:line 31

[Hatles]

This is definitely an issue on a generated alias with an added '0' at the end, ending up with a length of 31 characters:
[p0].[B_PARTNER_LONG_COLUMN_30_CHARS] AS [B_PARTNER_LONG_COLUMN_30_CHARS0]

Here is how is declared the column in the DbContext in the exemple I linked originaly with this issue:
entity.Property(e => e.LongColumn) .IsRequired() .HasColumnType("char") .HasMaxLength(1) .HasColumnName("B_PARTNER_LONG_COLUMN_30_CHARS");

[smitpatel]

Sorry I didn't see that there was alias assigned which was longer and picked up from the column name itself.

[smitpatel]

Verify 2 scenarios - column name appearing as alias and alias being picked up from definition of nominal type

[Tom-V]

I wanted to take a look at this as this is an "easy-fix"

What I found was that indeed the current implementation doesn't know anything about the max length for aliases.
The model already has a max identifier length, but I'm not sure this is the right annotation to change because this is really for identifiers and not for aliases in expressions, also the identifier is on the model and in this case it's the expression that we have to create, so both are 2 different contexts.

One of the things I found was that the SelectExpression right now just adds a unique number at the end.
Looking into it, I saw that right now the Expression doesn't have any annotation available. We could copy them from the entitytype in the constructor, but I'm not sure this is the correct way to continue.

My idea also was to maybe reuse the Uniquifier to make it unique, but also this I'm not sure if we want to do this or not.
And add a new annotation and convention? to be able to set the maximum alias length for each database type as this is different in for example SQL and Oracle

Basically, to be able to continue with this, I need some more directions or help to be able to fix this, in my opinion this doesn't look like an easy-fix.

[ajcvickers]

@Tom-V The max identifier length in the model is the correct value to use. This will need to be made available to the select expression through dependency injection. The Uniquifer seems like the correct service to tie this all together.

Note that the easy-fix label is perhaps mis-named. It really means that it is easy for somebody who is very familiar with how the EF query pipelines works. Which is basically just @smitpatel.

[smitpatel]

Note: SelectExpression already as logic to give aliases and unique-fy them. Just need to pass the maxLength there to truncate accordingly. No need to use Uniquifier.

[holatom]

Any update on this? I have same error on .NET 7.

[ajcvickers]

@holatom This issue is in the Backlog milestone. This means that it is not planned for the next release. We will re-assess the backlog following the this release and consider this item at that time. However, keep in mind that there are many other high priority features with which it will be competing for resources. Make sure to vote (👍) for this issue if it is important to you.