Querying for users in roles (or permissions)

Topics: Writing modules
Aug 2, 2011 at 6:34 PM

I'm still trying to navigate my way through the querying aspect of Orchard. I'm attempting to formulate a query that retreives all users that exist in a role (or a list of roles).  Eventually, I'll try to make this go against permissions instead of a role, but that'll be part 2 of this question.

I tried doing something like this:

var users = _cms
                .Query<UserPart, UserPartRecord>()

But I get a compile error:

UserRolesPartRecord must be convertible to ContentPartRecord in order to use as a parameter TRecord in the generic method IContentQuery<TPart>.Join<TRecord>()

I've written a million sql queries, but I just can't seem to get a good handle on the query setup for Orchard...can someone assist with writing this query with "Roles" and maybe then maybe with "Permissions?"

Aug 2, 2011 at 9:37 PM

I think you've hit a current limitation of Content Manager queries. Did you check out the code in RoleService?

Aug 2, 2011 at 9:44 PM

I did look at that, but didn't really see anything I could use for this issue.  

Aug 2, 2011 at 10:07 PM

Is this something that can be done with the IRepository<UserPartRecord>?  Or am I reduced to getting all the users and then filtering them out with a linq query, something like:

var users = _cms
var filteredUsers = from u in users where u.UserRoles.Contains("role1") select u;
(that may not be 100% correct syntax, but you get my point)
Aug 2, 2011 at 10:13 PM

Yes, we try to steer people to Content Manager as much as possible because this is the higher abstraction, but what can be done there is a subset of what would be useful. The repositories are closer to the metal, and enable a wideer range of queries, but are less resilient to changes to the platform. Finally, you can go all the way down to nHibernate, HQL and ICriteria and that is even richer but yet more fragile.

Filtering after calling List() is going to perform poorly if you have many users because the filtering will happen in-memory.

Aug 2, 2011 at 10:14 PM

Yeah, I would like to avoid the filtering after list option if I can.  So IRepository should be able to get me what I need? Is there a good example somewhere in the source that I can use to look at how this query would look?

Aug 2, 2011 at 10:18 PM

RoleService has some querying against the repository. Other service classes throughout the codebase have similar querying against repos.

Aug 2, 2011 at 10:19 PM

I've done the basic .Table syntax, but nothing with a join...I didn't find much that did joining, unless I wasn't searching for the right thing...

Aug 2, 2011 at 10:22 PM

It's basically Linq to nHib at this point, so joins between tables should work within the limits of nHibernate capabilities, I think.

Aug 4, 2011 at 10:59 PM

Can you give me an example of what the query for this would look like using IRepository?  I have been trying for a while now and I can't make it jive...

Aug 9, 2011 at 4:41 PM

I've tried performing joins in the past with nhib and they don't exactly play very nice...

I need to find some way to generate this query using IRepository (or some other method):

SELECT UserName,  UserId
FROM Users u
INNER JOIN UserRoles ur on u.UserId = ur.UserId
INNER JOIN Roles r on ur.RoleId = r.RoleId


Seems like it'd be pretty simple, and common, but I've had no luck. I checked out the RoleService but that just seems to be doing separate queries (e.g., Get a role, then loop through the permissions in that Role) instead of doing it all in one statement.


Aug 9, 2011 at 11:33 PM

You can always go native, at the nHib level. Dirty but will get the job done. I wish I had some time to research it, 'sorry I can't give you a better answer at this time. I think we should build a doc topic on custom querying.

Aug 10, 2011 at 2:06 PM

No problem Bertrand, you've always been responsive and I understand all that. I appreciate the response.

My strategy usually is, since we're so time-sensitive on our project that if I run into something that I think I'm going to have trouble with in Orchard, I post about it hoping that someone else has already faced the same issue and saves us some time.  Otherwise, it's very easy to spin our wheels on things.

As far as at the nHib level, that's what I've been struggling with. I've attempted to use joins in the past with nHib and I've always had trouble with it.  I'll keep digging and post back if I find anything.

Aug 10, 2011 at 6:34 PM

I tried the following:


