NHibernate Query Generator - More Collections Magic

time to read 5 min | 828 words

I wouldn't have expected it to be this hard*, but it is alive!

Here is the query:

   1:  User one = User.FindOne(
   2:         Where.User.Blogs
   3:              .With(JoinType.InnerJoin)
   4:                   .Name == "Ayende @ Blog"
   5:  );

And the generated SQL:

   1:  SELECT 
   2:     this_.Id as Id4_1_,
   3:     this_.Name as Name4_1_,
   4:     this_.Email as Email4_1_,
   5:     blog1_.Id as Id3_0_,
   6:     blog1_.Name as Name3_0_,
   7:     blog1_.Author as Author3_0_ 
   8:  FROM Users this_ inner join Blogs blog1_ 
   9:     on this_.Id=blog1_.Author 
  10:  WHERE blog1_.Name = @p0

And, because Rob has asked, here is how you do a more complex query, over Many To Many association:

   1:  User one = User.FindOne(
   2:      Where.User.Name == "Ayende" &&
   3:      Where.User.Roles.With().Name == "Administrator"
   4:  );

And the generated SQL:

   1:  SELECT this_.Id as Id0_1_,
   2:     this_.Name as Name0_1_,
   3:     this_.Email as Email0_1_,
   4:     roles3_.UserId as UserId__,
   5:     role1_.Id as RoleId,
   6:     role1_.Id as Id9_0_,
   7:     role1_.Name as Name9_0_ 
   8:  FROM Users this_ inner join UsersRoles roles3_ 
   9:     on this_.Id=roles3_.UserId 
  10:        inner join Roles role1_ 
  11:           on roles3_.RoleId=role1_.Id 
  12:  WHERE this_.Name = @p0 and role1_.Name = @p1

It is in the repository now, but right now I would consider it beta stage, it works, but the generation will probably die if you have any sort of interesting schemas (specifically, if you have collections of value types, it is supposed to die horribly with confusing error message).

I intend to do a screen cast about this area of querying, searching and persistence soon.

* Right now code generation competes with template magic and three stars code in my internal dislike list. Trying to get the sort of syntax that you see about is really stretching C# to its limits. When can I get a Method Missing implementation on C#? That would actually make my life easier?