LINQ-to-SQL - Join, Count
- by ile
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?
