Multiple many-to-one joins on same column erroneously results in SELECT being performed

Question

I have a class which 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; }
}

The SessionReportSummaries view has a ClientId column, and I’m trying to join both a Client object and a ClientReportSummary object using this column.

Unfortunately, NHibernate only wants to join the first one defined in the class, and always performs a SELECT for the second one. So 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 joins), and then N of these:

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

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

If I swap the positions of the Client and ClientReportSummary objects then NHibernate instead joins ClientReportSummary onto the SessionReportSummaries object, and performs a select for each Client object.

Does anyone know how I can get NHibernate to perform a join for both of these?

Answer 1

NHibernate will take only one same column mapping in a single query. So, because there are two different entities mapped via column attribute to value "ClientId":

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

The uniqueness of column mapping is in this case not granted. And it could cause damage, when insert or update form both entities would be applied. But we can use a 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 take one colum mapping as real relation and evaluate the second (defined in formula) as a different one. Now single select statement will be used

Whenever formula is used for mapping (instead of column), it should be marked as insert="false" and update="false". We need it just for a SELECT. (otherwise we can append a Client and a ClientReportSummary with different ClientId to SessionReportSummaries entity - which will violate exception...

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