Dear All,
I am using NHibernate 2.1.0.3001. And using FluentNHibernate for
mapping. When I am trying to get data using Projections.SqlFunction
of ICriteria. The parameters get messed up. The same issue was raised
when I used NHibernate 2.0.1.400 . At that time the same issue was
happened when using Projections.Conditional of ICriteria. That issue
was solved when I used NHibernate 2.1.0.3001 version. But the
parameter mess up issue with Projections.SqlFunction still exists.
Here I am attaching the code, test method and result.
Business Logic Method:
--------------------------------------------------------------------------
public IList<PageControlDTO> GetPageControlMappingDetails
(string page, long userId)
{
IList<PageControlDTO> pageContrlList = null;
try
{
IList<long> privilageList = this.GetPrivilageList
(page, userId);
object[] privilageArray = new object
[privilageList.Count];
for (int i = 0; i < privilageList.Count; i++)
{
privilageArray[i] = privilageList[i];
}
// Criteria for User
ICriteria criteria = SessionManager.OpenSession
().CreateCriteria(typeof(PageControlMapping));
criteria.CreateAlias(PageControlMapping.PRIVILEGEID,
PageControlMapping.PRIVILEGEID);
// Availability 1 means active users in that project.
criteria.Add(Expression.Eq(PageControlMapping.PAGEID,
page));
// Adding data to projectionquery w.r.t above
criteria.
ProjectionList objList = Projections.ProjectionList()
.Add(Projections.Property
(PageControlMapping.PAGECONTROLMAPPINGID),
PageControlDTO.PAGECONTROLMAPPINGID)
.Add(Projections.Property
(PageControlMapping.PAGEID), PageControlDTO.PAGEID)
.Add(Projections.Property
(PageControlMapping.CONTROLSECTION), PageControlDTO.CONTROLSECTION)
.Add(Projections.Property
(PageControlMapping.PRIVILEGEID + "." + Privilege.PRIVILEGEID),
PageControlDTO.PRIVILEGEID)
.Add(Projections.Property
(PageControlMapping.PRIVILEGEID + "." + Privilege.PRIVILEGENAME),
PageControlDTO.PRIVILEGENAME)
.Add(Projections.Property
(PageControlMapping.CONTROLPROPERTY), PageControlDTO.CONTROLPROPERTY)
.Add(Projections.Conditional
(Restrictions.In(Projections.Property(Privilege.PRIVILEGEID),
privilageArray),
new ConstantProjection(1), new ConstantProjection(0)),
PageControlDTO.PRIVILEGE);
// Invoke DBUtility.TransformToDTO by Criteria and
ProjectionList
pageContrlList =
DBUtility<PageControlDTO>.TransformToDTO(criteria, objList);
foreach (PageControlDTO pageContrl in pageContrlList)
{
DetachedCriteria userRolesCriteria =
DetachedCriteria.For(typeof(UserRole));
userRolesCriteria.Add(Expression.Eq
(UserRole.USERID + "." + User.USERID, userId));
userRolesCriteria.SetProjection
(Projections.ProjectionList().
Add
(Projections.Property(UserRole.ROLEID)));
ICriteria rolePrivilegecriteria =
SessionManager.OpenSession().CreateCriteria(typeof(RolePrivilege));
rolePrivilegecriteria.Add(Expression.Eq
(RolePrivilege.PRIVILEGEID + "." + Privilege.PRIVILEGEID,
pageContrl.PrivilegeId));
rolePrivilegecriteria.Add(Subqueries.PropertyIn
(RolePrivilege.ROLEID, userRolesCriteria));
rolePrivilegecriteria.SetProjection
(Projections.ProjectionList().
Add
(Projections.Max(Projections.SqlFunction("substring",
NHibernateUtil.String,
Projections.Property(RolePrivilege.SCOPETYPE),
new
ConstantProjection(5),
new
ConstantProjection(1)))));
string scopeType =
rolePrivilegecriteria.UniqueResult<string>();
if (scopeType.Equals("1"))
{
pageContrl.Privilege = 1;
}
}
}
catch (Exception ex)
{
ErrorHandler.WriteLog(ex);
throw ex;
}
return pageContrlList;
}
-----------------------------------------------------------------------------------------------------------------------------------
Test Method:
------------------------
[Test]
[Row("ActivityCodeDetails", 308)]
public void TestGetPageControlMappingDetails(string page, long
userId)
{
SecurityDAO roleDAO = new SecurityDAO();
IList<PageControlDTO> pageContrlList =
roleDAO.GetPageControlMappingDetails(page, userId);
}
---------------------------------------------------------------------------------------------------------------------------------------
Generated Query :
--------------------------
NHibernate: select roleprivil0_.PrivilegeId as col_0_0_ from
PMO_tblSecRolePrivilege roleprivil0_, PMO_tblSecUserRoles userrole1_,
MIS_tblUsers user2_, EIS_tblEmployeeStatusHistory employeest3_ where
roleprivil0_.RoleId=userrole1_.RoleId and
userrole1_.UserId=user2_.User_Id and
employeest3_.User_Id=user2_.User_Id and
(employeest3_.EmployeeStatus_Id in (1 , 2 , 3)) and user2_.User_Id=308
NHibernate: SELECT this_.PageControlMappingId as y0_, this_.PageId as
y1_, this_.ControlSection as y2_, privilegei1_.PrivilegeId as y3_,
privilegei1_.PrivilegeName as y4_, this_.ControlProperty as y5_, (case
when this_.PrivilegeId in (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7,
@p8, @p9, @p10, @p11, @p12, @p13, @p14, @p15, @p16, @p17, @p18) then
@p19 else @p20 end) as y6_ FROM PMO_tblSecPageControlMapping this_
inner join PMO_tblSecPrivileges privilegei1_ on
this_.PrivilegeId=privilegei1_.PrivilegeId WHERE this_.PageId =
@p21;@p0 = 110, @p1 = 3, @p2 = 193, @p3 = 215, @p4 = 215, @p5 = 248,
@p6 = 247, @p7 = 242, @p8 = 249, @p9 = 246, @p10 = 262, @p11 = 244,
@p12 = 243, @p13 = 241, @p14 = 261, @p15 = 358, @p16 = 457, @p17 =
458, @p18 = 459, @p19 = 1, @p20 = 0, @p21 = 'ActivityCodeDetails'
NHibernate: SELECT max(substring(this_.ScopeType, ?, ?)) as y0_ FROM
PMO_tblSecRolePrivilege this_ WHERE this_.PrivilegeId = @p0 and
this_.RoleId in (SELECT this_0_.RoleId as y0_ FROM PMO_tblSecUserRoles
this_0_ WHERE this_0_.UserId = @p1);@p0 = 5, @p1 = 1, @p2 = 457, @p3 =
308
-------------------------------------------------------------------------------------------------------------------------------------------
Here the parameters for substring is not assigned when the query is
generated.
And wrong parameters are assigned to PrivilegeId and UserId . This is
the issue.
Please provide me suggestions ASAP
Regards
Arun
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"nhusers" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/nhusers?hl=en
-~----------~----~----~----~------~----~------~--~---