var users2 = from u in _userRepository.Table
                         join ur in _userRolesRepository.Table on u.Id equals ur.UserId
                         select u;


And my view throws an error:


Line 26:             Assign to: @Html.DropDownList("AssignedUserId", Model.UserList, "Select...", new { id = "UsersList" })        
Line 27:             <div id='ReviewersContainer' style='display:none;'>
Line 28:             <p>Note</p>


The method Join is not implemented.

Just documenting my finds for other people's use...

Aug 10, 2011 at 7:03 PM

Well, talking about increasingly worse solutions, nHib does also support actual SQL queries.

Aug 10, 2011 at 7:04 PM

Yeah, I'm going to have to start looking into that...is that done via ISessionLocator?

Aug 10, 2011 at 7:05 PM

I'm not exactly sure but yes, very probably.

Aug 10, 2011 at 7:23 PM

Since I've over-exhausted the amount of time I am allowed to spend on this, I'm just going to create my own setting that I can join to in order to determine if the user should show up in my list. I don't like it, but it's really all I have to work with at the moment.  If anything comes to light on this, I'll be sure to post my findings.

Aug 12, 2011 at 4:17 PM

Well, my previous attempt to create that setting didn't satisfy my needs, so I'm back to finding a solution for this. 

I started to look at the source for ISessionLocator and put together a basic query:

var itemList = _sessionLocator.For(typeof (UserPartRecord))
                .CreateSQLQuery("SELECT Id, UserName FROM Orchard_Users_UserPartRecord")
                .AddEntity(typeof (UserPartRecord))

Strangely enough, this throws an ADO exception...it seems to have a problem with the email column, even though I'm not returning email. 

