Communication with objects

Question

It seems that Linq to Entities does not support the Aggregate method. I need to rewrite this part of .Select(a => a.Permissions).Aggregate((a, b) => a | b) larger expression to what Linq to Entities understands. Is it possible?

Perhaps I should give you a better explanation of what I am doing. I need to get a collection of Organizers from a database based on user rights for these Organizers. Permissions are saved as bit flags and are a combination of the following:

• DefaultOrganizerPermissions - each organizer has specific default permissions
• OwnerPermissions - If the User is the owner of the Organizer, he has additional permission.
• UserPermisions. Users can be manually assigned permissions to the organizer.
• RolePermissions - Users can be members of roles, and additional permissions for organizers can be assigned to these roles.

So I have a method with the following signature

public IQueryable<Organizer> GetOrganizers(Person person, OrganizerPermissions requiredPermissions)

and inside it there is such a code

 organizers = organizers.Where(o => (( // Default permissions DefaultOrganizerPermissions | // Owner permissions (o.OwnerId == person.Id ? OwnerOrganizerPermissions : 0) | // Personal permissions (o.AccessIdentifier.Accesses.FirstOrDefault(a => a.Accessor is PersonalAccessor && (a.Accessor as PersonalAccessor).PersonId == person.Id) != null ? (OrganizerPermissions)o.AccessIdentifier.Accesses.FirstOrDefault(a => a.Accessor is PersonalAccessor && (a.Accessor as PersonalAccessor).PersonId == person.Id).Permissions : 0) | // Role permissions (o.AccessIdentifier.Accesses.Any(a => a.Accessor is RoleAccessor && (a.Accessor as RoleAccessor).Role.RoleMembers.Any(rm=>rm.PersonId == person.Id)) ? (OrganizerPermissions)o.AccessIdentifier.Accesses.Where(a=> a.Accessor is RoleAccessor && (a.Accessor as RoleAccessor).Role.RoleMembers.Any(rm => rm.PersonId == person.Id)).Select(a=>a.Permissions).Aggregate((a, b) => a | b) : 0) ) & requiredPermissions) == requiredPermissions); 

It is simplified something like this:

 organizers = organizers.Where(o => (( DefaultOrganizerPermissions | OwnerOrganizerPermissions | UserPermisions | RolePermissions ) & requiredPermissions) == requiredPermissions); 

The problem is that the user can be a member of several roles, so RolePermissions are actually multiple permissions, and I need to smooth it out with bitwise OR. But what if Aggregate is not supported?

Answer 1

If the amount of data is not excessively large, you might consider calling .ToList() before the Select(...) operator to read the data into memory, and then execute the aggregate as Linq to Objects

Answer 2

If permissions are NULL, you probably need to insert a.Permissions.GetValueOrDefault ()
so the request should be. Select (a => a.Permissions.GetValueOrDefault ()). Unit ((a, b) => a | b)

In addition, if the EF provider does not support the conversion of Aggregate to SQL (this part is specific to the provider, so it can work with one DBMS, but not with another DBMS), you need to materialize the request using ToList () (for example, suggested by @sjager )

EDIT
The problem is that the EF provider does not support converting Aggregate to SQL.

I am not a SQL Server expert, as you should find an aggregate function (e.g. SUM or AVG) that does what you need (bitwise OR). There is BIT_OR () in MySQL.

If it does not exist, then GAME OVER

If it exists, you can view the source code for the EF provider for SQL Server and see how the LINQ function translates into this or asks another question -

Answer 3

Surprisingly, yesterday I faced the same situation. Thus, as @bubi stated, without the corresponding aggregate function, SQL Server is not possible .

The way I worked was to create a group of OR expression groups for each role, each of which has a comparison with a user role. After that, join all the "OR expressions" with the AND predicate.

eg. if the user has a value of 0011 enum, then you will need to create OR predicates for flags 0001 and 0010. 0001 the "OR group" flag will look like

 x=> x.Equals(0001) || x.Equals(0011) || x.Equals(0101)| || ..) 

But this is definitely not recommended.