代码之家 › 专栏 › 技术社区 › ilija veselica

LINQ到SQL-连接,计数

count join linq-to-sql

ilija veselica · 技术社区 · 14 年前

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
             });

现在,我需要修改这个查询。。。我的另一张桌子是桌上交易。我想计算一下用户上个月和去年创建了多少交易。我试过这样的方法:

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
             });

但这里的语法甚至不正确。你知道吗?

2 回复 | 直到 14 年前

Community Mr_and_Mrs_D 7 年前

我想我找到了解决办法 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()
             });

Amy B 14 年前

如果你愿意的话 create an association

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()
  });