Fluent NHibernate and Oracle

In this topic, I would like to highlight the Fluent NHibernate library in conjunction with Oracle and give a small example. There are few articles on this topic now (mentioned in Habré ), even fewer descriptions of interaction with Oracle. Naturally, most are not in Russian. However, the library deserves attention.

The authors set themselves the task to rid NHibernate of XML (xmlless, so to speak). Therefore, in our project it will not be. Totally. Instead, a fluent (“fluid” or “flexible”) interface is provided that uses lambda expressions and method chains for setting up a database and mapping entities.

Requisites

We will need:

• Oracle Server (in my case 11.2.0.1.0)
• Oracle DAC (Data Access Components, in my case 11.2.0.2.1), Oracle Client or any other provider of your access to the Oracle database.

To avoid problems in the future, make sure that you can connect to the database, for example, using third-party utilities. Client settings are located at:

%Oracle DAC/Client%\product\11.2.0\client_1\Network\Admin\tnsnames.ora

In my case, the file has the form:
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = orasrvxp)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)


Create a project

Create a new console project in Visual Studio 2010 and name it FluentExample. I use the NuGet Package Manager to quickly find, get, and install the necessary packages. Library installation can be done manually .
')
We right-click on the project file and click "Add Library Package Reference ...". In the Online tab, in the search field, enter “fluent”:



In addition to Fluent NHibernate itself, all dependencies will be loaded (Castle.Core, Iesi.Collections, NHibernate, NHibernate.Castle).

I will build on the native example from the library site , it simply and fairly fully illustrates the capabilities of the library. The scheme has the form:

In the project, create two subfolders: Entities and Mappings, which will contain the entities and, accordingly, the display.

Describe entities

The first entity is an employee. The employee has a name, surname and place of work - the store. In the Entities folder, add a new class with the following code:

public class Employee
{
public virtual int Id { get ; private set ; }
public virtual string FirstName { get ; set ; }
public virtual string LastName { get ; set ; }
public virtual Store Store { get ; set ; }
}

* This source code was highlighted with Source Code Highlighter .

I draw your attention to two things:

• Hereinafter, the Id property and list (one-to-many) properties are declared with the private setter. Only NHibernate will set the values ​​for Id (through reflection), and we initialize the lists in the constructor.
• All properties are declared with the virtual modifier, since NHibernate will create proxy classes for our entities, and you must enable property overloading.

Next in line is the product that has the name, price and list of stores selling them:

public class Product
{
public virtual int Id { get ; private set ; }
public virtual string Name { get ; set ; }
public virtual double Price { get ; set ; }
public virtual IList<Store> StoresStockedIn { get ; private set ; }

public Product()
{
StoresStockedIn = new List <Store>();
}
}

* This source code was highlighted with Source Code Highlighter .

The list has a private setter and initialization occurs in the constructor, as described above.

The class of the store, which has a name, as well as lists of goods and staff:

public class Store
{
public virtual int Id { get ; private set ; }
public virtual string Name { get ; set ; }
public virtual IList<Product> Products { get ; private set ; }
public virtual IList<Employee> Staff { get ; private set ; }

public Store()
{
Products = new List <Product>();
Staff = new List <Employee>();
}

public virtual void AddProduct(Product product)
{
product.StoresStockedIn.Add( this );
Products.Add(product);
}

public virtual void AddEmployee(Employee employee)
{
employee.Store = this ;
Staff.Add(employee);
}
}

* This source code was highlighted with Source Code Highlighter .

In this class, we describe a little bit of logic, since NHibernate requires that both sides of the relationship be defined before saving related entities.

This is where the description of our entities ends. The linking table for the realization of the many-to-many relationship between the product and the store will be discussed below.

Mappim entity

Further, when using the classic NHibernate, you should start writing xml mapping files and base configurations. But it is here that Fluent NHibernate takes effect.

All the mapping classes described below are located in the Mappings folder. Let's start by displaying the Employee class.

using FluentExample.Entities;
using FluentNHibernate.Mapping;

namespace FluentExample.Mappings
{
public class EmployeeMap : ClassMap<Employee>
{
public EmployeeMap()
{

}
}
}

* This source code was highlighted with Source Code Highlighter .

The mapping code itself will be placed in the class constructor inherited from ClassMap from the FluentNHibernate library. In order for Fluent NHibernate to find mappings, it is necessary to declare classes with the public modifier. Immediately consider the full mapping:

