LINQ join and group

Question

How to expand this query:

public Dictionary<int, List<TasksInDeal>> FindAllCreatedTasks()
{
    return (from taskInDeal in db.TasksInDeals
            where taskInDeal.Date > DateTime.Now && taskInDeal.Date < DateTime.Now.AddDays(7)
            group taskInDeal by taskInDeal.CreatedByUserID
                into groupedDemoClasses
                select groupedDemoClasses).ToDictionary(gdc => gdc.Key, gdc => gdc.ToList());
}

into something like this:

public Dictionary<int, List<TaskForNotification>> FindAllCreatedTasks()
{
    return (from taskInDeal in db.TasksInDeals
            join user in db.Users on taskInDeal.CreatedByUserID equals user.UserID
            where taskInDeal.Date > DateTime.Now && taskInDeal.Date < DateTime.Now.AddDays(7)
            group taskInDeal by taskInDeal.CreatedByUserID
                into groupedDemoClasses
                select new TaskForNotification 
                { 
                    Email = user.Email,
                    TaskInDealField1 = taskInDeal.TaskInDealField1,
                    TaskInDealField2 = taskInDeal.TaskInDealField2,
                    TaskInDealField3 = taskInDeal.TaskInDealField3,
                    ...
                }
                ).ToDictionary(gdc => gdc.Key, gdc => gdc.ToList());
}

So, to first query I need to join email from other table.

Answer 1

  // do the date logic up front, not in the database.
DateTime now = DateTime.Now
DateTime weekFromNow = now.AddDays(7);

  // pull the joined rows out of the database.
var rows =
(
  from taskInDeal in db.TasksInDeals
  where taskInDeal.Date > now && taskInDeal.Date < weekFromNow
  join user in db.Users
    on taskInDeal.CreatedByUserID equals user.UserID
  select new {TaskInDeal = taskInDeal, UserEmail = user.Email}
).ToList();

  // shape the rows in memory
Dictionary<int, List<TaskForNotification>> result =
(
  from row in rows
  let taskForNotification = new TaskForNotification  
  {  
    Email = row.UserEmail, 
    TaskInDealField1 = row.TaskInDeal.TaskInDealField1, 
    TaskInDealField2 = row.TaskInDeal.TaskInDealField2, 
    TaskInDealField3 = row.TaskInDeal.TaskInDealField3, 
    ... 
  }
  group taskForNotification by row.TaskInDeal.CreatedByUserID
  // without an "into", group by ends the query.
).ToDictionary(g => g.Key, g => g.ToList()); 

When you group, bear this in mind. Groups in SQL have only keys and aggregates. Groups in LINQ have keys, aggregates and elements! If you ask the database for groups, and then ask for the elements - SQL couldn't provide you with those elements in a single query. You'll wind up automatically repeatedly re-querying using the group's key as a filter.