I have following structure. 1) GroupMember.cs ``` public class GroupMember { public virtual int Id { get; set; } public virtual SystemUser User { get; set; } public virtual Group Group { get; set; } public virtual IList GroupDetail { get; set; } public virtual IList SystemUserDetail { get; set; } // Few more Properties are there } ``` 2) SystemUser.cs ``` public class SystemUser { public virtual int Id{get;set;} public virtual string DisplayName{get;set;} // Few more Properties are there } ``` Nhibernate files GroupMembers ``` <many-to-one entity-name="SysUse…

Sub query in Nhibernate

linq, nhibernate

Solution

You can use NHibernate's LINQ provider for this. The examples listed here, http://code.msdn.microsoft.com/101-LINQ-Samples-3fb9811b, are a good starting point for learning LINQ. Most of those examples are written in the `from ... select` syntax, but I prefer the extension method syntax:

var subquery = session.Query<GroupMember>()
    .Where(gm => gm.Group.Id == 5)
    .Select(gm => gm.User.Id);
var users = session.Query<User>()
    .Where(u => !subquery.Contains(u.Id));

Hopefully this example will get you started on the right track, and you'll be writing your own LINQ queries in no time.

Problem

I have following structure. 1) GroupMember.cs ``` public class GroupMember { public virtual int Id { get; set; } public virtual SystemUser User { get; set; } public virtual Group Group { get; set; } public virtual IList<Group> GroupDetail { get; set; } public virtual IList<SystemUser> SystemUserDetail { get; set; } // Few more Properties are there } ``` 2) SystemUser.cs ``` public class SystemUser { public virtual int Id{get;set;} public virtual string DisplayName{get;set;} // Few more Properties are there } ``` Nhibernate files GroupMembers ``` <?xml version="1.0" encoding="utf-8" ?> <hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" assembly="NSP.DataModel" namespace="NSP.DataModel.Account"> <class name="GroupMember" entity-name="SysGroupMember" table="SYS_DEF_GROUP_MEMBERS"> <id name="Id" column="id" type="Int32"> <generator class="identity"/> </id> <many-to-one entity-name="SysGroup" name="Group" column="GroupID" not-null="true" cascade="none"/> <many-to-one entity-name="SysUser" name="User" column="UserID" not-null="false" cascade="none"/> <property name="Status" type="int" not-null="false"> <column name="Status" not-null="false"/> </property> <property name="CreatedDate" type="datetime" not-null="false"> <column name="CreatedDate"/> </property> <property name="CreatedBy" type="int" not-null="false"> <column name="CreatedBy"/> </property> <property name="UpdatedDate" type="datetime" not-null="false"> <column name="UpdatedDate"/> </property> <property name="UpdatedBy" type="int" not-null="false"> <column name="UpdatedBy"/> </property> <bag name="GroupDetail" inverse="true"> <key column="Id"/> <one-to-many entity-name="SysGroup"/> </bag> <bag name="SystemUserDetail"> <key column="Id"/> <one-to-many entity-name="SysUser"/> </bag> </class> </hibernate-mapping> ``` SysUser ``` <?xml version="1.0" encoding="utf-8" ?> <hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" assembly="NSP.DataModel" namespace="NSP.DataModel.Authentication"> <class name="SystemUser" entity-name="SysUser" table="SYS_DEF_USER" abstract="true"> <id name="Id" column="id" type="Int32"> <generator class="identity"/> </id> <many-to-one entity-name="SysUserTypes" name="UserTypeId" column="UserTypeId" not-null="true" cascade="none" /> <property name="IsActive" column="IsActive" type="Boolean" not-null="true"/> <property name="IsLicensed" column="IsLicensed" type="Boolean" not-null="true"/> <property name="DisplayName" type="string" not-null="false"> <column name="DisplayName" length="128"/> </property> <property name="Email" column="Email" type="string" not-null="true" length="200"/> <property name="PasswordMD5HexHash" column="PasswordMD5HexHash" type="string" not-null="false"/> <bag name="UserTypeList" inverse="true"> <key column="UserTypeId"/> <one-to-many entity-name="SysUserTypes"/> </bag> </class> </hibernate-mapping> ``` I want to get the result using this query select * from sys_def_user where id not in (select UserId from SYS_DEF_GROUP_MEMBERS where GroupID =5) What can be the nhibernate syntax for this? Please its urget...

Original source