Fluent Nhibernate over legacy database in oracle

Question

Hello , I am trying to connect to the existing db in oracle with fluentmapping . I got Mapping over CUstomer

public CustomerMapping()
    {

         Not.LazyLoad();
        Id(x => x.Cst_Recid).GeneratedBy.Increment() ;
    }

and i am trying to create session

public static ISessionFactory CreateSessionFactory()
    {
        return Fluently
            .Configure()
            .Database(OracleClientConfiguration.Oracle10.ConnectionString
            ("...."))
            .Mappings(m =>
             {
                 m.FluentMappings.AddFromAssemblyOf<CustomerMapping>();

             })
             .BuildConfiguration() 
             .BuildSessionFactory();
    }

i have some trial class to try and create the sessionFactory

public class MyDataProvider
{
    public static  Customer GetCustomerById(long customerId)
    {
        ISessionFactory sessionFactory = SessionFactory.CreateSessionFactory(); 
        ISession session = sessionFactory.OpenSession();
          return session.Linq<Customer>().Where(x => x.Cst_Recid.Equals(temp)).FirstOrDefault();

    }


}

i am not being able to get the Customer by Id even though I am getting to open session and activating ...

the test is very simple - only to check the select activity

    [Test]
    public void CanGetCustomerById()
    {
        MyDataProvider provider = new MyDataProvider();
        Assert.AreEqual(33941, MyDataProvider.GetCustomerById(33941).Cst_Recid);

    }

there is a mistake -

TestCase '...DataLayer.Tests.CustomerMappingTests.CanGetCustomerById' failed: NHibernate.ADOException : could not execute query [ select * from ( SELECT this_.Cst_Recid as Cst1_0_0_, this_.Cst_Customerid as Cst2_0_0_, this_.Cst_First_Name as Cst3_0_0_, this_.Cst_Group_Recid as Cst4_0_0_, this_.Cst_Insdbdt as Cst5_0_0_, this_.Cst_Insdbuser as Cst6_0_0_, this_.Cst_Joingroup_Dt as Cst7_0_0_, this_.Cst_Last_Name as Cst8_0_0_, this_.Cst_Lastupddt as Cst9_0_0_, this_.Cst_Lastupduser as Cst10_0_0_, this_.Cst_Tat_Lakoach_Meshalem as Cst11_0_0_, this_.Cst_Typeid as Cst12_0_0_, this_.Cst_Tziyun_Meshalem_Rashi_Only as Cst13_0_0_, this_.Cst_Tziyun_Mizdamen as Cst14_0_0_ FROM "Customer" this_ WHERE this_.Cst_Recid = :p0 ) where rownum <=:p1 ] Positional parameters: #0>33941 [SQL: select * from ( SELECT this_.Cst_Recid as Cst1_0_0_, this_.Cst_Customerid as Cst2_0_0_, this_.Cst_First_Name as Cst3_0_0_, this_.Cst_Group_Recid as Cst4_0_0_, this_.Cst_Insdbdt as Cst5_0_0_, this_.Cst_Insdbuser as Cst6_0_0_, this_.Cst_Joingroup_Dt as Cst7_0_0_, this_.Cst_Last_Name as Cst8_0_0_, this_.Cst_Lastupddt as Cst9_0_0_, this_.Cst_Lastupduser as Cst10_0_0_, this_.Cst_Tat_Lakoach_Meshalem as Cst11_0_0_, this_.Cst_Typeid as Cst12_0_0_, this_.Cst_Tziyun_Meshalem_Rashi_Only as Cst13_0_0_, this_.Cst_Tziyun_Mizdamen as Cst14_0_0_ FROM "Customer" this_ WHERE this_.Cst_Recid = :p0 ) where rownum <=:p1] ----> System.Data.OracleClient.OracleException : ORA-00942: table or view does not exist

the query that he is trying to run is build automaticly by FluentNHibernate . If i remove the quoates the query executes right , it gets the result .. the trouble is that i can not change the query as i want .. maybe the problem is that we are using Oracle 11 g and FluentNhibernate adjusted only to Oracle 9 or 10 ?

will appreceate any help.

Answer 1

My previous answer was incorrect. Allow me to try again!

When you quote an object name in Oracle, it becomes case-sensative. Your CUSTOMER table is being quoted as "Customer" which is not the same as being quoted "CUSTOMER":

SQL> select * from "dual";
select * from "dual"
              *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> select * from "Dual";
select * from "Dual"
              *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> select * from "DUAL";

D
-
X

I still don't know anything about Fluid NHibernate, but is it possible to get it to look for a "CUSTOMER" table instead of a "Customers" table?

Alternatively, if nothing else is looking for a CUSTOMERS table, you could rename it to "Customers"... however, this will break references to a CUSTOMERS table:

SQL> create table CUSTOMERS (x int);

Table created.

SQL> insert into CUSTOMERS (x) values (1);

1 row created.

SQL> select * from CUSTOMERS;

         X
----------
         1

SQL> select * from "Customers";
select * from "Customers"
              *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> select * from "CUSTOMERS";

         X
----------
         1

SQL> alter table CUSTOMERS rename to "Customers";

Table altered.

SQL> select * from CUSTOMERS;
select * from CUSTOMERS
              *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> select * from "Customers";

         X
----------
         1

Good luck! I hope this helps...