CODEWITHSUNDEEP
c#linq-to-sql
Liaoo
Liaoo

Reputation: 425

Problem with linq2sql Group-By then Where

I'm trying to get the user records that do NOT have the current role. A user can have multiple roles.

I'm working with 2 tables - Users and UserRoleMap

var superAdminRoleId = *GUID*

var query = from user in _userRepository.Table
            join userRoleMap in _userRoleMapRepository.Table
            on user.Id equals userRoleMap.UserId
            // Now, group the roles by the user
            group userRoleMap by user into userRoleMaps
            // get the records that are NOT super admin
            where !userRoleMaps.Any(map=>map.UserId == superAdminRoleId)
            select userRoleMaps.Key;

I'm getting the error

LinqToDB.LinqToDBException: ''map.UserId' cannot be converted to SQL.'

So I revised it to

var query = from user in _userRepository.Table
            join userRoleMap in _userRoleMapRepository.Table
            on user.Id equals userRoleMap.UserId
            // Now, group the roles by the user
            group userRoleMap.UserId by user into userRoleMaps   // changed userRoleMap to userRoleMap.UserId 
            // get the records that are NOT super admin
            where !userRoleMaps.Any(map=>map == superAdminRoleId) // update
            select userRoleMaps.Key;

Now, I'm getting

System.ArgumentException: 'Property 'System.Guid Id' is not defined for type 'System.Guid' (Parameter 'property')'

Upvotes: 1

Views: 31

Answers (1)

StriplingWarrior
StriplingWarrior

Reputation: 156624

There's probably a way to fix your group by, but looking at what it seems you're trying to accomplish I think you'll get a better-performing and simpler query like this:

var userRoleMaps = _userRoleMapRepository.Table;
var nonAdminUsers = _userRepository.Table
    .Where(user => !userRoleMaps
        .Any(map => map.UserId == user.Id && map.RoleId == superAdminRoleId));

Upvotes: 1

Related Questions