dotnet-db-samples: ORA-00918: column ambiguously defined

Using Oracle 12C 12.1.0 with latest EF Core 3.1 provider

I am mapping to an existing user/schema/database (: and are not in charge of it.

I have a contact person that have a contact type. I only have navigation to the ContactType

When I run this code

var contact = context.Contacts.Include(x=>x.Type).FirstOrDefault();

That produces this sql:

 fail: Microsoft.EntityFrameworkCore.Database.Command[20102]
      Failed executing DbCommand (67ms) [Parameters=[], CommandType='Text', CommandTimeout='0']
      SELECT "c"."ID", "c"."TYPE", "c"."CUSTOMER", "c"."EMAIL", "c"."NAME", "c"."PHONE", "c0"."ID", "c0"."NAME"
      FROM "SAMSON"."CUSTOMER_CONTACT" "c"
      INNER JOIN "SAMSON"."CUSTOMER_CONTACT_TYPE" "c0" ON "c"."TYPE" = "c0"."ID"
      FETCH FIRST 1 ROWS ONLY

I have no problems running the sql: Is it an error, or are my relation mapping wrong?

SELECT "c"."ID", "c"."TYPE", "c"."CUSTOMER", "c"."EMAIL", "c"."NAME", "c"."PHONE", "c0"."ID", "c0"."NAME"
FROM "SAMSON"."CUSTOMER_CONTACT" "c"
INNER JOIN "SAMSON"."CUSTOMER_CONTACT_TYPE" "c0" ON "c"."TYPE" = "c0"."ID";

contact_domain contact_type_domain samson_contact contact_mapping contact_type_mapping

About this issue

  • Original URL
  • State: closed
  • Created 4 years ago
  • Comments: 15 (4 by maintainers)

Most upvoted comments

@Martin-Andersen Yes, the bug does not occur in DB 12.2 and higher as it does in DB 12.1. I was assured by the SQL dev team the recommended solution is to upgrade to 12.2 or higher if aliases can’t be used.

@Martin-Andersen There aren’t really any good solutions.

EF Core doesn’t have a way for providers to retrieve the DB server version. Thus, if ODP.NET EF Core generates aliases, it would have to make this change for all DB versions, not just 12.1. Now, I’m not sure if aliases can be used in this way in LINQ. I’ll find out.

The SQL generated is actually correct. DB 12.1 has a known, documented issue. If the select list contains columns with identical names and you specify the row_limiting_clause, then an ORA-00918 error occurs. This error occurs whether the identically named columns are in the same table or in different tables. You can work around this issue by specifying unique column aliases for the identically named columns.

This issue was fixed in 12.2, but was not backported to 12.1. Essentially, the “patch” is to move to DB 12.2 or higher.