项目linq组按结果到一个单一的视图模型对象

Question

我想获得一些实体表的计数，并将它们分配给保存计数值的单个对象。

我使用联合是因为我想对数据库执行单个查询。

我已经编写了以下代码，但这将为每个组返回一个单独的计数视图模型，而不是我想将值分配给单个计数视图模型的属性。

var counts =
                _db.Departments.All()
                    .Select(c => new {key = 1, count = 0})
                    .Union(_db.Students.All().Select(c => new {key = 2, count= 0}))
                    .GroupBy(c=>c.key)                  
                    .Select(x => new CountsVm()
                    {
                        DepartmentCount = x.Count(d => d.key == 1),
                        StudentCount = x.Count(s => s.key == 2)
                    });

public class CountsVm
    {
        public int StudentCount { get; set; }
        public int DepartmentCount { get; set; }
    }

Answer 1

这是一个将产生一个查询的解决方案

var countsQuery =
    _db.Departments.All()
        .Select(p => new { key = 1, count = 0 })
        .Union(_db.Students.All().Select(p => new { key = 2, count = 0 }))
        .GroupBy(p => p.key)
        .Select(p => new { key = p.Key, count = p.Count() }).ToList();
var counts = new CountsVm()
    {
        DepartmentCount =
            countsQuery.Where(p => p.key == 1)
                       .Select(p => p.count)
                       .FirstOrDefault(),
        StudentCount =
            countsQuery.Where(p => p.key == 2)
                       .Select(p => p.count)
                       .FirstOrDefault()
    };

Answer 2

是否只需要对每个条目表分别调用count ?

var counts = new CountsVm()
    {
        DepartmentCount = _db.Departments.All().Count(),
        StudentCount = _db.Students.All().Count()
    };

Answer 3

如果我理解正确的话，你可以这样做:(我只使用linq，但是在选择中返回null不是一个好的做法)。一个foreach会给你更好的服务)

var countsVm = new CountsVm(){
     DepartmentCount = 0,
     StudentCount = 0
};
var counts =
                _db.Departments.All()
                    .Select(c => new {key = 1, count = 0})
                    .Union(_db.Students.All().Select(c => new {key = 2, count= 0}))
                    .GroupBy(c=>c.key)                  
                    .Select(x => {
                          countsVm.DepartmentCount += x.Count(d => d.key == 1);
                          countsVm.StudentCount += x.Count(s => s.key == 2);
                          return null;
                     });

public class CountsVm
    {
        public int StudentCount { get; set; }
        public int DepartmentCount { get; set; }
    }

Answer 4

尝试从查询中删除All并运行FirstOrDefault()

var counts =
                _db.Departments.
                    .Select(c => new {key = 1, count = 0})
                    .Union(_db.Students.Select(c => new {key = 2, count= 0}))
                    .GroupBy(c=>c.key)                  
                    .Select(x => new CountsVm()
                    {
                        DepartmentCount = x.Count(d => d.key == 1),
                        StudentCount = x.Count(s => s.key == 2)
                    }).FirstOrDefault();

public class CountsVm
    {
        public int StudentCount { get; set; }
        public int DepartmentCount { get; set; }
    }