I've been using NHibernate and Castle ActiveRecord for a few months, building a complex web application with it. In my repository, I've been using DetachedCriteria and ICriteria with many hindrances, so I decided to experiment with LINQ because of popularity (meaning more helpful information when searching on Google, which proven most valuable). I find LINQ to be very pleasing with strongly-typed syntax (newbie friendly).
Even though, I found a solution to my query through trial and error. I found some limitations and bugs with NHibernate.Linq. Here's my setup, I have a many to many relationship with an association table between: Group.cs, GroupMember.cs, User.cs Using plain SQL Syntax (what I'm trying to archive): SELECT groupName FROM Groups WHERE groupId NOT IN ( SELECT memberGroupId FROM GroupsMembers WHERE memberUserId = '3e0d22d2-83c9-443d-978b-9c7000264f74' ) Using NHibernate DetachedCriteria and ICriteria (the query works, but took me many hours to figure out because lack of information/ documentation): public static IList<Models.Group> GetGroupsNotByMember(Guid userId) { return Models.Group.FindAll(DetachedCriteria.For<Models.Group>().Add( Restrictions.Eq("groupIsDeleted", false) ).Add( Subqueries.PropertyNotIn("groupId", DetachedCriteria.For<Models.GroupMember>().SetProjection( Projections.Property("memberGroup.groupId") ).Add( Restrictions.Eq("memberUser.userId", userId) )) )); } Most ideal solution using LINQ, but got an error "System.ArgumentException : Unhandled Expression Type: 1000", probably NHibernate.Linq is limited: public static IList<Models.Group> GetGroupsNotByMember(Guid userId) { return ( from g in Models.Group.Queryable where g.groupIsDeleted == false && !( from gm in Models.GroupMember.Queryable where gm.memberUser.userId == userId select gm.memberGroup.groupId ).Contains(g.groupId) select g ).ToList(); } Workaround with LINQ (causing to send two queries to the database): public static IList<Models.Group> GetGroupsNotByMember(Guid userId) { var memberGroups = ( from g in Models.GroupMember.Queryable where g.memberUser.userId == userId select g.memberGroup.groupId ).ToArray(); return ( from g in Models.Group.Queryable where g.groupIsDeleted == false && memberGroups.Contains (g.groupId) select g ).ToList(); } >From debugging NHibernate.Linq, I found a query containing unnecessary things, the "not" clause on groupIsDeleted and multiple parenthesises. I think it's a bug: ... WHERE (not ((this_.groupIsDeleted = ?p0)) and not ((this_.groupId in (?p1))));?p0 = True, ?p1 = 29dbf697-cdcb-49a4-ab8a Specs: ASP.NET 3.5 SP1 MVC 1.0 MySQL 5.0.24a MySQL Connector/Net 6.1.1 NHibernate 2.1.0.GA NHibernate.Linq 1.0.0.GA Castle ActiveRecord 2.0 Sincerely, William Chang --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "nhusers" group. To post to this group, send email to nhusers@googlegroups.com To unsubscribe from this group, send email to nhusers+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/nhusers?hl=en -~----------~----~----~----~------~----~------~--~---