Yes, if course! Been staring at this stuff too much and missing the
obvious. Thanks
Sent from my iPhone
On 2 Dec 2009, at 18:04, Ayende Rahien <[email protected]> wrote:
I think that this should do it:
select u.Id, r.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
