Clever Geek Handbook

Getting multiple counts in EF Model LINQ in one DB call

I am using LINQ EF Model to get value from database. I need to count the number of lines and I am using the following code for it.

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
}

This will call the database i times. is it possible to get the total in one database call? will it affect the performance of the code if the value i is large?

+3


source to share


2 answers


You can use GroupBy

as below:

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

Or in a smaller line of codes:

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

Update



One thing that should change in the above snippets is the use of the method Convert.ToString

inside the linq query. This can be done as follows:

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

Then, in your linq query:

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());
+2


source


You can use Contains

.

 var answers = optionsList.Select((v, i) => Convert.ToString(i)).ToArray();
 int totalCount = _db.UserSurveyResultToBeRevieweds1
                .Where(x=>x.refKey == singleQuestionsLists.referenceKey && 
                          x.type == questionTypeSAQ && 
                          x.questionId == Convert.ToString(singleQuestionsLists.Id) &&  
                          answers.Contains(x.answer)).Count();

But I'm afraid there is a related issue in your code Convert.ToString(singleQuestionsLists.Id)

that cannot be converted to linq expression.

It should be.

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


And use it like.

x.questionId == questionListId

Complete code

 var answers = optionsList.Select((v, i) => Convert.ToString(i)).ToArray();
 var questionListId = Convert.ToString(singleQuestionsLists.Id);
 int totalCount = _db.UserSurveyResultToBeRevieweds1
                .Where(x=>x.refKey == singleQuestionsLists.referenceKey && 
                          x.type == questionTypeSAQ && 
                          x.questionId == questionListId &&  
                          answers.Contains(x.answer)).Count();
+1


source






More articles:


All Articles