I think that this should do it: select u.Id, r.Name <http://u.role.name/> from User u left outer join u.Role r
On Wed, Dec 2, 2009 at 7:03 PM, Steve Strong <[email protected]> wrote: > Just porting over the last few tests from the 1.0 provider, and came across > this one: > > from user in db.Users > select new > { > Id = user.Id, > RoleName = user.Role.Name > }; > > pretty simple stuff, and I generate the following HQL: > > select u.Id, u.Role.Name from User u > > and then stick this through a result transformer to build the required > anonymous type. The problem is that it does not return any users that do > not have a Role, since the sql generated essentially joins the User & Role > tables. > > If you run the same query in Linq2Sql, you get back all users, with a null > RoleName for those that have no role. It's generating an outer join in the > sql to achieve this. I can tweak the HQL to be this: > > select u.Id, u.Role.Name from User u left outer join u.Role > > which then generates the correct SQL but still only returns results where > Role is not null. The v1.0 provider is going through the Criteria API, > where doing a projection of "Role.Name" is returning null when there is no > role. > > The question is whether there is any simple syntax to get HQL to behave in > the same way as the criteria API? I've tried a few combinations, but every > time I reference u.Role I fail to get rows back where that is null. I'm > hoping I'm missing something obvious here! > > Cheers, > > Steve > >
