count on LINQ union
- by brechtvhb
I'm having this link statement:
List<UserGroup> domains = UserRepository.Instance.UserIsAdminOf(currentUser.User_ID);
query = (from doc in _db.Repository<Document>()
join uug in _db.Repository<User_UserGroup>() on doc.DocumentFrom equals uug.User_ID
where domains.Contains(uug.UserGroup)
select doc)
.Union(from doc in _db.Repository<Document>()
join uug in _db.Repository<User_UserGroup>() on doc.DocumentTo equals uug.User_ID
where domains.Contains(uug.UserGroup)
select doc);
Running this statement doesn't cause any problems. But when I want to count the resultset the query suddenly runs quite slow.
totalRecords = query.Count();
The result of this query is :
SELECT COUNT([t5].[DocumentID])
FROM (
SELECT [t4].[DocumentID], [t4].[DocumentFrom], [t4].[DocumentTo]
FROM (
SELECT [t0].[DocumentID], [t0].[DocumentFrom], [t0].[DocumentTo
FROM [dbo].[Document] AS [t0]
INNER JOIN [dbo].[User_UserGroup] AS [t1] ON [t0].[DocumentFrom] = [t1].[User_ID]
WHERE ([t1].[UserGroupID] = 2) OR ([t1].[UserGroupID] = 3) OR ([t1].[UserGroupID] = 6)
UNION
SELECT [t2].[DocumentID], [t2].[DocumentFrom], [t2].[DocumentTo]
FROM [dbo].[Document] AS [t2]
INNER JOIN [dbo].[User_UserGroup] AS [t3] ON [t2].[DocumentTo] = [t3].[User_ID]
WHERE ([t3].[UserGroupID] = 2) OR ([t3].[UserGroupID] = 3) OR ([t3].[UserGroupID] = 6)
) AS [t4]
) AS [t5]
Can anyone help me to improve the speed of the count query?
Thanks in advance!
