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

Exerciser

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.

Nickey

I tried implementing your solution but the values for Item1 and Item2 are always 0, so I get a duplicate key error. I know the query is correct and the dictionary has a record count of 3 but the values are not being put in there. I'm not sure why. Thx! – Exerciser

It works if I change the names of the Item1 -> QuestionId and Item2 -> Count, but that kind of defeats the purpose of using a Dictionary<int,int>. Any ideas on how to get it to work with the Item1, and Item2 values? It just doesn't seem to want to map to them because it doesn't match a column name. – Exerciser

@chobo: The only explanation I can think of is that you've kept your original TSQL statement syntax without column alias names and without changing column order... If you set column alias names then you shouldn't be getting QuestionId and Count back from your query but rather columns Item1 and Item2. Especially not Count because my answer doesn't use this alias, nor does your original question. – Nickey

@chobo: and when you say "if I change the names" I suppose you're talking about property names in Pair<T1,T2> class, right? – Nickey

yes. I changed Item1 and Item2 in pair to correspond to the column names – Exerciser

oh... You are right on the alias part, completely overlooked that :) I'm an idiot. thanks so much for your help! – Exerciser

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.

Thirtytwo