Stack trace:
2011-08-12 10:03:56,319 [41] NHibernate.Util.ADOExceptionReporter - Email
2011-08-12 10:06:41,770 [71] NHibernate.Util.ADOExceptionReporter - Email
2011-08-12 10:07:28,060 [71] Orchard.ContentManagement.Drivers.Coordinators.ContentPartDriverCoordinator - ADOException thrown from IContentPartDriver by Laughlin.PublishFlow.Drivers.PublishFlowPartDriver
NHibernate.ADOException: could not execute query
[ SELECT Id, UserName FROM Orchard_Users_UserPartRecord ]
[SQL: SELECT Id, UserName FROM Orchard_Users_UserPartRecord] ---> System.IndexOutOfRangeException: Email
   at System.Data.SqlClient.SqlDataReader.GetOrdinal(String name)
   at NHibernate.Driver.NHybridDataReader.GetOrdinal(String name)
   at NHibernate.Type.NullableType.NullSafeGet(IDataReader rs, String name)
   at NHibernate.Type.NullableType.NullSafeGet(IDataReader rs, String[] names, ISessionImplementor session, Object owner)
   at NHibernate.Type.AbstractType.Hydrate(IDataReader rs, String[] names, ISessionImplementor session, Object owner)
   at NHibernate.Persister.Entity.AbstractEntityPersister.Hydrate(IDataReader rs, Object id, Object obj, ILoadable rootLoadable, String[][] suffixedPropertyColumns, Boolean allProperties, ISessionImplementor session)
   at NHibernate.Loader.Loader.LoadFromResultSet(IDataReader rs, Int32 i, Object obj, String instanceClass, EntityKey key, String rowIdAlias, LockMode lockMode, ILoadable rootPersister, ISessionImplementor session)
   at NHibernate.Loader.Loader.InstanceNotYetLoaded(IDataReader dr, Int32 i, ILoadable persister, EntityKey key, LockMode lockMode, String rowIdAlias, EntityKey optionalObjectKey, Object optionalObject, IList hydratedObjects, ISessionImplementor session)
   at NHibernate.Loader.Loader.GetRow(IDataReader rs, ILoadable[] persisters, EntityKey[] keys, Object optionalObject, EntityKey optionalObjectKey, LockMode[] lockModes, IList hydratedObjects, ISessionImplementor session)
   at NHibernate.Loader.Loader.GetRowFromResultSet(IDataReader resultSet, ISessionImplementor session, QueryParameters queryParameters, LockMode[] lockModeArray, EntityKey optionalObjectKey, IList hydratedObjects, EntityKey[] keys, Boolean returnProxies)
   at NHibernate.Loader.Loader.DoQuery(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies)
   at NHibernate.Loader.Loader.DoQueryAndInitializeNonLazyCollections(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies)
   at NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters)
   --- End of inner exception stack trace ---
   at NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters)
   at NHibernate.Loader.Loader.ListIgnoreQueryCache(ISessionImplementor session, QueryParameters queryParameters)
   at NHibernate.Loader.Loader.List(ISessionImplementor session, QueryParameters queryParameters, ISet`1 querySpaces, IType[] resultTypes)
   at NHibernate.Loader.Custom.CustomLoader.List(ISessionImplementor session, QueryParameters queryParameters)
   at NHibernate.Impl.SessionImpl.ListCustomQuery(ICustomQuery customQuery, QueryParameters queryParameters, IList results)
   at NHibernate.Impl.SessionImpl.List(NativeSQLQuerySpecification spec, QueryParameters queryParameters, IList results)
   at NHibernate.Impl.SessionImpl.List(NativeSQLQuerySpecification spec, QueryParameters queryParameters)
   at NHibernate.Impl.SqlQueryImpl.List()
   at Laughlin.PublishFlow.Drivers.PublishFlowPartDriver.LoadModel(PublishFlowPart part) in c:\Projects\git\skin_cancer_org\src\Orchard.Web\Modules\Laughlin.PublishFlow\Drivers\PublishFlowPartDriver.cs:line 137
   at Laughlin.PublishFlow.Drivers.PublishFlowPartDriver.Editor(PublishFlowPart part, Object shapeHelper) in c:\Projects\git\skin_cancer_org\src\Orchard.Web\Modules\Laughlin.PublishFlow\Drivers\PublishFlowPartDriver.cs:line 73
   at System.Dynamic.UpdateDelegates.UpdateAndExecute3[T0,T1,T2,TRet](CallSite site, T0 arg0, T1 arg1, T2 arg2)
   at Orchard.ContentManagement.Drivers.ContentPartDriver`1.Orchard.ContentManagement.Drivers.IContentPartDriver.BuildEditor(BuildEditorContext context) in C:\Projects\git\skin_cancer_org\src\Orchard\ContentManagement\Drivers\ContentPartDriver.cs:line 25
   at Orchard.ContentManagement.Drivers.Coordinators.ContentPartDriverCoordinator.<>c__DisplayClassd.<BuildEditor>b__c(IContentPartDriver driver) in C:\Projects\git\skin_cancer_org\src\Orchard\ContentManagement\Drivers\Coordinators\ContentPartDriverCoordinator.cs:line 53
   at Orchard.InvokeExtensions.Invoke[TEvents](IEnumerable`1 events, Action`1 dispatch, ILogger logger) in C:\Projects\git\skin_cancer_org\src\Orchard\InvokeExtensions.cs:line 19

Aug 12, 2011 at 4:32 PM

Well, it looks like it needs all the columns to fill the object, so as soon as I specified the * it worked. Here's the final code:


var users = _sessionLocator.For(typeof (UserPartRecord))
                .CreateSQLQuery("SELECT " +
	                                "u.* " +
                                "FROM " + 
	                                "Orchard_Users_UserPartRecord u " +
	                                "INNER JOIN Orchard_Roles_UserRolesPartRecord ur on u.Id = ur.UserId " +
	                                "INNER JOIN Orchard_Roles_RoleRecord r on ur.Role_id = r.Id " +
                                "WHERE " +
	                                "r.Name <> 'ExcludeRole'")
                .AddEntity(typeof (UserPartRecord))

I only feel a *little* dirty about doing it this way.  Hope this helps someone else...

P.S. I used this page for reference and it had some good information:


Aug 12, 2011 at 6:32 PM

I would rather have used a hql query here. More mapping agnostic.

Also I think you had to do this because the UserRolesPartRecord has a UserID property, instead of a UserPartRecord property. It would have been easier with the later, and I can't explain this choice (which doesn't mean there is actually no choice). Might be worth investigating for a future release.

