Linq to sql，与其他计数进行多对多查询 [英] Linq to sql, query many-to-many with count of other

问题描述

我有一个多对多关系，我正在尝试创建一个查询，该查询将获取我的左侧信息，并提供一个属性，该属性计算它所引用的记录数.

I have a many-to-many relation and I'm trying to create the query which will fetch me the left side and a property which counts the number of records which are refferecend by it.

以下是我的查询

    var dbSet = await (from user in _dbContext.Users
                       where (from courseUsers in _dbContext.CourseUsers select courseUsers.UserId).Contains(user.Id)
                        select new
                        {
                           Name = user.Name,
                           Id = user.Id,
                           CourseUsersCount = _dbContext.CourseUsers.Where(item => item.UserId == user.Id).Count()
                        })
                        .ToListAsync();

我不喜欢如何计算CourseUsersCount.我还想包括total count属性，我要做的是在 select 上添加另一个属性，该属性只会对 _dbContext.CourseUsers 进行计数然后进行另一次内存中转换.

What I don't like is how CourseUsersCount is computed. I would also like to include the total count property and the way I would do it is to add another property on the select which would just do a count over the _dbContext.CourseUsers and after that do another in-memory transformation.

最后，我希望创建具有这种结构的结果

I the end I would like a result with this structure to be created

{
   count: 1000,
   data: [{
      Id: 1,
      Name: "c",
      CourseUsersCount: 2
   }]
}

我想知道如何使用linq-to-sql直接做到这一点.

and I want to know how can I do this directly using linq-to-sql.

推荐答案

如注释中所述，您必须使用GroupBy进行此类计算:

As mentioned in comments you have to use GroupBy for such calculation:

var query
    from user in _dbContext.Users
    from courseUsers in user.Courses
    group user by new { user.Id, user.Name } into g
    select new 
    {
        g.Key.Id,
        g.Key.Name,
        CourseUsersCount = g.Count()
    };

这篇关于Linq to sql，与其他计数进行多对多查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！