Cannot get connection string right with EF5 and Oracle 11g

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
phowe
Posts: 20
Joined: Fri 12 Jul 2013 16:51
Location: Maine

Cannot get connection string right with EF5 and Oracle 11g

Post by phowe » Fri 12 Jul 2013 17:14

I am trying to get some basic Entity Framework test code running, but cannot get past the database connection point. Drilling down into the exception that is thrown shows:

{"An error occurred while getting provider information from the database. This can be caused by Entity Framework using an incorrect connection string. Check the inner exceptions for details and ensure that the connection string is correct."}

inner:
{"The provider did not return a ProviderManifestToken string."}

inner:
{"Can not find Oracle home."}


The connection string I put in the app.config file was roughly the same as the one that Server Explorer (VS2012) showed for a successful database connection:

User Id=XXXXXXXXXXX;Server=XXXXXXXXXXXXXX;Persist Security Info=True
providerName=Devart.Data.Oracle

(Server Explorer showed "dotConnect for Oracle" as the provider name, which I even tried in the config file, but if course it could not find it...)

I guess I am just a noob when it comes to Entity Framework, but if you can point out the mistake without laughing too much, that would be great. :-)

Thanks,
Peter

phowe
Posts: 20
Joined: Fri 12 Jul 2013 16:51
Location: Maine

Re: Still have strange problem with Entity Framework 5 and Oracle 11g

Post by phowe » Mon 15 Jul 2013 12:47

I did manage to get the connection string sorted out, but now I have a different problem that I don't think will be so easy to solve without a leg up. I wrote some extremely simple code to test Entity Framework access to our database. I have a single entity class:

Code: Select all

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace Paychex.IP.Common.IPEntities
{
    [Table("USERS")]
    public class User
    {
        [Key]
        public long User_Id { get; set; }
        public virtual long Pclient_Id { get; set; }
        public string Password { get; set; }
        public string UserName { get; set; }
        public string User_Description { get; set; }
        public virtual long? Emp_Id { get; set; }
        public bool? Employee_only  { get; set; }
        public bool? Must_Change_Password { get; set; }
        public long? Failed_Logins { get; set; }
        public DateTime? Last_Login { get; set; }
        public long? Sequence_Number { get; set; }
        public bool? All_Divs { get; set; }
        public bool? All_Depts { get; set; 
        public string User_Role { get; set; }
        public DateTime? Begin_Support { get; set; }
        public DateTime? End_Support { get; set; }
        public string One_Time_Access { get; set; }
        public long? Cloned_From_User { get; set; }
        public string Email { get; set; }
        public string Reset_Email { get; set; }
        public DateTime? Reset_Timeout { get; set; }
        public long? Challenge_Failures { get; set; }
        public string Perm_User_Role { get; set; }
        public DateTime? Password_Changed_Date { get; set; }
    }
Some very basic data context code:

Code: Select all

using System.Data.Entity;
using System.Data.Entity.Infrastructure;

namespace Paychex.IP.Common.IPDBContext
{
    public class IPDBContext<TContext> : DbContext where TContext : DbContext
    {
        static IPDBContext()
        {
            Database.SetInitializer<TContext>(null);
        }

        public IPDBContext()
            : base("Name=IPDBContext")
        {

        }
    }

    public class UserSecurityContext : IPDBContext<UserSecurityContext>
    {
        public DbSet<User> Users { get; set; }
    }
And a simple test program to show some data:

Code: Select all

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Paychex.IP.Common.IPDBContext;
using Paychex.IP.Common.IPEntities;

namespace EntitiesConsoleTests
{
    class Program
    {
        private static void Main(string[] args)
        {
            Console.Write("Press <Enter> to begin...");
            Console.ReadLine();
            try
            {
                using (var db = new UserSecurityContext())
                {
                    var q = from usr in db.Users
                            orderby usr.UserName
                            select usr;
                    foreach (var u in q)
                    {
                        Console.WriteLine(u.UserName);
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("Exception: " + ex.Message);

            }
            Console.Write("Press <Enter>");
            Console.ReadLine();
        }
    }
}
With a break point on the single line of code inside the catch block, I see the following in the debugger, when I look at the variables in the watch window:
+ ex {"An error occurred while executing the command definition. See the inner exception for details."} System.Exception {System.Data.EntityCommandExecutionException}
+ q {SELECT
"Extent1"."User_Id",
"Extent1"."Pclient_Id",
"Extent1"."Password",
"Extent1"."UserName",
"Extent1"."User_Description",
"Extent1"."Emp_Id",
"Extent1"."Employee_only",
"Extent1"."Must_Change_Password",
"Extent1"."Failed_Logins",
"Extent1"."Last_Login",
"Extent1"."Sequence_Number",
"Extent1"."All_Divs",
"Extent1"."All_Depts",
"Extent1"."User_Role",
"Extent1"."Begin_Support",
"Extent1"."End_Support",
"Extent1"."One_Time_Access",
"Extent1"."Cloned_From_User",
"Extent1"."Email",
"Extent1"."Reset_Email",
"Extent1"."Reset_Timeout",
"Extent1"."Challenge_Failures",
"Extent1"."Perm_User_Role",
"Extent1"."Password_Changed_Date"
FROM "Users" "Extent1"
ORDER BY "Extent1"."UserName" ASC} System.Linq.IOrderedQueryable<Paychex.IP.Common.IPEntities.User> {System.Data.Entity.Infrastructure.DbQuery<Paychex.IP.Common.IPEntities.User>}
{SELECT Invalid expression term '{'
"Extent1"."User_Id", Identifier expected
"Extent1"."Pclient_Id", Identifier expected
"Extent1"."Password", Identifier expected
"Extent1"."UserName", Identifier expected
"Extent1"."User_Description", Identifier expected
"Extent1"."Emp_Id", Identifier expected
"Extent1"."Employee_only", Identifier expected
"Extent1"."Must_Change_Password", Identifier expected
"Extent1"."Failed_Logins", Identifier expected
"Extent1"."Last_Login", Identifier expected
"Extent1"."Sequence_Number", Identifier expected
"Extent1"."All_Divs", Identifier expected
"Extent1"."All_Depts", Identifier expected
"Extent1"."User_Role", Identifier expected
"Extent1"."Begin_Support", Identifier expected
"Extent1"."End_Support", Identifier expected
"Extent1"."One_Time_Access", Identifier expected
"Extent1"."Cloned_From_User", Identifier expected
"Extent1"."Email", Identifier expected
"Extent1"."Reset_Email", Identifier expected
"Extent1"."Reset_Timeout", Identifier expected
"Extent1"."Challenge_Failures", Identifier expected
"Extent1"."Perm_User_Role", Identifier expected
"Extent1"."Password_Changed_Date" Identifier expected
FROM "Users" "Extent1" End of expression expected
ORDER BY "Extent1"."UserName" ASC} ; expected
{"An error occurred while executing the command definition. See the inner exception for details."} Invalid expression term '{'
I am guessing there is a total newbie mistake or missing piece, but I do not know what it is. I don't know where the "Extent1" is coming from or why it is not looking for the columms from the USERS table.

Any help would be greatly appreciated.

Thanks,
Peter
Last edited by phowe on Mon 15 Jul 2013 13:09, edited 2 times in total.

phowe
Posts: 20
Joined: Fri 12 Jul 2013 16:51
Location: Maine

Re: Still have strange problem with Entity Framework 5 and Oracle 11g

Post by phowe » Mon 15 Jul 2013 13:01

Sorry, I forgot to post the app.config file contents for the test program:

Code: Select all

<?xml version="1.0"?>
<configuration>
  <configSections>
    <!-- For more information on Entity Framework configuration, visit http://go.microsoft.com/fwlink/?LinkID=237468 -->
    <section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=4.4.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false"/>
  </configSections>
  <entityFramework>
    <defaultConnectionFactory type="System.Data.Entity.Infrastructure.LocalDbConnectionFactory, EntityFramework">
      <parameters>
        <parameter value="v11.0"/>
      </parameters>
    </defaultConnectionFactory>
  </entityFramework>
  <connectionStrings>
    <clear/>
    <add name="IPDBContext"
         connectionString="User Id=IXXXXXXXXXXXXXXX;Password=XXXXXXX;Server=IXXXXXXXXXXXXXXXXX;Home=OraClient11g_home3;Persist Security Info=True"
         providerName="Devart.Data.Oracle"/>
  </connectionStrings>
  <startup>
<supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5"/>
</startup>
</configuration>


Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: Cannot get connection string right with EF5 and Oracle 11g

Post by Shalex » Tue 16 Jul 2013 11:39

phowe wrote:"Can not find Oracle home."
Possible reasons of the error (for future reference):
1. Make sure that Oracle Client Software is installed on your computer;
2. Specify the name of Oracle Home explicitly in the Home connection string parameter.
3. Place the content of the ORACLE_HOME variable on the first position in the PATH variable of your operating system;
4. Make sure that capacity (x86 or x64) of your application and the capacity of your Oracle Client are the same.

Entity Developer is a 32 bit application, so it cannot access the database via the x64 version of Oracle Client. Please install Oracle client x86 or connect using Direct Mode
phowe wrote:"ORA-00942: table or view does not exist"
We have answered you at http://forums.devart.com/viewtopic.php?f=1&t=27520.

Post Reply