Aug 12, 2011 at 6:43 PM


I think you're exactly correct about the UserId and not the UserPartRecord property...I didn't understand why there was no explicit relationship between them.  As for using HQL, I couldn't figure out how to do HQL.  If you have an example on how to do that, I'd rather do it that way, but I just went with what worked after searching through the source.

Aug 12, 2011 at 6:57 PM

session.CreateQuery("select ... from ....")

Aug 12, 2011 at 6:58 PM

Sorry, spelled your name incorrectly in my last post, my apologies.  I tried doign something like this at one point:

inject user, user roles, and role repository into my constructor

from _userRepository.Table as u
join _userRoleRepository.Table ur ...

But it yelled at me, saying "join" was not implemented, and that's all the further I got.

Aug 12, 2011 at 6:59 PM

Perhaps I should look into CreateQuery before I ask this, but aren't both CreateQuery and CreateSQLQuery  just running SQL statements? 

Aug 12, 2011 at 7:26 PM

Yep, I should have searched first :)

CreateSqlQuery vs. CreateQuery


Aug 12, 2011 at 8:00 PM

I'm a little confused at the "For" syntax...if I'm doign an addentity, why do I need to specify the type?  What if I want values from two entities? For instance, I have a log record that gets created every time you perform a certain action on a page.  That record has for columns:

ContentItemRecord_Id <int>
Notes <string>

On another page, I query these records and display them back in a list.  I want to show the title of the content item on that list...so what does my query look like there?

var items = _sessionLocator.For(typeof (HistoryRecord)) or

var items = _sessionLocator.For(typeof (ContentItemRecord))




Aug 12, 2011 at 8:45 PM

You might want to get only the content item ids from this request, and do another one using the ContentManager.Get() function

Aug 12, 2011 at 8:53 PM

Do you mean get the content item Ids and then  iterate over that result set and call get on each one individually? I didn't see a Get method that took in more than one ContentItem Id, and it seems that calling .Get on every content item is wasteful or expensive...just want to clarify what you're suggesting.  

Aug 12, 2011 at 9:38 PM

There is no such method, you'jj have to wait for next release.

Oct 26, 2011 at 5:58 PM

I just tried using a LINQ Join on two repositories for the first time, and got the same "NotImplementedException" as @tjans. It's unfortunate - that would be a nice option to have. I presume this is due to the older version of NHib that Orchard is using. If I understand correctly, NHib 3 has a fully functional LINQ provider, which would allow these types of queries to work?

Just adding my voice to those who have suggested upgrading to Nhib 3. :-)  I know it's no small task.

Nov 12, 2011 at 8:25 PM

This is a surprising limitation (not being able to do LINQ joins). Thanks for the details on using _sessionLocator.

I still see one problem. If I have to hardcode the DataTablePrefix (the one in settings.txt), this solution will not work with multiple tenants. Is there any way in the workcontext I can access what the DataTablePrefix is? I have spent about 6 hours or so trying to find a way to access the ShellSettings for the current context at runtime. I believe this is not possible by design for security reasons and that makes sense. However the property DataTablePrefix is not sensitive data really. So would it be feasible to somehow expose this in ISite and thus make getting the DataTablePrefix easy via WorkContext.CurrentSite?

This is needed to properly overcome the lack of support for joins in NHibernate.LINQ .  A hack such as looping through one collection and then building another (as mentionned above) is very inefficient if you have a large amount of data in play.

I'm going to create an issue for this (accessing the DataTablePrefix).



Nov 15, 2011 at 2:25 AM

Linq is not the only way to do joins. ContentManager does joins.

Nov 19, 2011 at 10:51 PM

Did you try to inject IRepository<UserRolesPartRecord, getting all UserIds from a query on that repo, and then use content manager to get the set of users from their ids? That's only two requests.

Nov 20, 2011 at 9:25 PM

If you are using native SQL. To get the DataTablePrefix all you need is to have a ShellSettings object as a parameter and it will be injected. A specific interface is not needed for this. That caught me out.