Dapper: How to read into list of Dictionary from query?

user_6089708 0
Viewed 8749

Dapper provides lots of ways mapping data into list of dynamic objects. However in some case I'd like to read data to list of Dictionary.

The SQL may looks like:

"SELECT * FROM tb_User"

As tb_User may change outside, I don't know what columns will return in result. So I can write some code like this:

var listOfDict = conn.QueryAsDictionary(sql);
foreach (var dict in listOfDict) {
    if (dict.Contains("anyColumn")) {
        // do right thing...
    }
}

Is there any built-in methods for Dapper to do this conversion?

dapper dictionary sql c#
3 Answers

You could use the Cast extension method from System.Linq

IEnumerable<IDictionary<string, object>> rows;
rows = connection.Query(sqlRequest).Cast<IDictionary<string, object>>();

foreach (var row in rows)
{
    var columnValue = row['columnName']; // returns the value of the column name
}
user_25145260

You can just assign aliases to your query so that it matches the Key and Value properties of a KeyValuePair and then use the .ToDictionary method like this:

var dict = db.Query<KeyValuePair<string, int>>(@"
    SELECT COMMUNITY_TYPE As Key, COUNT(*) AS Value
    FROM SNCOMM.COMMUNITY
    GROUP BY COMMUNITY_TYPE")
    .ToDictionary(x => x.Key, x => x.Value);

Now you have a Dictionary<string, int> without any manual converting.

user_32766340
Related