public EmployeeMap()
{
Table( "Employee" );
Id(e => e.Id)
.GeneratedBy.Sequence( "Employee_seq" );
Map(e => e.FirstName);
Map(e => e.LastName);
References(e => e.Store);
}

* This source code was highlighted with Source Code Highlighter .

The first line, Table() , is responsible for the name of the table that is being mapped. Below we will see why you specify the name explicitly.

In the Id() method, we specify which field identifies the object (NHibernate: <id> ). If we used an auto-increment database, Fluent NHibernate would automatically recognize the type and assign the identity generator (NHibernate: <generator> ). But, since there is no auto-increment in Oracle, we will use sequences (sequence), which is indicated later in the method chain.

The next two lines indicate the display of scalar properties (NHibernate: <property> ), where the type and name are determined automatically. This is implemented using the property naming convention. The column name can also be specified manually using the Column() method, but I still recommend using the default conventions or setting your own. Read more about agreements .

The next line shows that our object refers to the Store class in the singular, that is, it implements the “many” side in relation to many-to-one. The name of the foreign key, again, following the conventions, is assumed to be Store_id. The foreign key column can be overridden by the Column() method. The References() method is used on the “many” side, on the other side of the relationship (“one”), the HasMany() method (NHibernate: <bag> ) will be applied, and the corresponding method of renaming the key field will be KeyColumn() .

Let's go back to the Table() method. The point here is that Fluent NHibernate defaults to quoting table names when displaying table names (this is the agreement), that is, our table now should have the name ”Employee”, along with quotes. In Oracle, case-only names are surrounded by quotes. In our example, I would not like to use quotes and leave the table names case-insensitive and without quotes, so I indicated them explicitly.

Let's go to the mapping store:

public class StoreMap : ClassMap<Store>
{
public StoreMap()
{
Table( "Store" );
Id(x => x.Id)
.GeneratedBy.Sequence( "Store_seq" );
Map(x => x.Name);
HasMany(x => x.Staff)
.Inverse()
.Cascade.All();
HasManyToMany(x => x.Products)
.Cascade.All()
.Table( "StoreProduct" );
}
}

* This source code was highlighted with Source Code Highlighter .

We see here the symmetric call HasMany() for the Employee class, which we discussed above. It also uses the HasManyToMany() method, which implements the many-to-many relationship between the store and the product through an intermediate table, whose name is specified in the chain by the Table() method.

The Inverse() method shows that the collection owner is the other end of the relationship, that is, the Employee class, and it will be saved first. Cascade.All() transfers events to related elements. That is, if we, for example, delete a store, then all links with products and all the staff of this store will also be deleted. For more information, see the NHibernate documentation.

Finally, let's display our product:

public class ProductMap : ClassMap<Product>
{
public ProductMap()
{
Table( "Product" );
Id(x => x.Id)
.GeneratedBy.Sequence( "Product_seq" );
Map(x => x.Name);
Map(x => x.Price);
HasManyToMany(x => x.StoresStockedIn)
.Cascade.All()
.Inverse()
.Table( "StoreProduct" );
}
}

* This source code was highlighted with Source Code Highlighter .

I hope that from the experience of the previous mappings you will not find anything new here. More on fluent mappings .

Now it's time to configure a connection to Oracle.

Connect to Oracle

We will connect using connectionString. Add a new file, App.config, to the project, and enter our connection string for the user fluent with the password nhibernate:

Copy Source | Copy HTML

1. <? xml version = "1.0" encoding = "utf-8" ? >
2. < configuration >
3. < connectionStrings >
4. < add name = "Oracle"
5. connectionString = "DATA SOURCE = orcl; PASSWORD = nhibernate; PERSIST SECURITY INFO = True; USER ID = fluent"
6. providerName = "Oracle.DataAccess.Client" />
7. </ connectionStrings >
8. </ configuration >

Next, you need to add a link to the Oracle.DataAccess assembly that comes with your chosen provider:



The adding links dialog will most likely be different - I use Productivity Power Tools .

Set the link to the property Copy Local = True , this indicates that this library should be copied to the folder with the output binary files of our project:



For convenience, we will use a small helper to open a session in Fluent NHibernate:

using FluentExample.Entities;
using FluentNHibernate.Cfg;
using FluentNHibernate.Cfg.Db;
using NHibernate;
using NHibernate.Driver;

