For Fluent NHibernate experts: Join with where condition

Question

SQL Server syntax is:

select tableAColumn1, tableAColumn2, tableBColumn1
from tableA, tableB
where ISNUMERIC(tableAColumn1) = 1
and CONVERT(INT, tableAColumn1) = tableBColumn1
and tableAColumn2 = 'something'

What would be the best way to achieve this in Fluent NHibernate? How many classes would I need to help get the resulting ClassMap and how would it look like?

Edit:

public class BarausInfoMap : ClassMap<BarausInfo>
    {
        public BarausInfoMap()
        {
            Table("BARAUS");

            Id(x => x.nr);

            Map(x => x.betrag);

            Join("BARAUSLANG", m =>
            {
                m.Fetch.Join();

                m.KeyColumn("Ula");
                m.Map(x => x.bezeichnung);
                m.Map(x => x.sprache);
                m.Map(x => x.la);
                this.Where("m.la = 'SPE'");
            });
        }
    }

nr column is int and ula column is string, but I need to join those 2. also, the this.where refers to the outer table I guess, it should however refer to the inner table.

Answer 1

Maybe it's better to use separate entities and separate mapping, than you build query like

queryOver<BarausInfo>.JoinQueryOver(x => x.BarauslangObject, barauslangAlias, JoinType.InnerJoin, conjunction)

and the conjunction will contain the ISNUMERIC(tableAColumn1) = 1 and tableAColumn2 = 'something'. And n the BarausInfo Mapping you can spefify something like References(v => v.BarauslangObject).Formula("CONVERT(INT, tableAColumn1) = tableBColumn1"). Just find out how to specify columns in formula properly.