3

I have the following Criteria query (using Lambda extensions):

var workflowResult = repository.GetSession() 
                .CreateCriteria<Core.Domain.Application>() 
                .SetFetchMode<Core.Domain.Application>(app => app.ApplicationWorkflows, FetchMode.Join)  
                .SetResultTransformer(new DistinctRootEntityResultTransformer()) 
                .Future<Core.Domain.Application>();

This is working correctly. Each Application eagerly loads the ApplicationWorkflows collection. However, I'd like to go one deeper and load the ApplicationStatus object of each ApplicationWorkflow. I can do this with the following HQL but would like to translate to Criteria: 

var workflowQuery = "SELECT DISTINCT app" + 
                               " FROM Application app" + 
                               " JOIN FETCH app.ApplicationWorkflows awf" + 
                               " JOIN FETCH awf.ApplicationStatus";

I've been advised to use the following, but am having issues with it working in all cases:

.SetFetchMode<Core.Domain.Application>(app => app.ApplicationWorkflows[0].ApplicationStatus, FetchMode.Join)
Mike Cole
  • 14,474
  • 28
  • 114
  • 194
  • Are you sure the HQL works? I've tried something similar in the past and had trouble with duplicates. `DistinctRootEntity` didn't do what I expected when doing recursive join-fetches. – Gabe Moothart Jul 12 '10 at 17:05
  • Hmmm, I thought it did. Let me check to make sure. Thanks for the heads up! – Mike Cole Jul 12 '10 at 17:31
  • [This answer](http://stackoverflow.com/questions/5266180/fighting-cartesian-product-x-join-when-using-nhibernate-3-0-0/5285739#5285739) helped me see how to use QueryOver and Future queries to eagerly fetch children and grandchildren without returning duplicates. The technique involves breaking the task down into separate SQL queries that are executed in one roundtrip to the database. – David McClelland Oct 20 '11 at 17:00

1 Answers1

0

Try this.

var workflowResult = repository.GetSession() 
                .CreateCriteria<Core.Domain.Application>() 
                .CreateAlias("ApplicationWorkflows", "awf") 
                .SetFetchMode("ApplicationWorkflows", FetchMode.Join)  
                .SetFetchMode("awf.ApplicationStatus", FetchMode.Join)  
                .SetResultTransformer(new DistinctRootEntityResultTransformer()) 
                .Future<Core.Domain.Application>();
Amitabh
  • 59,111
  • 42
  • 110
  • 159
  • The last time I checked, `DistinctRootEntity` did not work when you do grandchildren fetching like this. Make sure this doesn't return duplicates. – Gabe Moothart Jul 12 '10 at 17:03