Gotcha: DistinctRootEntityResultTransformer Doesn’t Play Well with Restricted Result Sets

I’ll be honest, the main reason for the last post was to make sense of this one.  Consider the following code:

public User UserByName(string name)
{
    return session.Linq<Person>()
        .Cached()
        .DistinctRoot()
        .Expand("UserRoles")
        .Where(u => u.WindowsUserName == name)
        .FirstOrDefault();
}

It’s exactly what you’d hope for.  Nice and explicit: you want the first person whose windows user name matches ‘name’ and to pull back the person’s roles at the same time.  Because you understand how eager fetching works, you’ve slapped a distinct root in there too.

Pity the code’s wrong.  The gotcha is the way that NHibernate.Linq interprets FirstOrDefault.  To be clear, this isn’t a bug, it’s definitely the right behaviour.  FirstOrDefault translates to a “top 1” in SQL Server (or a Limit in others).  DistinctRootEntityResultTransformer works after the query has run.

So, you will get at most one Person object back, but you’ll also get at most one Role back, which would undoubtedly lead to problems elsewhere in your code.  Try writing an example program to demonstrate this and get it to print the SQL you run.

So, how do you deal with it?  Well, you need to stop FirstOrDefault getting translated into the SQL.  So we use my favourite LINQ defeater:  ToList. 

public User UserByName(string name)
{
    return session.Linq<Person>()
        .Cached()
        .DistinctRoot()
        .Expand("UserRoles")
        .Where(u => u.WindowsUserName == name)
        .ToList()
        .FirstOrDefault();
}

Now that code actually does what you wanted.  Of course, there’s a catch: if there really were two people with the same windows user name, it would fetch them both.  But at least your code is now correct.