Join table with another table in a seperate database using NHibernate Query Language HQL

Question

I'm trying to join a table from another database using NHibernate. I basically prefix the table name with the DB name which I have done in the mapping files before and it has worked.

Please see my code below:

query.CreateAlias("OtherDatabase.SiteProduct","OtherDatabase.SiteProduct", JoinType.InnerJoin);

However, I get the following error message:

NHibernate.QueryException: could not resolve property: OtherDatabase of: XXX.XXXXX.Core.Domain.Catalog.Product

Is this possible to do in NHibernate? I'm assuming since there is no foreign key that ties the two tables together that maybe I need to specify what column the two tables need to be joined on?

Answer 1

Working with other DB/Table (accessible with current connection) is supported. The thing here is.. the path to such table must come from mapping.

We can use either standard mapped Class, or we can introduce just an dynamic dictinoary mapping. E.g.

<class entity-name="OtherDBTable" table="[otherDB].[schema].[Table]" ... >
  <id       name="ID"           column="id"      type="Int32" />
  <property name="PropertyName" column="ColName" type="String" />
  ...
</class>

JOIN without mapped relation

Despite of the title .. about HQL .. the broken statement

query.CreateAlias("...

seems to belong to CreateriaQuery... which we will use to JOIN this table

So, let's create standard CriteriaQuery

var query = session.CreateCriteria<MyInnerEntity>("rootAlias");

And we can join it to the dynamically mapped other DB/Table like this

query.CreateEntityAlias("otherAlias",
    Restrictions.EqProperty("rootAlias.OtherId", "otherAlias.ID"),
    JoinType.InnerJoin,
    "OtherDBTable"
);

and we can get the results

query.SetProjection(Projections.ProjectionList()
    .Add(Projections.Property("rootAlias.ID"))
    .Add(Projections.Property("rootAlias.OtherId"))
    .Add(Projections.Property("otherAlias.ID"))
    .Add(Projections.Property("otherAlias.PropertyName"))
);

and finally we can read it as a list of array (or transform it ...)

var list = query.List<object[]>();

JOIN with mapped relation

in case, that our mapping to otherDB/Table would contain a relation (e.g. many-to-one) .. we could even use standard CreateAlias .. but still ... the fact that we crossed boundaries of current DB.. must come from mapping