How to map to a Dictionary from database using Dapper?

Question

I have a class :

public class TestClass
{
    public string value1 {get; set;}
    public string value2 {get; set;}
    public string value3 {get; set;}
    public string value4 {get; set;}
}

and the database :


Database - Test

id    code    value1    value2    value3     value4
--    ----    ------    ------    ------     ------

1     1000    xxxxx     xxxxx     xxxxx      xxxxx
2     1000    yyyyy     .....     .....      .....
3     1000    yyyy3     .....     .....      .....
4     1000    yyyy4
5     2000    yyyy5
6     2000    yyyy6
7     3000    yyyy7
8     3000    yyyy8
9     4000    yyyy9
10    4000    y9999

This is gonna be 4 key and 4 list of TestClass. string is a code and rest is testClass list.

I wanna map this to this dictionary: If Code is a same add it into list of TestClass.

Dictionary<string, List<TestClass>> testDic = new Dictionary<string, List<TestClass>>();

testDic = conn.Query("SELECT * FROM Test").ToDictionary(x=> x.????) ;

How to do it? I assume it is something like think but its not working?

I've not used Dapper so I don't know what `conn.Query("....")` returns, but I'd expect you need something like `.GroupBy(r => r.Code).ToDictionary(g => g.Key, g => g.Select(r => ConvertToTestClass(r)).ToList());` — ProgrammingLlama, Jan 25 '21 at 08:09
Does this answer your question? [How to map to a Dictionary object from database results using Dapper Dot Net?](https://stackoverflow.com/questions/14780767/how-to-map-to-a-dictionary-object-from-database-results-using-dapper-dot-net) — Silvermind, Jan 25 '21 at 08:14
@Silvermind, yes but i am trying to take into list when is code is a same. I dont think that is right example of my situation. — 闇Yami, Jan 25 '21 at 08:17

score 4 · Accepted Answer · answered Jan 25 '21 at 08:27

It looks like you're trying to group by the code. To do that, I would do something like:

public class TestClass
{
    public int Id {get;set;}
    public string Code {get;set;}
    public string Value1 {get; set;}
    public string Value2 {get; set;}
    public string Value3 {get; set;}
    public string Value4 {get; set;}
}
/// ...
testDic = conn.Query<TestClass>("SELECT * FROM Test").ToLookup(x=> x.Code);

The ToLookup here is standard LINQ which works a lot like a Dictionary<TKey, List<TValue>>, but inbuilt (it is essentially a lot like GroupBy, but intended to be accessed multiple times).

If you actually need a dictionary, it is more complex; ToDictionary won't really help you, so the most practical approach is probably:

var data = new Dictionary<string, List<TestClass>>();
foreach (var row in conn.Query<TestClass>("SELECT * FROM Test"))
{
    if (!data.TryGetValue(row.Code, out var list))
    {
        data.Add(row.Code, list = new List<TestClass>());
    }
    list.Add(row);
}

Whats different between ILookup and Dictionary? I've changed your first code into this ``` testDic = conn.Query("SELECT * FROM Test").ToLookup(x=> x.Code).ToDictionary(x=> x.Key, y=> y.ToList()) ``` and it returns what i need; — 闇Yami, Jan 25 '21 at 08:34
@闇Yami `Lookup` and `Dictionary>` are broadly similar, but not interchangeable - but if you have what you need: great! — Marc Gravell, Jan 25 '21 at 08:50

How to map to a Dictionary from database using Dapper?

1 Answers1