Mapping to collection in PetaPoco?

Question

Is there a way to map the following to a Dictionary<int,int>? It seem it produces rows for as many returned results there are, but they have no values...

Sql sql = new Sql()
    .Append("SELECT Count(*) as 'Answer Count', QuestionId")
    .Append("FROM CF.Answers")
    .Append("WHERE SurveyId = @0", surveyId)
    .Append("GROUP BY QuestionId");

var result = database.Fetch<Dictionary<int,int>>(sql);

Answer 1

Fetch always returns a List<T>

The fact that Fetch<T>() method returns a List<T> would mean that in your code example it returns

List<Dictionary<int, int>> result = ...

which is likely not what you want and each dictionary would be holding one item only which beats the whole reason why you want to have a dictionary in the first place. As I understand your question you actually want to get:

Dictionary<int, int> result = ...

There are of course extension methods on List<T> that let you convert to other types as one. One such method is .ToDictionary() that can convert your result to a dictionary that you want to get.

First ideas

Now the problem that we have at hand here is what type can we use with Fetch method? Initially two things came to my mind:

KeyValuePair<int, int>
Tuple<int, int>

Even though nice ideas, none of them would work, because Key property in KeyValuePair doesn't have a public setter and the second one doesn't have a parameterless constructor that PetaPoco could use.

Solution

What we're left off here is creating a custom type similar to Tuple but with functionality we can actually use with PetaPoco. Let's make this type generic, so we can easily reuse it with different types:

public class Pair<T1, T2>
{
    public T1 Item1 { get; set; }
    public T2 Item2 { get; set; }
}

Using this custom class we can now easily get a dictionary:

Sql sql = new Sql()
    .Append("SELECT QuestionId as Item1, COUNT(*) as Item2")
    .Append("FROM Answers")
    .Append("WHERE SurveyId = @0", surveyId)
    .Append("GROUP BY QuestionId");

var result = database
    .Fetch<Pair<int,int>>(sql)
    .ToDictionary(i => i.Item1, i => i.Item2);

Mind the fact that I've reversed the order of select fields (and set them different alias names), because you don't want counts to be dictionary keys (as they may repeat) but rather Question IDs. So it's either you reverse the order of select fields as I did, or provide correct selectors for .ToDictionary() extension method.

Answer 2

I'd opt for using a dynamic:

var result = database.Query<dynamic>(sql)
    .ToDictionary(d => d.QuestionId, d => d.AnswerCount);

A couple notes:

• You'll need to drop the space from your 'Answer Count' column and go with 'AnswerCount' in order for this to work.
• I used Query instead of Fetch for performance reasons; in theory, it should skip materializing to a List first and instead stream the results directly into the Dictionary.