namespace FluentExample
{
public static class FluentNHibernateHelper
{
private static ISessionFactory _sessionFactory;
public static ISessionFactory SessionFactory
{
get
{
if (_sessionFactory == null )
{
var dbConfig = OracleDataClientConfiguration.Oracle10
.ConnectionString(c => c.FromConnectionStringWithKey( "Oracle" ))
.Driver<OracleDataClientDriver>()
.ShowSql();

_sessionFactory = Fluently.Configure()
.Database(dbConfig)
.Mappings(m => m.FluentMappings.AddFromAssemblyOf<Employee>())
.BuildSessionFactory();
}
return _sessionFactory;
}
}

public static ISession OpenSession()
{
return SessionFactory.OpenSession();
}
}
}

* This source code was highlighted with Source Code Highlighter .

In the database configuration, we use the OracleDataClientConfiguration predefined class from the OracleDataClientConfiguration namespace and configure it to use the connection string we created and the NHibernate driver. You can also enable the option to display the generated SQL queries. Learn more about database configuration .

Next, perform the very configuration of NHibernate. We substitute our database configuration, indicate the source of mappings and ask for the session factory. Read more about fluent configuration .

At this stage, the session should succeed:

class Program
{
static void Main()
{
using (FluentNHibernateHelper.OpenSession())
{

}
}
}

* This source code was highlighted with Source Code Highlighter .

Test our base

Now your project should have a structure similar to this:



It's time to run a real test:

private static void Main()
{
using ( var session = FluentNHibernateHelper.OpenSession())
{
using ( var transaction = session.BeginTransaction())
{
var barginBasin = new Store { Name = "Bargin Basin" };
var superMart = new Store { Name = "SuperMart" };

var potatoes = new Product { Name = "Potatoes" , Price = 3.60 };
var fish = new Product { Name = "Fish" , Price = 4.49 };
var milk = new Product { Name = "Milk" , Price = 0.79 };
var bread = new Product { Name = "Bread" , Price = 1.29 };
var cheese = new Product { Name = "Cheese" , Price = 2.10 };
var waffles = new Product { Name = "Waffles" , Price = 2.41 };

var daisy = new Employee { FirstName = "Daisy" , LastName = "Harrison" };
var jack = new Employee { FirstName = "Jack" , LastName = "Torrance" };
var sue = new Employee { FirstName = "Sue" , LastName = "Walkters" };
var bill = new Employee { FirstName = "Bill" , LastName = "Taft" };
var joan = new Employee { FirstName = "Joan" , LastName = "Pope" };

// . ,
// many-to-many.
AddProductsToStore(barginBasin, potatoes, fish, milk, bread, cheese);
AddProductsToStore(superMart, bread, cheese, waffles);

//
AddEmployeesToStore(barginBasin, daisy, jack, sue);
AddEmployeesToStore(superMart, bill, joan);

//
session.SaveOrUpdate(barginBasin);
session.SaveOrUpdate(superMart);

transaction.Commit();
}

//
using (session.BeginTransaction())
{
var stores = session.CreateCriteria( typeof (Store)). List <Store>();
foreach ( var store in stores)
WriteStorePretty(store);
}

Console .ReadKey();
}
}

public static void AddProductsToStore(Store store, params Product[] products)
{
foreach ( var product in products)
store.AddProduct(product);
}

public static void AddEmployeesToStore(Store store, params Employee[] employees)
{
foreach ( var employee in employees)
store.AddEmployee(employee);
}

private static void WriteStorePretty(Store store)
{
Console .WriteLine(store.Name);
Console .WriteLine( " Products:" );

foreach ( var product in store.Products)
Console .WriteLine( " " + product.Name);

Console .WriteLine( " Staff:" );
foreach ( var employee in store.Staff)
Console .WriteLine( " " + employee.FirstName + " " + employee.LastName);

Console .WriteLine();
}

* This source code was highlighted with Source Code Highlighter .

That's all.
I also provide a script for generating a schema in Oracle.

What's next?

Unrealized functionality

The authors support a large part of the capabilities of the native library, but some features of NHibernate are not yet available, for example, <sql-insert> .

Automapping

Fluent NHibernate allows you to do mapping automatically, just follow the entered agreements or define your own.

Testing mappings

The library allows you to quickly and in the same style to test created mappings.

Full API documentation