Bug with includes/joins query generation — oracle-tech

    Forum Stats

  • 3,715,997 Users
  • 2,242,925 Discussions
  • 7,845,725 Comments

Discussions

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Bug with includes/joins query generation

dferenc
dferenc Member Posts: 3
edited August 2019 in ODP.NET

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()

Best Answer

  • Alex Keh-Oracle
    Alex Keh-Oracle Posts: 2,753 Employee
    edited June 2019 Accepted Answer

    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.

Answers

Sign In or Register to comment.