count on LINQ union

Question

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!

Answer 1

Get rid of your unions and try something like this.

query = (from doc in _db.Repository<Document>().Where(x=> _db.Repository<User_UserGroup>().Any(y=>y.User_ID==x.DocumentFrom ||y.User_ID==x.DocumentTo ))

Sorry i can't guarantee it compiles... but basically use Where any any to do your job.

Answer 2

Be sure to have indexes on [Document].[DocumentFrom], [Document].[DocumentTo] and [User_UserGroup].[UserGroupID].

By reading your query I can see you join the same table on different conditions.
If you don't need duplicate results you might look at this alternative:

var query = from doc in _db.Repository<Document>()
            from uug in _db.Repository<User_UserGroup>() 
            //join wether DocumentFrom or DocumentTo equals User_ID
            where (
                     (doc.DocumentFrom == uug.User_ID) ||
                     (doc.DocumentTo == uug.User_ID)
                  ) &&
                  //same check on this                      
                  domains.Contains(uug.UserGroup)
            select doc;

//execute it
var list = query.ToList();

EDIT: If I am not mistaken .Union picks distinct elements between the two queries, so my alternative IS a valid one.
If you truly want to merge the two result sets you might want to look at .Concat.