Clever Geek Handbook

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?

+2


source to share


4 answers


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;
+1


source


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;
+1


source


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.

+1


source


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;
0


source






More articles:


All Articles