dotnet / dotnet/efcore

Projection column aliases don't take the max identifier length into account when uniquifying

Open
#26,993 16 comments 0 reactions 0 assignees View on GitHub
area-query customer-reported
Dominant language
C#
Stars
14.8k
Forks
3.4k
PR merge metrics
PR metrics pending

Description

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.

```C#
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](https://github.com/dotnet/efcore/files/7710469/TestSimple.zip)

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.