Execute multiple queries then union them with LINQ?

Question

I'm trying to build a drop down list that holds available options (i.e. a page category that a user can create a page for) for the logged in user, however, these are based on user roles due to the nature of how our business works - of which a user can belong to multiple roles and each role can have different options to select from in the drop down list.

What I'd like to do for users that are in multiple roles is combine the options of each individual role and use it as the values for the drop down list.

What I have assumed is that I would be able to run multiple LINQ queries by say running them through a for each (For Each r In userRoles) and then unioning them?

Though I don't know if this is possible in the way I'm trying to do it.

Is it possible or am I barking up the wrong tree?

Can someone please please please point me in the right direction?

Answer 1

It sounds like you could join user on roles and options and then group on options.

Something like this

Dim userId = GetCurrentUserId
Dim userOptions = From userRole In UserRoles
                Join roleOption In RoleOptions
                On userRole.roleId Equals roleOption.roleId And 
                     userRole.userId Equals userId
                Group By OptionId = roleOption.optioId, OptionName = roleOption.name
                Into UserOptions = Group
                Order By OptionName

I'm not a VB.NET coder and that is totally untested but hopefully you get the idea.