BUG: Oracle EF CodeFirst nested tables + any problem

[b0rg]

I couldn't find how to file a bug report, so here it is.

Rather simple code first structure (3 "nested" tables):

Code: Select all

public class Change 
{
	IColection Approvals {get;set;}
	Status Status {get;set;}
}

public class Approval 
{
	public int ApprovalId {get;set;}
	public int ChangeId {get;set;}
	public bool CurrentlyActive {get;set;}
	IColection ApprovalUser {get;set;}
}

pulbic class ApprovalUser
{
	public int ApprovalUserId {get;set;}
	public string sid {get;set;}
	public bool Active {get;set;}
}

// not necessary to represent the behaviour
public class Status 
{
	public int StatusId {get;set;}
	pulbic string Name {get;set;}
}

And a linq query:
"Give me all approvals for the given user sid which are active.

Code: Select all

IQueryable changes = from c in changeRepo
	 where c.Status.Name == status &&
		c.Approvals.Any(
			a =>
			a.CurrentlyActive == true &&
			a.ApprovalUser.Any(au => au.Approver == sid && au.Active == true))
	 select c; 

will generate the following sql:

Code: Select all

SELECT 
"GroupBy1".A1 AS C1
FROM 
( 
SELECT Count(1) AS A1
	FROM  CHG_DEVELOP."CHANGE" "Extent1"
	INNER JOIN CHG_DEVELOP.REF_STATUS "Extent2" ON "Extent1".STATUS_ID = "Extent2".STATUS_ID
	WHERE 
   (
   	( EXISTS 
      	(
         	SELECT 1 AS C1
				FROM 
            ( 
            	SELECT 
                  "Extent3".CHANGE_APPROVAL_ID AS CHANGE_APPROVAL_ID, 
                  "Extent3".IS_CURRENTLY_ACTIVE AS IS_CURRENTLY_ACTIVE
               FROM CHANGE_APPROVAL "Extent3"
					WHERE "Extent1".CHANGE_ID = "Extent3".CHANGE_ID
				)  "Project1"
				WHERE ("Project1".IS_CURRENTLY_ACTIVE = 1) AND ( EXISTS 
            (
            	SELECT 1 AS C1
					FROM CHANGE_APPROVAL_USER "Extent4"
					WHERE ("Project1".CHANGE_APPROVAL_ID = "Extent4".CHANGE_APPROVAL_ID) AND (("Extent4".APPROVER_SID = 'E529322') AND ("Extent4".IS_ACTIVE = 1))
				)
			)
	)) AND ("Extent2".NAME = :p__linq__1)) AND ("Extent1".CHANGE_NUMBER LIKE '%8%' ESCAPE '/')
)  "GroupBy1"

or simplified

Code: Select all

select * from Change "Extent1"
where exists 
(
	select 1 as c1 
	FROM 
   ( 
      SELECT 
         "Extent3".CHANGE_APPROVAL_ID AS CHANGE_APPROVAL_ID, 
         "Extent3".IS_CURRENTLY_ACTIVE AS IS_CURRENTLY_ACTIVE
      FROM CHANGE_APPROVAL "Extent3"
      WHERE "Extent1".CHANGE_ID = "Extent3".CHANGE_ID
   )  "Project1"
)

Generated sql blows

"ORA-00904: "Extent1"."CHANGE_ID": invalid identifier"

on the following line in both the original and a generated sql.
> WHERE "Extent1".CHANGE_ID = "Extent3".CHANGE_ID

Oracle version:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

Please fix, as we're getting really unhappy.

Dev Art versions:

Devart.Data, Version=5.0.272.0,
Devart.Data.Oracle, Version=6.30.172.0
Devart.Data.Oracle.Entity, Version=6.30.172.0

[b0rg]

Any update?

[Shalex]

Please send us a small complete test project with the corresponding DDL/DML script to reproduce the issue in our environment.

[bjansson]

I'm having the exact same problem
Unfortunately the answer in the FAQ doesn't give me much hope.

DevArt: Is there a fix planned?

[Shalex]

