Fluent NHibernate Many-to-One Join on a Substring

Question

I'm trying to map 2 tables together in Fluent Nhibernate, but the only way to join them is based on using the LEFT function on one of the columns. So a SQL join would look like this:

select * from TableA INNER JOIN TableB ON LEFT(TableA.ColA, 12) = TableB.ColB

Is there any way to map this in NHibernate?

Answer 1

I'm not 100% sure this will actually work. But Its probably worth a try. Lets say you have ClassA and ClassB. ClassA should have a reference to ClassB in the way you describe. Then you can add a property to ClassB that should contain the result of the LEFT sql statement. Lest call it BKey for now. Then ClassB would look something like this:

public class ClassB {
    public virtual string BKey { get; set; }
    //Other properties
}

Then when you map ClassB you can map a formula to the property BKey. That would look like this:

Map(x => x.BKey).Formula("(SELECT LEFT(b.[[columnname]], 12) FROM TableB as b WHERE b.Id = Id)");

This will take the result of that sql query and put in the property BKey. Then when you map that reference for ClassA you do something like this:

Reference(x => x.ClassB).PropertyRef(x => x.BKey).Column("[[TableAColumnName]]");

Again, I'm not sure that this will work, I haven't tried anything like that myself.