Accepted answer

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
            select task;


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;


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;


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.

Related Articles