3 Replies Latest reply: Feb 14, 2012 11:00 AM by 917242 RSS

    Exception occurs in EntityCommand.ExecuteReader on my query

    917242
      I have downloaded the latest ODAC 11.2 R4 for Entity Framework on Oracle and VS2010.
      I have used EntityFramework on SQL Server with 3 different ways; EntitySQL, Sprocs, and LinqToEntiteis.
      I am on a Project with Oracle and I would like to use ODP.net with Entity Framework in VS 2010.
      I have installed the latest ODP.net for VS2010. I am using 11.2.0.3.0 Oracle Developer Tools for Visual Studio 2010.
      I have a Datasoure with multiple schemas brought into a EDMx Entity model. Now
      I am writing some tests for Entity Framework on Oracle. LinqToEntites works great so far
      but testing with EF's EntitySql is failing to find schema which is in the EDMX file on all the tables in the query.

      The test fails on ecmd.ExcuteReader with and EntitySqlException:

      System.Data.EntitySqlException: 'ESP.CUSTOMER' could not be resolved in the current scope or context. Make sure that all referenced variables are in scope, that required schemas are loaded, and that namespaces are referenced correctly. Near member access expression, line 1, column 61.

      What do I need using EntitySQL on ODAC 11.2.0.3.0?


      using System;
      using System.Linq;
      using System.Data;
      using System.Data.EntityClient;

      using Microsoft.VisualStudio.TestTools.UnitTesting;
      using EFData.Data;

      namespace EFData.Tests
      {
      [TestClass]
      public class EntityFrameworkTests
      {
      [TestMethod]
      public void FindDefaultCustomersWithEntitiesSQL()
      {
      string esql = @"SELECT ESP.CUSTOMER.CustomerId, ESP.CUSTOMER.LEGAL_NAME FROM ESP.CUSTOMER WHERE ESP.CUSTOMER.PURCHASE_ID= 1";

      using (var conn = new EntityConnection("name=MyEntities"))
      {
      conn.Open();
      EntityCommand cmd = conn.CreateCommand();

      cmd.CommandText = esql;
      //
      //EntitySqlException on schema happens on next
      //
      EntityDataReader reader = cmd.ExecuteReader(CommandBehavior.Default);//CommandBehavior.SequentialAccess);

      while (reader.Read())
      {
      long id = (Int64)reader.GetDecimal(0);
      Assert.IsTrue(id > 0);
      string legal_name = reader.GetString(1);
      Assert.IsFalse(string.IsNullOrEmpty(legal_name));
      break;
      }
      }
      }
      }
      Any ideas?

      Edited by: 914239 on Feb 13, 2012 5:14 PM
        • 1. Re: Exception occurs in EntityCommand.ExecuteReader on my query
          917242
          And my LinqToEntities test method is returning a record properly.

          [TestMethod]
          public void FindOneMatchingGEDefaultCustomersWithLinq2Entities()
          {
          // LINQ to Entities query --Find Default Customers with PURCHASE_ID = 1
          using (var ctx = new MyEntities())
          {
          var Customers = from e in ctx.CUSTs
          where e.CUST_LEGAL_NAMEToUpper() == "GENERAL ELECTRIC"
          select e;
          Assert.IsNotNull(Customers);
          Assert.IsTrue(Customers.Any());
          var Customer = Customers.FirstOrDefault();
          Assert.IsNotNull(Customer);
          }
          }
          Any Ideas why Linq2Entities and a Data Source query window in server explorer returns this record but my test using Oracle EF's EntitySql does not return same record?

          Edited by: 914239 on Feb 13, 2012 8:02 PM

          Edited by: 914239 on Feb 13, 2012 8:05 PM
          • 2. Re: Exception occurs in EntityCommand.ExecuteReader on my query
            Tridus
            >
            // LINQ to Entities query --Find Default Customers with PURCHASE_ID = 1
            using (var ctx = new MyEntities())
            {
            var Customers = from e in ctx.CUSTs
            where e.CUST_LEGAL_NAMEToUpper() == "GENERAL ELECTRIC"
            select e;
            The two queries aren't doing the same thing. Your esql one is looking for purchase_id = 1, and your linq one is looking for cust_legal_name = "general electric".
            • 3. Re: Exception occurs in EntityCommand.ExecuteReader on my query
              917242
              Yes I know.
              My point was that LinqToEntities queries are working. Here is the same working version as my post but it uses Linq2Entities and ODP.net to Oracle db
              (instead of EntitiesSql and ODP.net to Oracle db). For some reason the EntitiesSql EntityCommand & EntitiyDataReader objects do not know about the schema or table names
              in the sql.
              //
              //This test is a Linq2Entities with Oracle db and it works unlike my first post using the EF EntitiesSql.
              //
              [TestMethod]
              public void FindCustomersWithLinq2Entities()
              {
              // LINQ to Entities query --
              using (var ctx = new MyEntities())
              {
              var Customers = from e in ctx.Customers
              where e.PurchaseID == 1
              select e;
              Assert.IsNotNull(Customers);
              Assert.IsTrue(Customers.Any());
              }
              }

              I am wondering why the EntitiesSql code (in my first post) does not see the schema from the Edmx model. Am I missing calling a method before executing the EntityDataReader to include the Schema?