Fluent NHibernate Using Multiple Database

Summary

This time I’m going to show how to use tables from two different databases in one context. It’s difficult to find information on this subject and many links point to an article that uses a dictionary of context’s with all kinds of fancy configuration and code to map things. After an hour of hunting around in that rabbit hole (technically, the technique being presented works across different database servers, where the version I’m looking for is multiple databases in one server), I stumbled onto something that Fluent NHibernate has that makes it all soooo clear. Inside the class mappings for each table you can specify the exact table mapping in the database. This can be used in situations where you want to use a different name for your class object table name than the database table name. It can also be used to define which database your table is accessed from.

Setting Up

The ERD I’m going to use looks like this:

I’m trying to keep this simple, so there are really no integrity constraints as shown in the diagram above. Each class can have one teacher, and each student can only be in one class (but classes can have more than one student). This is not really practical, but it’s just a minor example. Technically, I could have shown this example with two tables, but I want to demonstrate two tables in one database and one table in a different database. The databases are named facultydata and studendata.

Don’t forget to populate your database with some data. I hand-typed this data into the three tables shown:

On to the Code

Here’s the class for the Student table with the mappings. This is where the magic occurs:

public class Student
{
    public virtual int Id { get; set; }
    public virtual string Name { get; set; }
    public virtual int classid { get; set; }
}

public class StudentMap : ClassMap<Student>
{
    public StudentMap()
    {
        Id(u => u.Id);
        Map(u => u.Name).Nullable();
        Map(u => u.classid).Not.Nullable();
        Table("studentdata..student");
    }
}

Notice the addition of the “Table” mapping. Also notice that I introduced the database name right in the table mapping string. NHibernate will replace the tablename in any “where” clause of it’s queries with the whole text inside this mapping.

How do I do the LINQ Query?

No difference. Here’s the query that I used:

using (ISession session = NHibernateHelper.OpenSession())
{
    var query = (from t in session.Query<Teacher>()
            join c in session.Query<Class>() on t.Id equals c.TeacherId
            join s in session.Query<Student>() on c.Id equals s.classid
            select new 
            { 
                TeacherName = t.Name,
                ClassName = c.Name,
                StudentName = s.Name
            }).ToList();
}

That is the only change from previous NHibernate blog examples.  You can download the entire Visual Studio 2012 project here: 

NHibernateMultipleDatabases.zip

What if I have Two Tables with the Same Names?

For this situation, I would use the mapping to indicate which table is being referenced, then change the class name to reflect which table. Let’s say that you had a student table in two different databases (who knows why, but this stuff happens). You can name each class with a combination of database name and table name like StudentdataStudent and FacultydataStudent. When you form your LINQ query, you’ll use the long name version of your tables and the ORM will map to the table in the correct database.