Join multiple table on same key in Fluent nHibernate
I am new to nHibernate. In my project I am trying to eliminate the database dependencies and hence using Fluent nHibernate. I am having problem while joining more than 2 tables without a direct PK-FK relation ship. Below are the tables I want to join 
And below are the class file for these tables
public class RoleMaster
{
public virtual int RoleId { get; set; }
public virtual string RoleName { get; set; }
public virtual string RoleType { get; set; }
public virtual string RoleDesc { get; set; }
public virtual int Status { get; set; }
}
public class UGRoleDetails
{
public virtual int UGRoleID { get; set; }
public virtual ProjectMaster projMaster { get; set; }
public virtual ProcMaster procMaster { get; set; }
public virtual SubProcMaster subProcMaster { get; set; }
public virtual RoleMaster RoleMaster { get; set; }
public virtual UGMaster UGID { get; set; }
public virtual int Active { get; set; }
public override bool Equals(object obj)
{
var other = obj as UGRoleDetails;
if (ReferenceEquals(null, other)) return false;
if (ReferenceEquals(this, other)) return true;
return this.UGRoleID == other.UGRoleID &&
this.projMaster == other.projMaster &&
this.procMaster == other.procMaster &&
this.subProcMaster == other.subProcMaster &&
this.UGID == other.UGID &&
this.RoleMaster == other.RoleMaster;
}
public override int GetHashCode()
{
unchecked
{
int hash = GetType().GetHashCode();
hash = (hash * 31) ^ UGRoleID.GetHashCode();
hash = (hash * 31) ^ projMaster.GetHashCode();
hash = (hash * 31) ^ procMaster.GetHashCode();
hash = (hash * 31) ^ subProcMaster.GetHashCode();
hash = (hash * 31) ^ UGID.GetHashCode();
hash = (hash * 31) ^ RoleMaster.GetHashCode();
return hash;
}
}
}
public class UserRoleDetails
{
public virtual int UserRoleId { get; set; }
public virtual int UGRoleID { get; set; }
public virtual ProjectMaster ProjMaster { get; set; }
public virtual ProcMaster procMaster { get; set; }
public virtual SubProcMaster subProcMaster { get; set; }
public virtual int UserId { get; set; }
public virtual RoleMaster RoleMaster { get; set; }
public virtual Nullable<int> Status { get; set; }
public override bool Equals(object obj)
{
var other = obj as UserRoleDetails;
if (ReferenceEquals(null, other)) return false;
if (ReferenceEquals(this, other)) return true;
return this.UserRoleId == other.UserRoleId &&
this.ProjMaster == other.ProjMaster &&
this.procMaster == other.procMaster &&
this.subProcMaster == other.subProcMaster &&
this.UserId == other.UserId &&
this.RoleMaster == other.RoleMaster;
}
public override int GetHashCode()
{
unchecked
{
int hash = GetType().GetHashCode();
hash = (hash * 31) ^ UserRoleId.GetHashCode();
hash = (hash * 31) ^ ProjMaster.GetHashCode();
hash = (hash * 31) ^ procMaster.GetHashCode();
hash = (hash * 31) ^ subProcMaster.GetHashCode();
hash = (hash * 31) ^ UserId.GetHashCode();
hash = (hash * 31) ^ RoleMaster.GetHashCode();
return hash;
}
}
}
and here is my fluent mapping for these classes
public class RoleMasterMappings : ClassMap<RoleMaster>
{
public RoleMasterMappings()
{
Id(x => x.RoleId);
Map(x => x.RoleName, "RoleName");
Map(x => x.RoleType, "RoleType");
Map(x => x.Status, "Status");
Table("RoleMaster");
}
}
public class UGRoleDetailsMap : ClassMap<UGRoleDetails>
{
public UGRoleDetailsMap()
{
CompositeId()
.KeyProperty(x => x.UGRoleID, "UGRoleID")
.KeyReference(x => x.ProjMaster, "ProjId")
.KeyReference(x => x.procMaster, "procId")
.KeyReference(x => x.subProcMaster, "SubProcId")
.KeyReference(x => x.UGID, "UGID")
.KeyReference(x => x.RoleMaster, "RoleId");
Map(x => x.Active, "Active");
References<ProjectMaster>(x => x.ProjId, "ProjId");
References<ProcMaster>(x => x.ProcId, "procId");
References<SubProcMaster>(x => x.SubProcId, "SubProcId");
References<RoleMaster>(x => x.RoleMaster, "RoleId");
References<UGMaster>(x => x.UGID, "UGID");
Table("UGRoleDetails");
}
}
public class UserRoleDetailsMap : ClassMap<UserRoleDetails>
{
public UserRoleDetailsMap()
{
CompositeId()
.KeyProperty(x => x.UserRoleId, "UserRoleId")
.KeyReference(x => x.ProjMaster, "ProjId")
.KeyReference(x => x.procMaster, "procId")
.KeyReference(x => x.subProcMaster, "SubProcId")
.KeyReference(x => x.UserId, "UserId")
.KeyReference(x => x.RoleMaster, "RoleId");
Map(x => x.Status, "Status");
Map(x => x.UserId, "UserId");
References<ProjectMaster>(x => x.ProjMaster, "ProjId");
References<ProcMaster>(x => x.procMaster, "procId");
References<SubProcMaster>(x => x.subProcMaster, "SubProcId");
References<RoleMaster>(x => x.RoleMaster, "RoleId");
Table("UserRoleDetails");
}
}
Now I want to write the Fluent nHibernate query for the below SQL. Here UGRoleDetails and UserRoleDetails are joining over RoleID which is the primary key for RoleMaster . And these 2 tables have no direct relation.
select UGR.UGId,Usr.UserId from UGRoleDetails UGR inner join UserRoleDetails UsR on UGR.RoleId=UsR.RoleId where usr.Status=1 order by UGR.UGId
Can any body help me how I could run the above query. I tried to do that but getting error. Four other tables ProjectMaster , ProcMaster , SubProcMaster , and UGMaster are just a table with ID-Name pair. Did not mention the classes for those here. Thanks in advance.
链接地址: http://www.djcxy.com/p/39462.html上一篇: iPhone：如何获得当前毫秒？