A cumbersome linq query

Question

I have the following setup: Tasks, Accounts and Groups tables. Tasks can be assigned to both individual accounts and groups. I've made two supporting tables: TasksAndAccounts and AccountsInGroups. TasksAndAccounts table has the following fields: TaskId, AccountId, GroupId and AccountsInGroups has AccountId and GroupId fields. I'm trying to write a query that will return all tasks assigned to a given account id. The culprit here is the query should look first in TasksAndAccounts table to see if there are tasks related directly to the given account and then also look for any groups that the given account is associated with.

The basic algorithm is simple:

1. Get all tasks from TasksAndAccounts where TasksAndAccounts.AccountId == accountId
2. Get all groups from AccountsInGroups where AccountsInGroups.AccountId == accountId
3. Get all tasks from TasksAndAccounts where TasksAndAccounts.GroupId is in the result set from step 2.
4. Merge steps 1 and 3.

I've tried to tackle the issue in a few different ways but wasn't able to get any satisfactory result.

Any ideas on writing an elegant single query solution?

Answer 1

Should be something like:

var tasks = from taa in TasksAndAccounts.Where(t => t.AccountId == accountId)
            join aig in AccountsInGroups.Where(a => a.AccountId == accountId) on taa.GroupId equals aig.GroupId
            select taa;

Answer 2

Ugh. Looks like you'll need a sub-select.

var ids = from task in context.TaskAndAccounts
          where task.AccountId == accountId ||
            (from grp in context.AccountsInGroups
             where grp.AccountId == accountId
             select grp.GroupId).Contains(task.GroupId)
          select task;

Answer 3

This should translate into an EXISTS subquery:

var tasks = from task in dc.TasksAndAccounts
            where task.AccountId == accountId || (
                from g in dc.AccountsInGroups
                where g.AccountId == accountId && g.GroupId == task.GroupId
                select g.GroupId
                ).Any()
            select task;

Answer 4

Personally, I'd do it something like this:

var tasks = db.TasksAndAccounts.Where(x => x.AccountId == accountId);  
var groups = db.AccountsInGroups.Where(x => x.AccountId == accountId);  
var groupIDs = groups.Select(x => x.GroupId);  
var groupTasks = db.TasksAndAccounts.Where(x => groupIDs.Contains(x.GroupId));
var allTasks = tasks.Union(groupTasks);

It's more than one line, but it's a lot clearer than trying to cram the whole thing into one line, in my opinion. Since LINQ uses deferred execution, you still won't be executing anything until you're actually using the allTasks result set.