Joining across databases in Active Record

Question

Is there a way in Castle Active Record/NHibernate to join across two databases?

I have data in 2 tables in separate databases that I need to join together and return in one query resultset. If there isn't an OO-way to do it, can it be done using either hql or worst case, is there a standard way to execute dynamic sql and have it return back some sort of collection, such as a datatable?

Answer 1

If you have the two tables already mapped, but in separate session factories, I don't think you can do much about it. You'll have to "join" in your own code.

If that's not the case, you can map the foreign table through a database view, i.e.:

create view othertable as select * from otherdatabase..othertable

and map this view in ActiveRecord as if it were a local table.

Answer 2

Did you already tried map classes by database? In your ActiveRecord startup config

<config database="MsSqlServer2005" type="BaseClassDatabaseOne, MyAssembly">
</config>
<config database="MsSqlServer2005" type="BaseClassDatabaseTwo, MyAssembly">
</config> 

abstract class BaseClassDatabaseOne {}
abstract class BaseClassDatabaseTwo {}

[ActiveRecord]  
class MyClassModelTwo: BaseClassDatabaseTwo  
{  
    [PrimaryKey(PrimaryKeyType.Native)]
    public virtual Byte Id { get; set; }
}  

class MyClassModel: BaseClassDatabaseOne 
{
    [PrimaryKey(PrimaryKeyType.Native)]
    public virtual Byte Id { get; set; }

    [BelongsTo("Id")]
    public virtual MyClassModelTwo Two { get; set; }
}

I hope this help.