Have Employee: BaseEntity
class with
public virtual BaseEntity Department {get;set;}
public virtual BaseEntity Position {get;set;}
and BaseEntity
class:
public virtual long Id {get;set;}
public virtual long Name {get;set;}
How can I map my entities and /create select like this
SELECT DepartmentTable.nvarchar1 as Department, PositionTable.nvarchar1 as Position, COUNT(*) as N
FROM AllUserData EmployeeTable
left outer join AllUserData PositionTable on EmployeeTable.int2=PositionTable.tp_ID
left outer join AllUserData DepartmentTable on EmployeeTable.int3=DepartmentTable.tp_ID
WHERE EmployeeTable.tp_ListId = @p0 and PositionTable.tp_ListId = @p2 and DepartmentTable.tp_ListId = @p1
GROUP BY DepartmentTable.nvarchar1, PositionTable.nvarchar1;
I tried like this:
var criteria = session.CreateCriteria<Entities.Employee>();
criteria.CreateAlias("Position", "PositionTable", JoinType.LeftOuterJoin);
criteria.Add(Restrictions.Or(Restrictions.Eq("PositionTable.ListId", Program.PositionListGuid), Restrictions.IsNull("PositionTable.Id")));
criteria.CreateAlias("Department", "DepartmentTable", JoinType.LeftOuterJoin);
criteria.Add(Restrictions.Or(Restrictions.Eq("DepartmentTable.ListId", Program.DepartmentListGuid), Restrictions.IsNull("DepartmentTable.Id")));
criteria.Add(Restrictions.Eq("ListId", Program.EmployeeListGuid));
var projectionList = Projections.ProjectionList();
projectionList.Add(Projections.RowCount());
projectionList.Add(Projections.GroupProperty("Department"), "AliasedId");
criteria.SetProjection(projectionList);
criteria.SetResultTransformer(Transformers.AliasToBean(typeof(BaseEntity)));
var list = criteria.List<BaseEntity>();
// "More than one row with the given identifier was found" exception raised here
I'am confused, should I somehow use joined table alias for Projections.GroupProperty, rework my mapping/Entity to use only primitive types (string, int) or any other approach?
Figured out:
Add int Number
to BaseEntity
and string DepartmentName
, string PositionName
to Employee
. Now I can use Projections to map Sql grouped by result to this properties:
var pl = Projections.ProjectionList()
.Add(Projections.RowCount(), GetEntityProperty(typeof (BaseEntity), "Number"))
.Add(Projections.GroupProperty("DepartmentTable.Title"), "PositionName")
.Add(Projections.GroupProperty("PositionTable.Title"), "DepartmentName");
ICriteria criteria = session.CreateCriteria<Employee>()
.CreateAlias("Position", "PositionTable", JoinType.LeftOuterJoin)
.Add(Restrictions.Eq("PositionTable.ListId", Program.PositionListGuid))
.CreateAlias("Department", "DepartmentTable", JoinType.LeftOuterJoin)
.Add(Restrictions.Eq("DepartmentTable.ListId",Program.DepartmentListGuid))
.Add(Restrictions.Eq("ListId", Program.EmployeeListGuid))
.SetProjection(pl)
.SetResultTransformer(Transformers.AliasToBean<BaseEntity>());
var list = criteria.List<BaseListEntity>().ToList();