Categories
.net c# entity-framework linq sql

How to union two complex queries querying complex objects?

I am quite new to entity framework so I am confused now. I have these two queries:

var usersQuery =
from role in this.dbContext.Set<Model.ApplicationRole>()
join ru in dbContext.Set<Model.ApplicationRoleUser>() on role.Id equals ru.ApplicationRoleId into rus
from ru in rus.DefaultIfEmpty()
join user in dbContext.Set<Model.User>() on ru.UserId equals user.Id into users
from user in users.DefaultIfEmpty()
where
(ru != null && ru.ApplicationId == application.Id)
&& (roleId == null || ru.ApplicationRoleId == roleId.Value)
group role by user into grps
select
new RoleActor
{
Actor =
new Actor
{
AccountName = grps.Key.AccountName,
DisplayName =
(grps.Key.DisplayName ?? string.Empty) != string.Empty
? grps.Key.DisplayName
: grps.Key.CommonName,
DomainName = grps.Key.DomainName,
Email = grps.Key.Email ?? string.Empty,
CompanyCode = grps.Key.CompanyCode,
AdGuid = grps.Key.AdGuid,
CommonName = grps.Key.CommonName
},
Type = Model.ActorType.User,
RoleNames = grps.Select(role => role.Name).ToList()
};
var groupsQuery =
from role in this.dbContext.Set<Model.ApplicationRole>()
join rg in dbContext.Set<Model.ApplicationRoleGroup>() on role.Id equals rg.ApplicationRoleId into rgs
from rg in rgs.DefaultIfEmpty()
join @group in dbContext.Set<Model.Group>() on rg.GroupId equals @group.Id into groups
from @group in groups.DefaultIfEmpty()
where
(rg != null && rg.ApplicationId == application.Id)
&& (roleId == null || rg.ApplicationRoleId == roleId.Value)
group role by @group into grps
select
new RoleActor
{
Actor =
new Actor
{
AccountName = grps.Key.AccountName,
DisplayName =
(grps.Key.DisplayName ?? string.Empty) != string.Empty
? grps.Key.DisplayName
: grps.Key.CommonName,
DomainName = grps.Key.DomainName,
Email = string.Empty,
CompanyCode = string.Empty,
AdGuid = grps.Key.AdGuid,
CommonName = grps.Key.CommonName
},
Type = Model.ActorType.Group,
RoleNames = grps.Select(role => role.Name).ToList()
};

I need to union these two queries. But when I try to use extension method Union, I get exception:

The ‘Distinct’ operation cannot be applied to the collection
ResultType of the specified argument. Parameter name: argument

I wanted to concat queries and then do something like:

queryConcatResult.GroupBy(x => x.Key).Select(x => x.FirstOrDefault())

However when I use method Concat, I get exception:

The nested query is not supported. Operation1=’UnionAll’
Operation2=’MultiStreamNest’

I will be grateful for any hint.