Multiple-valued join in one column erroneously leads to SELECT

Question

I have a class that looks like this:

[Class(Table = "SessionReportSummaries", Mutable = false)] public class SessionReportSummaries { [ManyToOne(Column = "ClientId", Fetch = FetchMode.Join)] public Client Client { get; private set; } [ManyToOne(Column = "ClientId", Fetch = FetchMode.Join)] public ClientReportSummary ClientReportSummary { get; private set; } } 

There is a ClientId column in the SessionReportSummaries view, and I'm trying to join the Client object and the ClientReportSummary object using this column.

Unfortunately, NHibernate only wants to join the first one defined in the class, and always does SELECT for the second. Therefore, in this scenario, NHibernate queries the database first with:

 SELECT {stuff} FROM SessionReportSummaries ... left outer join Clients on this.ClientId=Clients.Id ... 

(with lots of other associations), and then N of them:

 SELECT {stuff} FROM ClientReportSummary WHERE ClientReportSummary.ClientId = '{id goes here}' 

one for each of the N clients in question. This leads to terrible results.

If I swap the positions of the Client and ClientReportSummary objects, NHibernate instead attaches the ClientReportSummary to the SessionReportSummaries object and makes a selection for each Client object.

Does anyone know how I can get NHibernate to make a connection for both?

Answer 1

NHibernate will only accept one mapping of one column in one query. So, since the "ClientId" attribute displays two different objects, mapped by the column attribute:

• [ManyToOne (Column = "ClientId", Fetch = FetchMode.Join)]
• [ManyToOne (Column = "ClientId", Fetch = FetchMode.Join)]

The uniqueness of displaying columns in this case is not provided. And this can lead to damage if you insert or update the form, both objects will be applied. But we can use the trick: FORMULA mapping

 [Class(Table = "SessionReportSummaries", Mutable = false)] public class SessionReportSummaries { [ManyToOne(Column = "ClientId", Fetch = FetchMode.Join)] public Client Client { get; private set; } [ManyToOne(Formula = "ClientId", Fetch = FetchMode.Join)] public ClientReportSummary ClientReportSummary { get; private set; } } 

Now NHibernate will display one colum map as a real relation and evaluate the second (defined in FORMULA ) as another. The single select operator will now be used.

When FORMULA used for display (instead of column ), it should be marked as insert="false" and update="false" . We only need this for SELECT. (otherwise, we can add Client and ClientReportSummary with different ClientId objects for SessionReportSummaries - which will violate the exception ...

The second approach can be one-to-one, where it is expected that the "ClientId" will be really the same in all three tables ... but this is another topic