LINQ-to-SQL - Join, Count

Question

I have following query:

var result = (
             from role in db.Roles
             join user in db.Users on role.RoleID equals user.RoleID                         
             where
                 user.CreatedByUserID == userID                             
             orderby user.FirstName ascending
             select new UserViewModel
             {
                 UserID = user.UserID,
                 PhotoID = user.PhotoID.ToString(),
                 FirstName = user.FirstName,
                 LastName = user.LastName,
                 FullName = user.FirstName + " " + user.LastName,
                 Email = user.Email,
                 PhoneNumber = user.Phone,
                 AccessLevel = role.Name
             });

Now, I need to modify this query... Other table I have is table Deals. I would like to count how many deals user created last month and last year. I tried something like this:

var result = (
             from role in db.Roles
             join user in db.Users on role.RoleID equals user.RoleID
             //join dealsYear in db.Deals on date.Year equals dealsYear.DateCreated.Year
             join dealsYear in
                 (
                         from deal in db.Deals
                         group deal by deal.DateCreated into d
                         select new { DateCreated = d.Key, dealsCount = d.Count() }
                 ) on date.Year equals dealsYear.DateCreated.Year into dYear
             join dealsMonth in
                 (
                         from deal in db.Deals
                         group deal by deal.DateCreated into d
                         select new { DateCreated = d.Key, dealsCount = d.Count() }
                 ) on date.Month equals dealsMonth.DateCreated.Month into dMonth
             where
                 user.CreatedByUserID == userID                 
             orderby user.FirstName ascending
             select new UserViewModel
             {
                 UserID = user.UserID,
                 PhotoID = user.PhotoID.ToString(),
                 FirstName = user.FirstName,
                 LastName = user.LastName,
                 FullName = user.FirstName + " " + user.LastName,
                 Email = user.Email,
                 PhoneNumber = user.Phone,
                 AccessLevel = role.Name,
                 DealsThisYear = dYear,
                 DealsThisMonth = dMonth
             });

but here is even syntax not correct. Any idea?

Btw, is there any good book of LINQ to SQL with examples?

Answer 1

I think found solution here:

var result = (
             from role in db.Roles
             join user in db.Users on role.RoleID equals user.RoleID
             join deal in db.Deals on user.UserID equals deal.SalesAgentID into deals                 
             where
                 user.CreatedByUserID == userID
             orderby user.FirstName ascending
             select new UserViewModel
             {
                 UserID = user.UserID,
                 PhotoID = user.PhotoID.ToString(),
                 FirstName = user.FirstName,
                 LastName = user.LastName,
                 FullName = user.FirstName + " " + user.LastName,
                 Email = user.Email,
                 PhoneNumber = user.Phone,
                 AccessLevel = role.Name,
                 DealsThisYear = deals.Where(deal => deal.DateCreated.Year == date.Year).Count()
             });

Answer 2

If you would create an association, you could write the query as

IQueryable<UserViewModel> result =
  from user in db.Users
  where user.CreatedByUserID == userID 
  orderby user.FirstName ascending 
  select new UserViewModel
  { 
    UserID = user.UserID, 
    PhotoID = user.PhotoID.ToString(), 
    FirstName = user.FirstName, 
    LastName = user.LastName, 
    FullName = user.FirstName + " " + user.LastName, 
    Email = user.Email, 
    PhoneNumber = user.Phone, 
    AccessLevel = user.Role.Name, 
    DealsThisYear = user.Deals
      .Where(deal => deal.DateCreated.Year == date.Year).Count()
  });