bjansson, this is an Oracle issue (not our provider's).
Always there is a possibility to reconstruct the query so that it is executed at Oracle. Please rewrite your query to work around the problem.

[bjansson]

Shalex:
Ok, I thought it was your provider that generated the SQL that doesn't work.

To reconstruct the query, do you mean I can do that in C# code (with lambdas) or do I need to write my own SQL and do something like a ExecuteStoreCommand?

[Shalex]

To reconstruct the query, do you mean I can do that in C# code (with lambdas) or do I need to write my own SQL and do something like a ExecuteStoreCommand?

You can use any of these 2 workarounds.

[b0rg]

To reconstruct the query, do you mean I can do that in C# code (with lambdas) or do I need to write my own SQL and do something like a ExecuteStoreCommand?

You can use any of these 2 workarounds.

It would help us greatly if you can provide an example of doing it.

[Shalex]

Here is an example of doing it (slightly modified - the ApprovalUser.ApprovalId property is added):

Code: Select all

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Entity;
using System.Data.Entity.ModelConfiguration.Conventions;
using System.Data.Entity.Infrastructure;
using System.ComponentModel.DataAnnotations;
using Devart.Data.Oracle;
using Devart.Data.Oracle.Entity.Configuration;

namespace ConsoleApplication456 {
    class Program {
        static void Main(string[] args) {

          var monitor = new OracleMonitor() { IsActive = true };

          var config = OracleEntityProviderConfig.Instance;
          config.Workarounds.IgnoreSchemaName = true;

          Database.SetInitializer(new DropCreateDatabaseAlways());

            MyDbContext ctx = new MyDbContext();

            //var changes = from c in ctx.Changes
            //              where c.Status.Name == "status" &&
            //                c.Approvals.Any(
            //                  a =>
            //                  a.CurrentlyActive == true &&
            //                  a.ApprovalUser.Any(au => au.sid == "sid" && au.Active == true))
            //              select c;

            var changes = ctx.Changes
              .Where(c => c.Status.Name == "status")
              .Join(ctx.Approvals, c => c.id, a => a.ChangeId, (c, a) => new { c, a })
              .Where(c_a => c_a.a.CurrentlyActive)
              .Join(ctx.ApprovalUsers, c_a => c_a.a.ApprovalId, au => au.ApprovalId, (c_a, au) => new { c_a, au })
              .Where(c_a_au => c_a_au.au.sid == "sid" && c_a_au.au.Active)
              .Select(c_a_au => c_a_au.c_a.c);

            var res = changes.ToList();
        }
    }

    public class Change {
        [Key]
        public int id { get; set; }
        public ICollection Approvals { get; set; }
        public Status Status { get; set; }
    }

    public class Approval {
       [Key]
       public int ApprovalId {get;set;}
       public int ChangeId {get;set;}
       public bool CurrentlyActive {get;set;}
       public ICollection ApprovalUser {get;set;}
    }

    public class ApprovalUser {
       [Key]
       public int ApprovalUserId {get;set;}
       public int ApprovalId { get; set; }
       public string sid {get;set;}
       public bool Active {get;set;}
    }

    // not necessary to represent the behaviour
    public class Status {
       public int StatusId {get;set;}
       public string Name { get; set; }
    }

    public class MyDbContext : DbContext {

      protected override void OnModelCreating(DbModelBuilder modelBuilder) {

        modelBuilder.Conventions.Remove();
        //modelBuilder.Conventions.Remove(); 
      }

        public DbSet Changes { get; set; }
        public DbSet Approvals { get; set; }
        public DbSet ApprovalUsers { get; set; }
       // public DbSet Statuses { get; set; }
    }
}

[babu_2082]

I too facing this issue.

My LINQ expression will be like this.

List<Request> lstUP = pm.GetObjectByGraphCriteria<Request>(v => v.RequestLevel.Any(x => x.RequestApprovers.Any(y => y.ApproverCode == "")), "RequestLevel.RequestApprovers");

Error
{"ORA-00904: \"Project1\".\"C1\": invalid identifier"}

So how can i rewrite the above expressions ?

Thanks in advance.

[Shalex]

Sorry, this question exceeds the goals of our support. You have encountered a limitation of Oracle server.