nHibernate select query?

Question

I have an ado.net statement I want to convert to use NHibernate:

Dim sql As New StringBuilder()
sql.AppendLine("SELECT r.RoleId,  r.RoleName ")
sql.AppendLine("FROM dbo.aspnet_Roles r ")
sql.AppendLine("WHERE r.RoleId IN ")
sql.AppendLine(" (select roleID from dbo.MenuRole where menuId = @MenuId) ")
sql.AppendLine("Order By r.RoleName")

later, I populate the parameter with: cmd.Parameters.AddWithValue("@MenuId", menuId)

Considering I want to return an: IList(Of AspnetRole)

and I'm using:

Dim managerFactory As IManagerFactory = New ManagerFactory()
Dim roleManager As IAspnetRoleManager = managerFactory.GetAspnetRoleManager()

How do I build and use that query with nHiberate?

(P.S. I am using Codesmithtools and VB.net and VS2008 and SQL Server 2008)

Answer 1

First, you'd have to map the aspnet_roles table and the MenuRole table to their corresponding classes. Once you've mapped them, I'd also map a many-to-one MenuRole property to the AspnetRole class.

Once you've done that the criteria query should look something like this:

Dim c As ICriteria = Session.CreateCriteria(TypeOf(AspnetRole))
c.Add(Restrictions.Eq("Menu.Id", menuId)
return c.List(Of AspnetRole)