Bug with includes/joins query generation

dferenc

Hi,

using beta4 of the Oracle.EntityFrameworkCore package with ASP.NET Core 2.2.106 in a web api project and I have encountered a very serious issue. There is a problem when generating queries when using includes or joins in LINQ when they are used in a many-to-one relation or one-to-one relations and the relating tables have the same column names (e.g. "ID"). The query generated tries to select two columns with the same name (e.g. "ID") and there is an error in the query and it can't be processed. Here's an example with two models, a query, and the generated query:

    [Table("PRGKVT")]

    public class PrgKvtModel

    {

        [Key]

        [DatabaseGenerated(DatabaseGeneratedOption.None)]

        [Column("ID")]

        public long Id { get; set; }

        [MaxLength(80)]

        [Column("NZV")]

        public string Name { get; set; }

        [Column("DNE")]

        [Required]

        public DateTime Date { get; set; }

        [InverseProperty("Match")]

        public List<KvtOddModel> KvtOdds { get; set; }

    }

    [Table("KVTODD")]

    public class KvtOddModel

    {

        [Key]

        [DatabaseGenerated(DatabaseGeneratedOption.None)]

        [Column("ID")]

        public long Id { get; set; }

        [Column("VLD")]

        [MinLength(1)]

        [MaxLength(1)]

        [Required]

        public string IsValid { get; set; }

        [Column("INSTM")]

        public DateTime Inserted { get; set; }

        [Column("PRGKVT_ID")]

        [Required]

        public long MatchId { get; set; }

        public PrgKvtModel Match { get; set; }

    }

and the LINQ query:

await _db.KvtOdds.Include(p => p.Match).Take(5).ToListAsync()

(_db.Matches is the PrgKvt model representation in the Database Context)

When executing this code the following query is generated:

SELECT "p"."ID", "p"."INSTM", "p"."VLD", "p"."PRGKVT_ID", "p.Match"."ID", "p.Match"."DNE", "p.Match"."NZV"

FROM "KVTODD" "p"

INNER JOIN "PRGKVT" "p.Match" ON "p"."PRGKVT_ID" = "p.Match"."ID"

FETCH FIRST :p_0 ROWS ONLY

And this throws the following error:

Oracle.ManagedDataAccess.Client.OracleException (0x80004005): ORA-00918: column ambiguously defined

because the query tries to select two columns with the same name ("ID") with the same name in the resulting set. I guess that other EF implementations solve this problem by using aliases in column generation and mapping. The same issue happens if one tries to use the .Join LINQ method.

Please fix this or provide me with a workaround because this EF Core implementation for Oracle is absolutely useless for any serious work (I really hope that this problem won't make it into production!). The current solution I used is a for-each loop to retrieve related models but I'm sure you can see how can that be a problem with larger datasets.

Please not that in one-to-many realtionship everything works because the list of related objects/children is retrieved in a sub-query:

await _db.Matches.Include(p => p.KvtOdds).Take(5).ToListAsync()

Alex Keh-Oracle

Are you using Oracle DB version 12.1?

If so, then this is bug 29199665, which is actually a DB server bug, not ODP.NET. You can fix this issue by either upgrading the DB to 12.2 or higher. You can even resolve this issue by downgrading to 11.2.

Alex Keh-Oracle

Are you using Oracle DB version 12.1?

If so, then this is bug 29199665, which is actually a DB server bug, not ODP.NET. You can fix this issue by either upgrading the DB to 12.2 or higher. You can even resolve this issue by downgrading to 11.2.

dferenc

Thank you Alex, we do have 12.1. We will try to upgrade and will let you know if it solved the isue.

dferenc

Yes, this solved the issue. Thank you very much for your support!