Trouble using Entity Framework 5 Power Tools with Oracle 11g and dotConnect

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

Trouble using Entity Framework 5 Power Tools with Oracle 11g and dotConnect

Post by phowe » Mon 15 Jul 2013 14:29

I have started a new thread for this post since I could not find a way to re-name the old one, and the problem is not the same...

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 '{'
This is the content of app.config file 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=INSTANTPAY_DEV;Password=IPDV_01;Server=IPDEVNV1:15211/IPDEV1;Home=OraClient11g_home3"
         providerName="Devart.Data.Oracle"/>
  </connectionStrings>

   <startup>
      <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5"/>
   </startup>

</configuration>


I can see from online examples is that I am missing a mapping class for the table, which Entity Framework obviously did not create for me. Can anyone tell me how I can get EF to create this code so I don't have to create it myself?

Any help would be greatly appreciated.

Thanks,
Peter

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

Re: Trouble using Entity Framework 5 Power Tools with Oracle 11g and dotConnect

Post by phowe » Mon 15 Jul 2013 17:52

I am continuing to post to myself, apparently. If someone could lend me a hand,that would be much appreciated. :(

I wrote the mapping class myself, but the "Extent1" stuff remains in the queries generated, so the error from the database ("ORA-00942: table or view does not exist") makes perfect sense from Oracle's perspective, but I still cannot see where "Extent1" is coming from any why it does not use the actual table name ("USERS") which is how the entity class is tagged:

Code: Select all

    [Table("USERS")]
    public class User
Anyone?

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

Re: Trouble using Entity Framework 5 Power Tools with Oracle 11g and dotConnect

Post by Shalex » Tue 16 Jul 2013 11:31

phowe wrote:I wrote the mapping class myself, but the "Extent1" stuff remains in the queries generated
This is an alias for the "Users" table in the query.
phowe wrote:"ORA-00942: table or view does not exist"
Seems like the name of your table in the database is "USERS" but the generated query looks for the "Users" table (different case for some characters). A possible reason is that the [Table("USERS")] attribute of the class User is ignored. It can be the result of migration .NET 4.0 -> .NET 4.5. Unfortunately, we cannot reproduce the issue with your code in our environment. Could you please send us a complete small test project for reproducing?

A tip: you can use a dbMonitor tool for tracing the queries which are sent to the server:
http://www.devart.com/dotconnect/oracle ... nitor.html
http://www.devart.com/dbmonitor/dbmon3.exe
phowe wrote:I am continuing to post to myself, apparently.
Our support has a two business days response policy: http://www.devart.com/dotconnect/oracle ... pport.html. But we do our best to respond as soon as possible.

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

Re: Trouble using Entity Framework 5 Power Tools with Oracle 11g and dotConnect

Post by phowe » Tue 16 Jul 2013 12:32

My apologies for the sarcastic remark, I think I was under the impression that there was a development community behind the forum in addition to your company.

I finally got some output from my test application, but am dismayed that I needed to write the mapping class myself, I thought the EF Power Tools were going to do that for me, but they do not seem to work as advertised when not connected to SQL Server (Microsoft's issue, not yours...) Admittedly, our database is quite large, and the program sat for over a day just generating the entity classes. A great feature would be the ability to specify a subset (bounded context) of the database at a time rather than trying to bite it all off at once. Toad's schema browser tells me there are 379 tables... :roll:

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

Re: Trouble using Entity Framework 5 Power Tools with Oracle 11g and dotConnect

Post by phowe » Tue 16 Jul 2013 15:59

Hi,

Sorry again for the lousy attitude yesterday.

The biggest problem I have right now is trying to get the EF Power Tools to work. My first "run" with Oracle's data provider did not go well. We have 379 tables in our application's schema but the Power Tools created close to 3,000 entity classes, so they obviously grabbed EVERYTHING from the server including all the Oracle system tables, etc. The connection we used was specific to the application but the Power Tools seemed to have a mind of their own once connected to the database.

The second attempt did not go much better. That one was with dotConnect, which sat for at least a day, saying it was "generating entity classes" but I ended up having to kill it off.

We really would like to be able to specify a subset (i.e., bounded context) for the Power Tools to do their reverse-engineer magic on, but cannot find any way to do that. Once the program gets the database connection, it just grinds until it's finished or killed off.

Thanks,
Peter

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

Re: Trouble using Entity Framework 5 Power Tools with Oracle 11g and dotConnect

Post by Shalex » Wed 17 Jul 2013 15:49

Please try using Entity Developer (the Devart Entity Model item): add the DbContext template with "Fluent Mapping=True" to model. The "Metadata Artifact Processing" property of the model set to "Do Not Generate Mapping Files". This will generate a detailed complete mapping for your classes and properties.

Entity Developer has a lot of features comparing to standard EDM Wizard / Designer: http://www.devart.com/entitydeveloper/ed-vs-edm.html.

Post Reply