At work I have a section of code which I need to eager load all my data in order to pass that data back across the wire.  I thought I had it all setup correctly, but then I realized that I was not returning back unique results from my query.

Imagine I have the following Schema

My current need was to get all Destinations in the database (we know it is a small number and this is for the edit screen) and have it eager load the endpoint (which you actually get for free) and the Assigned_Message_Types

Originally setup my Criteria selection as below

var result = Session.CreateCriteria( typeof( Destination ) )                                
	.SetFetchMode( "Endpoint", FetchMode.Eager )
        .SetFetchMode( "AssignedMessageTypeses", FetchMode.Eager )        
        .List();

When I ran the query above, I expected to get 2 records back, one for each destination in the database.  However, I received 4 of them back.  The first 3 were the same, and the 4th one was different.  As you could imagine this is NOT what I expected.  I thought I would reach out to Kyle Baley (@kbaley) for some help on this, but turns out he was having the same issue recently.  Good thing for both of us the Twitterverse was able to lend a helpful hand.  James Kovacs (@jamesKovacs) along with someone else (sorry, forgot our handle) pointed both of us to the following 2 posts.

• Eager Loading aggregate with many child collections
• Get unique results from joined queries

I decided to get the usage of DistinctRootEntityResultTransformer a try, and the code below is what worked for me.

var result = Session.CreateCriteria( typeof( Destination ) )                                
	.SetFetchMode( "Endpoint", FetchMode.Eager )
        .SetFetchMode( "AssignedMessageTypeses", FetchMode.Eager )        
	
	// this is the magic line    
        .SetResultTransformer( new DistinctRootEntityResultTransformer() )
        .List();

By adding the DistinctRootEntityResultTransformer transformer to my query I now get 2 rows of data, which is exactly what I needed/wanted.

Till next time,