How to add a WHERE clause on the second table of a 1-to-1 join in Fluent NHibernate?
- by daddywoodland
I'm using a legacy database that was 'future proofed' to keep track of historical changes. It turns out this feature is never used so I want to map the tables into a single entity.
My tables are:
CodesHistory (CodesHistoryID (pk), CodeID (fk), Text)
Codes (CodeID (pk), CodeName)
To add an additional level of complexity, these tables hold the content for the drop down lists throughout the application.
So, I'm trying to map a Title entity (Mr, Mrs etc.) as follows:
Title ClassMap -
Public Sub New()
Table("CodesHistory")
Id(Function(x) x.TitleID, "CodesHistoryID")
Map(Function(x) x.Text)
'Call into the other half of the 1-2-1 join in order to merge them in
'this single domain object
Join("Codes", AddressOf AddTitleDetailData)
Where("CodeName like 'C.Title.%'")
End Sub
' Method to merge two tables with a 1-2-1 join into a single entity in VB.Net
Public Sub AddTitleDetailData(ByVal m As JoinPart(Of Title))
m.KeyColumn("CodeID")
m.Map(Function(x) x.CodeName)
End Sub
From the above, you can see that my 'CodeName' field represents the select list in question (C.Title, C.Age etc).
The problem is that the WHERE clause only applies to the 'CodesHistory' table but the 'CodeName' field is in the 'Codes' table.
As I'm sure you can guess there's no scope to change the database.
Is it possible to apply the WHERE clause to the Codes table?
