I have had a maddening time this morning with NHibernate. I have a set of tables like this:
aspnet_users
Profile
aspnet_membership
Club
The scenario is that a user has a membership record and a profile. A profile specifies the club that the user belongs to. The club has an instructor (essentially another user).
I am presenting this information on screen in an ASP.Net application in a grid with these columns:
Username, Surname, First Name, Initials, Club Name, Instructor Name, Email Address, Is Active
The user can sort ascending or descending by each of these fields.
A user does not have to have a profile with a valid club, so to get all the users listed, outer joins are required. I am using NHibernate and an HQL query to retrieve the data. All goes well until the user sorts by Club name. At this point, the records with no Club vanish from the grid...
A bit of digging (and SQL profiling) reveals that when an HQL query sorts by a column it has a bizarre WHERE clause that effectively eliminates any records that have null values. It took a while to work these out as the SQL generated by NHibernate is somewhat arcane.
I tried a couple of solutions relating to inserting a dummy record for "No Club", but this had its own share of issues. Eventually I hit upon the idea of creating a SQL Server View for the basic query with the outer joins. NHibernate seems quite happy to accept a View as a Table. I then let NHibernate do the relevant searching, sorting and row retrieval.
Hey Presto! The whole thing works within 10 minutes! All rows are displayed regardless of sort order and whether or not they are missing related records from a left outer join.
NHibernate is hard work. I hope that the full SP1 is out for .Net 3.5 soon so I can try Entities instead. I am resisting downloading the current Beta, but it starts to look more tempting the more I struggle with NHibernate...