Cumbersome linq query
I have the following setup: Tasks, Accounts and Groups tables. Tasks can be assigned to both individual accounts and groups. I created two supporting tables: TasksAndAccounts and AccountsInGroups. The TasksAndAccounts table has the following fields: TaskId, AccountId, GroupId, and AccountsInGroups have the AccountId and GroupId fields. I am trying to write a query that will return all tasks assigned to a given account id. The culprit here is a query that should first look in the TasksAndAccounts table to see if there are tasks associated directly with a given account, and then also look for the groups this account is associated with.
The basic algorithm is simple:
- Get all tasks from TasksAndAccounts, where TasksAndAccounts.AccountId == accountId
- Get all groups from AccountsInGroups where AccountsInGroups.AccountId == accountId
- Get all tasks from TasksAndAccounts where TasksAndAccounts.GroupId is in the result set from step 2.
- Combine steps 1 and 3.
I tried to solve the problem in several ways, but could not get a satisfactory result.
Any ideas for writing an elegant solution for a single query?
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;
Ugh. It looks like you need a sub-selection.
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;
Personally, I would 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 much clearer than trying to cram it all into one line, in my opinion. Since LINQ uses deferred execution, you still won't execute anything until you use the allTasks result set.
There 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;