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

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