c# - 在单个数据库调用中获取 EF 模型 LINQ 中的多个计数

Question

我正在使用 LINQ EF 模型从数据库中获取值。我必须获取某些行的计数，并且为此使用了以下代码。

for (int i = 0; i < optionsList.Length; i++)
{
     var map = new Dictionary<string, double>();
     int count = _db.UserSurveyResultToBeRevieweds1
                    .Where(x=>x.refKey == singleQuestionsLists.referenceKey && 
                              x.type == questionTypeSAQ && 
                              x.questionId == Convert.ToString(singleQuestionsLists.Id) &&  
                              x.answer == Convert.ToString(i)).Count();
     // need to use count in map
}

这将调用数据库 i 次。是否有可能在单个数据库调用中获得总数？如果 i 的值很大，这会影响代码的性能吗？

Answer 1

您可以使用 GroupBy，如下所示:

var map = new var map = new Dictionary<string, double>();
var groups = _db.UserSurveyResultToBeRevieweds1
                .Where(x=>x.refKey == singleQuestionsLists.referenceKey && 
                          x.type == questionTypeSAQ && 
                          x.questionId == Convert.ToString(singleQuestionsLists.Id)
                .GroupBy(x=>x.answer);

foreach(var g in groups)
    map.Add(g.Key, g.Count()); 

或者用更少的代码行:

var map = _db.UserSurveyResultToBeRevieweds1
             .Where(x=>x.refKey == singleQuestionsLists.referenceKey && 
                       x.type == questionTypeSAQ && 
                       x.questionId == Convert.ToString(singleQuestionsLists.Id)
             .GroupBy(x=>x.answer)
             .ToDictionary(x=>x.Key, x=>x.Count());

更新

上述代码段中应该更改的一件事是在 linq 查询中使用 Convert.ToString 方法。这可以按如下方式完成:

string questionListId = Convert.ToString(singleQuestionsLists.Id);

然后在你的 linq 查询中:

var map = _db.UserSurveyResultToBeRevieweds1
             .Where(x=>x.refKey == singleQuestionsLists.referenceKey && 
                       x.type == questionTypeSAQ && 
                       x.questionId == questionListId 
             .GroupBy(x=>x.answer)
             .ToDictionary(x=>x.Key, x=>x.Count());