C# 如何使用 Dapper Dot Net 从数据库结果映射到 Dictionary 对象？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14780767/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to map to a Dictionary object from database results using Dapper Dot Net?
提问by jpshook
If I have a simple query such as:
如果我有一个简单的查询，例如：
string sql = "SELECT UniqueString, ID FROM Table";
and I want to map it to a dictionary object such as:
我想将它映射到一个字典对象，例如：
Dictionary<string, int> myDictionary = new Dictionary<string, int>();
How would I do this with Dapper?
我将如何使用 Dapper 做到这一点？
I assume it is something like:
我假设它是这样的：
myDictionary = conn.Query<string, int>(sql, new { }).ToDictionary();
But can't figure out the proper syntax.
但无法弄清楚正确的语法。
采纳答案by Marc Gravell
There's various ways already shown; personally I'd just use the non-generic api:
已经展示了多种方式；我个人只是使用非通用api：
var dict = conn.Query(sql, args).ToDictionary(
row => (string)row.UniqueString,
row => (int)row.Id);
回答by w.brian
I'm not sure if what you're trying to do is possible. If you define a class to map the query to this becomes far more trivial:
我不确定你想做的事情是否可行。如果您定义一个类来将查询映射到此变得更加简单：
public class MyRow
{
public int Id { get; set; }
public string UniqueString { get; set; }
}
Then, you would just do this:
然后，您只需执行以下操作：
var sql = "SELECT UniqueString, ID FROM Table";
var myDictionary = conn.Query<MyRow>(sql).ToDictionary(row => row.UniqueString, row => row.Id);
回答by Tim Schmelter
Works also without an additional class:
无需额外的类也可以工作：
var myDictionary = conn.Query<string, int, KeyValuePair<string,int>>(sql, (s,i) => new KeyValuePair<string, int>(s,i))
.ToDictionary(kv => kv.Key, kv => kv.Value);
NOTE: When using Dapper.NET 3.5 version, the Query method that takes the first, second and return types requires you specify more parameters, as the .NET 4.0 and .NET 4.5 versions take advantage of optional arguments.
注意：使用 Dapper.NET 3.5 版本时，采用第一个、第二个和返回类型的 Query 方法需要您指定更多参数，因为 .NET 4.0 和 .NET 4.5 版本利用了可选参数。
In this case, the following code should work:
在这种情况下，以下代码应该可以工作：
string splitOn = "TheNameOfTheValueColumn";
var myDictionary = conn.Query<string, int, KeyValuePair<string,int>>(sql, (s,i) => new KeyValuePair<string, int>(s,i), null, null, false, splitOn, null, null)
.ToDictionary(kv => kv.Key, kv => kv.Value);
Most of the arguments will revert to a default, but splitOnis required, as it will otherwise default to a value of 'id'.
大多数参数将恢复为默认值，但这splitOn是必需的，否则将默认为“id”值。
For a query that returns two columns, 'ID' and 'Description', splitOnshould be set to 'Description'.
对于返回两列“ ID”和“说明”的查询，splitOn应设置为“说明”。
回答by dalenewman
Dapper also has an extension method for ExecuteReader. So, you could also do this:
Dapper 还有一个扩展方法ExecuteReader。所以，你也可以这样做：
var sql = "SELECT UniqueString, ID FROM Table";
var rows = new List<Dictionary<string, int>>();
using (var reader = cn.ExecuteReader(sql)) {
while (reader.Read()) {
var dict = new Dictionary<string, int>();
for (var i = 0; i < reader.FieldCount; i++) {
dict[reader.GetName(i)] = reader.GetInt32(i);
}
rows.Add(dict);
}
}
This approach works without knowing the column names. Moreover, if you don't know the data types, you could change Dictionary<string,int>to Dictionary<string,object>and GetInt32(i)to GetValue(i).
这种方法在不知道列名的情况下也能工作。此外，如果你不知道的数据类型，你可以改变Dictionary<string,int>到Dictionary<string,object>和GetInt32(i)到GetValue(i)。
回答by Allen.Cai
string strSql = "SELECT DISTINCT TableID AS [Key],TableName AS [Value] FROM dbo.TS_TStuctMaster";
Dictionary<string,string> dicts = sqlConnection.Query<KeyValuePair<string,string>>(strSql).ToDictionary(pair => pair.Key, pair => pair.Value);
You can use aliases and strong types.
您可以使用别名和强类型。
Aliases are the key points, which match the attributes of KeyValuePair type Key and Value.
别名是关键点，匹配 KeyValuePair 类型 Key 和 Value 的属性。
It works under strong typing and runs well.
它在强类型下工作并且运行良好。
I don't like dynamic type. It brings disaster in certain situations. Moreover, the boxing and unboxing brings performance loss.
我不喜欢动态类型。它在某些情况下会带来灾难。而且，装箱和拆箱会带来性能损失。
回答by herostwist
If you are using > .net 4.7 or netstandard2 you can use value tuples. the code is nice and terse and there is no use of dynamics.
如果您使用的是 > .net 4.7 或 netstandard2，您可以使用值元组。代码简洁明了，没有使用动态。
var sql = "SELECT UniqueString, Id FROM Table";
var dict = conn.Query<(string UniqueString, int Id)>(sql)
.ToDictionary(t => t.UniqueString,t => t.Id);
