Question: I'm using LINQ EF Model to get value from database. I've to fetch count of certain rows and I'm using following code for the same.

```
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 total count in single database call?
will this affect performance of the code if value of i is large?

Answer: 
You could make use of `GroupBy`, like 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 less 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 are the use of `Convert.ToString` method inside the linq query. This can be done as below:

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

```

Then in you 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());

```
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 problem in your code related to `Convert.ToString(singleQuestionsLists.Id)` that might not be able to be converted to linq expression.

It should be.

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

```

And use it like.

```
x.questionId == questionListId

```

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

```