SQL Server: transforming query for materialized path traversing in a view

Question

I would like to transform this query in a view that I can call passing the UserId as a parameter to select all groups (including subtrees of those groups) related to that user.

In fact I have 2 tables:

• "EVA_Roles" which contains the roles tree, defined through a materialized path in the RoleHid field as varchar(8000)
• "EVA_UsersInRoles" which related users to roles through the field UserId

Problem here is that only some roles may be related to the user in the EVA_UsersInRoles table, but maybe those roles are parents to other roles in the tree hierarchy so I have to retrieve multiple subtrees for each user. Finally I came up with this query which seems to work fine, but I would like to transform it in a View. The problem I'm facing of course is that the UserId parameter, which is the one I would use to filter the view results, is inside the subquery.

Any hint to refactor this into a view?

SELECT A.RoleId, E.EndDate FROM EVA_Roles A INNER JOIN EVA_Roles B ON
A.RoleHid LIKE B.RoleHid + '%' AND B.RoleHid IN (SELECT RoleHid FROM EVA_Roles C
LEFT JOIN EVA_UsersInRoles D ON C.RoleId = D.RoleId WHERE 
(D.Userid = @0 OR C.RoleId = @1) AND C.ApplicationId = @2) 
LEFT JOIN EVA_UsersInRoles E ON A.RoleId = E.RoleId AND E.UserId = @0 WHERE
A.ApplicationId = @2 ORDER BY A.RoleId

I left parameters where I should pass values to the view. I think it may be impossible to refactor in a view. It was just to exploit my micro-ORM (PetaPoco) in a more friendly way, otherwise I have to use the SQL in my code but it's ok, don't loose your mind on this.

About the tables definition:

EVA_Roles
RoleId INT - Primary Key
RoleHid VARCHAR(800) - Here I store the materialized path of the tree using nodes
ids... An example on this later.
RoleLevel INT - Security level of the role
RoleName INT - Name of the role (admin, guest, ...)
ApplicationID INT - Id of the application (in a multi app scenario)

EVA_UsersInRoles
RoleId - INT
UserId - INT

The materialized path in RoleHid follows this logic. Consider this data where RoleId 2 is child of RoleId 1:
RoleId 1
RoleHid "1."

RoleId 2
RoleHid "1.2."

With the query above I'm able to retrieve all subtrees tied to a specific user and application.