如何使用Dapper';s在将一种类型映射到另一种类型时的多重映射
本文关键字:映射 类型 一种 另一种 Dapper 何使用 | 更新日期: 2023-09-27 18:25:13
我的场景:我有一个名为Person的类,我使用Dapper将其保存到DB中。在Person上,我有一个Dictionary of Values,我将其序列化为字符串,并将其存储为varchar(MAX)。
public class Person
{
public string Name {get; set;}
public int Id {get; set;} //PK in DB
public int Age {get; set;}
public IDictionary<string, string> Values {get; set;}
}
这就是我保存到数据库的方式:
DynamicProperties dp = new DynamicProperties();
dp.Add("Name", p.Name);
dp.Add("Age", p.Age);
dp.Add("Values", Jil.JSON.Serialize<IDictionary<string, string>>(p.Values));
conneciton.Execute(insertSql, dp, commandType: CommandType.StoredProcedure);
这是我试图读出的地方
private Func<Person, object, Person> GetPerson = new Func<Person, object, Person>
((person, values) => {
person.Values = Jil.JSON.Deserialize<Dictionary<string, string>>((string)values);
return person;
});
string sql = "SELECT text
FROM otherTable
SELECT Name, Id, Age, Values
FROM People
WHERE Id = @Id"
SqlMapper.GridReader gridReader = connToDeviceConfig.QueryMultiple(sql, new {Id = 5}, commandType: CommandType.StoredProcedure);
List<string> listOfOtherStuff = gridReader.Read<string>().ToList();
List<Person> people = gridReader.Read<Person, object, Person>(GetPerson, splitOn: "Age").ToList();
//其他人员列表是独立的
第二个gridReader失败,返回使用多映射API时,如果您有Id以外的键,请确保设置splitOn参数''r''n参数名称:splitOn
我觉得我可能有点弯曲了Dapper,试图让它做一些它不应该做的事情,即从DB,并将其反序列化为Dictionary并分配给Person.Values。
是这样做的吗(我只是在某个地方有个bug)?还是我应该采取另一种方法?
我用这个作为参考:(链接到档案馆的文件中的大致位置)
public void TestProcSupport()
{
var p = new DynamicParameters();
p.Add("a", 11);
p.Add("b", dbType: DbType.Int32, direction: ParameterDirection.Output);
p.Add("c", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue);
connection.Execute(@"create proc #TestProc
@a int,
@b int output
as
begin
set @b = 999
select 1111
return @a
end");
connection.Query<int>("#TestProc", p, commandType: CommandType.StoredProcedure).First().IsEqualTo(1111);
p.Get<int>("c").IsEqualTo(11);
p.Get<int>("b").IsEqualTo(999);
}
StackTrace:
at Dapper.SqlMapper.GetNextSplit(Int32 startIdx, String splitOn, IDataReader reader) in D:'Dev'dapper-dot-net'Dapper NET40'SqlMapper.cs:line 2111
at Dapper.SqlMapper.GenerateDeserializers(Type[] types, String splitOn, IDataReader reader) in D:'Dev'dapper-dot-net'Dapper NET40'SqlMapper.cs:line 2057
at Dapper.SqlMapper.<MultiMapImpl>d__71`8.MoveNext() in D:'Dev'dapper-dot-net'Dapper NET40'SqlMapper.cs:line 1857
at Dapper.SqlMapper.GridReader.<MultiReadInternal>d__9`8.MoveNext() in D:'Dev'dapper-dot-net'Dapper NET40'SqlMapper.cs:line 4300
at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
at Dapper.SqlMapper.GridReader.Read[TFirst,TSecond,TReturn](Func`3 func, String splitOn, Boolean buffered) in D:'Dev'dapper-dot-net'Dapper NET40'SqlMapper.cs:line 4330
是否尝试过其他splitOn
?
我手头没有DB测试,但我认为splitOn应该是下一个映射对象中想要的第一列,而不是上一个对象中的最后一列。此外,列成为对象的成员,而不是整个值(尽管单列情况可能会有例外,但我手头没有测试数据库)。
TL;DR-试试这个
class ValuesObj{
public string Values {get;set;}
}
// whatever code here
private Func<Position, ValuesObj, Position> GetPerson = new Func<Person, ValuesObj, Person>
((person, valuesObj) => {
string values = valuesObj.Values;
person.Values = Jil.JSON.Deserialize<Dictionary<string, string>>((string)values);
return position;
});
string sql = "SELECT text
FROM otherTable
SELECT Name, Id, Age, Values
FROM People
WHERE Id = @Id"
SqlMapper.GridReader gridReader = connToDeviceConfig.QueryMultiple(sql, new {Id = 5}, commandType: CommandType.StoredProcedure);
List<string> listOfOtherStuff = gridReader.Read<string>().ToList();
List<Person> people = gridReader.Read<Person, ValuesObj, Person>(GetPerson, splitOn: "Values").ToList();
否则,我建议只使用dynamic
返回Dapper方法。