dotConnectio for Oracle 11g generates unsupported SQL

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
raffaelbechara
Posts: 3
Joined: Thu 09 Jul 2015 13:47

dotConnectio for Oracle 11g generates unsupported SQL

Post by raffaelbechara » Thu 09 Jul 2015 13:56

"dotConnect for Oracle" is supposed to support Oracle 11g, but it sometimes generates SQL keywords supported only by Oracle 12c.

How to solve this situation?

Example:

Please, consider the following model:

Code: Select all

namespace OracleDevartDriverTest
{
    public class Genero
    {
        public string CodigoGenero { get; set; }
    }

    public class Amigo
    {
        public int Id { get; set; }
        public string Nome { get; set; }
        public string FK_CodigoGenero { get; set; }
    }
}
The Fluent mappings are:

Code: Select all

 protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Amigo>()
                .ToTable("AMIGO")
                .HasKey(t => t.Id);

            modelBuilder.Entity<Amigo>()
                .Property(m => m.Id)
                .HasColumnName("ID");

            modelBuilder.Entity<Amigo>()
                .Property(m => m.Nome)
                .HasColumnName("NOME");

            modelBuilder.Entity<Amigo>()
                .Property(m => m.FK_CodigoGenero)
                .HasColumnName("FK_NM_GENERO");

            // ----

            modelBuilder.Entity<Genero>()
               .ToTable("GENERO")
               .HasKey(t => t.CodigoGenero);

            modelBuilder.Entity<Genero>()
                .Property(m => m.CodigoGenero)
                .HasColumnName("NM_GENERO");
        }
This is my LINQ query:

Code: Select all

var amigo = (from Genero a in ctx.Generos
                         join Amigo g in ctx.Amigos on a.CodigoGenero equals g.FK_CodigoGenero into h
                         select new
                         {
                             Nome = a.CodigoGenero,
                             Numero = h.OrderByDescending(n => n.Nome).FirstOrDefault()
                         }
                            ).ToList();
If I run this LINQ query, the output SQL contains an "OUTER APPLY" keyword, that is supported only in Oracle 12c. So, an exception is thrown and the following message is given by dotConnect provider:
OUTER APPLY is not supported by Oracle Database 11g and lower. Oracle 12c or higher is required to run this LINQ statement correctly. If you need to run this statement with Oracle Database 11g or lower, rewrite it so that it can be converted to SQL, supported by the version of Oracle you use.
The same happens to the official Oracle Database Privider for .NET (ODP).

How to tell the provider that I'm using Oracle 11g and not Oracle 12c???!!

I think it should do it automatically, but it doesn't do!

Shalex
Devart Team
Posts: 8088
Joined: Thu 14 Aug 2008 12:44

Re: dotConnectio for Oracle 11g generates unsupported SQL

Post by Shalex » Thu 09 Jul 2015 16:53


Post Reply