Filling Objects with NHibernate through Multiple Databases

Question

I have one SQL Server with multiple databases. Database 1 has a table with reference to identifiers that are stored in a table in database2. Not sure if this is possible, but can I configure NHibernate (Fluent NHibernate specifically) to saturate an object that retrieves data from multiple databases?

I'm not worried about writing to these tables, I'm just trying to ORM the objects displayed in the application to view the data.

I understand that this is not an ideal database situation, but this is what I was tasked with working with.

Answer 1

The usual response to db-specific query structures, such as queries from a cross-database, is to create a view of the "local" database (to which NH connects), which will execute the cross-database query and return the combined results. You can also have a repository for each database and develop some tools for querying records from each database and combining them manually.

One thing that will also work; the table property for each display is just a string and can be anything; NHibernate simply takes this and connects it wherever it needs to refer to the table name. So, you can try to specify tables in mappings using their fully qualified names: ConnectedDB..LocalTable, OtherDB..RemoteTable. This can be considered a hack, but it is also quite elegant; your program does not even need to know that there are several databases in the save scheme.