dotConnectio for Oracle 11g generates unsupported SQL

dotConnectio for Oracle 11g generates unsupported SQL

Postby 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!
raffaelbechara
 
Posts: 3
Joined: Thu 09 Jul 2015 13:47

Re: dotConnectio for Oracle 11g generates unsupported SQL

Postby Shalex » Thu 09 Jul 2015 16:53

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


Return to dotConnect for Oracle