Get users with a specific role

ServiceStack.OrmLite
1

I want to retrieve a user with a specific role. The table has a List of roles serialized to the Roles field (ie: not using the separate roles table).

What is the best way to query the table using ORMLite to retrieve users with a specific role. I have the following code, but it retrieves the entire users table. Looking at the SQL trace, the query doesn’t contain any filters.

var users = db.Select<UserAuth>().Where(u => u.Roles.Contains(RoleType.AccountManager.ToString()));
2

By default Roles and Permissions are blobbed with the UserAuth and the only way to inspect a blobbed field is to deserialize it on the client, i.e. you can’t search a blob field with a server-side SQL query.

But the OrmLiteAuthRepository supports storing Users Roles and Permissions in a separate table by specifying UseDistinctRoleTables = true, e.g:

container.Register<IAuthRepository>(c =>
    new OrmLiteAuthRepository(c.Resolve<IDbConnectionFactory>()) {
        UseDistinctRoleTables = true,
    });

Which then persists Roles and Permissions in the UserAuthRole which you can query.

3

Thanks, that makes sense.

Is there a recommended way of switching to using the distinct roles table when there’s existing data?

4

Go through each user in the UserAuth table and re-assign each of their roles with the OrmLiteAuthRepository’s IManageRoles.AssignRoles() API.

5

Super! I started doing exactly that. Thanks for your help!