none
EF Code First - Composite Key - A LINQ says Oracle Exception ('ORA-00932') RRS feed

  • Question

  • Hello, 

    I used EF code fist approach. 
    When I access Oracle data by LINQ, a LINQ says "ORA-00932: inconsistent datatypes: expected - got NCLOB". 
    Please note: I didn't compare any NCLOB type in the below LINQ. 

    --------------------------------------------------- 

    IQueryable<Table1> query = context.Table1s.AsNoTracking();
    var temp = from job in query.AsQueryable() join j in query on job.Id equals j.Id into jobGroup select jobGroup;
    query = temp.SelectMany(j => j).Distinct();

    int count = query.Count();  // Exception: "ORA-00932: inconsistent datatypes: expected - got NCLOB"

    --------------------------------------------------- 

    Please note: 

    1. Here, Column1 is NCLOB type. But, I didn't compare this in the above LINQ.

    2. "Please note: If I comment the below line then it works fine. 
    ' modelBuilder.Entity<Table1>().HasKey(j => new { j.Id, j.Id2 }); ' " 

    --------------------------------------------------- 

    FYI

    Oracle said that,
    “We didn't compare any NCLOB type from Oracle driver side.
     You need to check with Microsoft Support regarding better coding from EF side.”

    --------------------------------------------------- 

    Code


    namespace ConsoleApplication1
    {
        class Program
        {
            static void Main(string[] args)
            {
                using (var context = new Context())
                {
                    IQueryable<Table1> query = context.Table1s.AsNoTracking();
                    var temp = from job in query.AsQueryable() join j in query on job.Id equals j.Id into jobGroup select jobGroup;
                    query = temp.SelectMany(j => j).Distinct();

                    int count = query.Count();  // Exception: "ORA-00932: inconsistent datatypes: expected - got NCLOB"
                }
            }
        }

        public class Context : DbContext
        {
            public DbSet<Table1> Table1s { get; set; }

            public Context() :
                //base("Test")
                base(@"User Id=C##USER1;Password=user1;Data Source=localhost:1521/oracle")
            {
                Database.SetInitializer<Context>(new DropCreateDatabaseIfModelChanges<Context>());
            }

            protected override void OnModelCreating(DbModelBuilder modelBuilder)
            {
                string OracleUserNameInUpperCase = "C##USER1";
                modelBuilder.HasDefaultSchema(OracleUserNameInUpperCase);

                modelBuilder.Entity<Table1>().HasKey(j => new { j.Id, j.Id2 });  
            }
        }

        public class Table1
        {
            public int Id { get; set; }
            public int Id2 { get; set; }

            public string Column1 { get; set; }  // NCLOB Type on Oracle - Will have 10000 characters
        }
    }
    --------------------------------------------------- 

    Could you give suitable workaround?

    Thanks,

    A Murugan


    Murugan

    Thursday, May 7, 2015 7:07 AM

Answers

  • Hello Murugan,

    >>public string Column1 { get; set; }  // NCLOB Type on Oracle - Will have 10000 characters

    It seems that there is a limit of the number of character: An "ORA-00932 : inconsistent datatypes" error can be encountered if a string of 2,000 or more characters, or a byte array with 4,000 bytes or more in length, is bound in a WHERE clause of a LINQ/ESQL query. The same error can be encountered if an entity property that maps to a BLOB, CLOB, NCLOB, LONG, LONG RAW, XMLTYPE column is used in a WHERE clause of a LINQ/ESQL query: http://stackoverflow.com/a/17324250. However, I am not sure since I am not experienced with Oracle database, you could confirm it with Oracle database support forum.

    >>Oracle said that,“We didn't compare any NCLOB type from Oracle driver side. You need to check with Microsoft Support regarding better coding from EF side.”

    From your provided query, you are using IQueryable in LINQ to Entities, so that the LINQ to Entities providers would generate the T-SQL and send it to the local database to execute it. So if there are comparisons, they would be executed on database side, or you could try to use LINQ to Object to check if it is working by using .ToList().

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, May 8, 2015 2:32 AM
    Moderator

All replies

  • Hello Murugan,

    >>public string Column1 { get; set; }  // NCLOB Type on Oracle - Will have 10000 characters

    It seems that there is a limit of the number of character: An "ORA-00932 : inconsistent datatypes" error can be encountered if a string of 2,000 or more characters, or a byte array with 4,000 bytes or more in length, is bound in a WHERE clause of a LINQ/ESQL query. The same error can be encountered if an entity property that maps to a BLOB, CLOB, NCLOB, LONG, LONG RAW, XMLTYPE column is used in a WHERE clause of a LINQ/ESQL query: http://stackoverflow.com/a/17324250. However, I am not sure since I am not experienced with Oracle database, you could confirm it with Oracle database support forum.

    >>Oracle said that,“We didn't compare any NCLOB type from Oracle driver side. You need to check with Microsoft Support regarding better coding from EF side.”

    From your provided query, you are using IQueryable in LINQ to Entities, so that the LINQ to Entities providers would generate the T-SQL and send it to the local database to execute it. So if there are comparisons, they would be executed on database side, or you could try to use LINQ to Object to check if it is working by using .ToList().

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, May 8, 2015 2:32 AM
    Moderator
  • Hello Fred Bao,

    Thank you for your answer.

    I will use 'LINQ to Object '

    Thank you

    A Murugan


    Murugan


    Friday, May 8, 2015 3:00 AM