Hi,
I have 3 tables in my system
1. Credential (Id,Username,Password)
2. User(Id,FirstName,Lastname,CredentialId,RoleId)
3. UserRole(Id,RoleName)
User table has 2 references.
1. CredentialId->Credential.Id and 2. RoleId->UserRole.Id
I have used fluent hibernate the generate the mapping.
Now I have a problem. I have a query to find out all those usernames
which have a given RoleName
for this I need to join all these 3 tables by following query:-
Select Username,RoleName
from
Credential inner join
User on Credential.Id=User.credentialId
inner join UserRole on
UserRole.Id=User.RoleId
Here is the code which I am trying to execute:-
ICriteria criteria = Session.CreateCriteria<UserRole>()
.CreateAlias("Users", "user")
.CreateAlias("user.Credential",
"credential")
.Add(Expression.Eq("credential.Username",
username))
.SetResultTransformer(new
DistinctRootEntityResultTransformer())
.Add(Restrictions.Eq("RoleName",
role));
It is generating a wrong query for me
exec sp_executesql N'SELECT this_.Id as Id7_2_, this_.RoleName as
RoleName7_2_, user1_.Id as Id6_0_, user1_.FirstName as FirstName6_0_,
user1_.LastName as LastName6_0_, user1_.EmailAddress as
EmailAdd4_6_0_, user1_.PhoneNo as PhoneNo6_0_, user1_.MobileNo as
MobileNo6_0_, user1_.IsActive as IsActive6_0_, user1_.IsDeleted as
IsDeleted6_0_, user1_.Credential_id as Credential9_6_0_,
user1_.UserRole_id as UserRole10_6_0_, credential2_.Id as Id5_1_,
credential2_.Username as Username5_1_, credential2_.Password as
Password5_1_, credential2_.IsDeleted as IsDeleted5_1_,
credential2_.IsActive as IsActive5_1_ FROM [UserRole] this_ inner join
[User] user1_ on this_.Id=user1_.UserRole_id inner join [Credential]
credential2_ on user1_.Credential_id=credential2_.Id WHERE
credential2_.Username = @p0 and this_.RoleName = @p1',N'@p0
nvarchar(5),@p1 nvarchar(5)',@p0=N'sunil',@p1=N'Admin'
Here you can see that it is trying to join based on UserRole_id
column . Similar issue is for other entities as well.
Can some please help me with this. I am pretty new to nhibernate
--
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.
