无法获得多映射小巧玲珑工作 [英] Can't get multi-mapping to work in Dapper
问题描述
玩弄短小精悍,我很高兴的结果至今 - 耐人寻味
但是,现在,我的下一个情况是从两个表中读取数据 - 一个学生
和地址
表
学生
表中有 StudentID(INT IDENTITY)
,地址$ C $的主键C>有一个
AddressID(INT IDENTITY)
。 学生
也有称为FK AddressID
连接到地址
表。
我的想法是创建两个类,每个表,用我感兴趣的。另外,我把属性的 PrimaryAddress
属性类型地址
到在C#我的学生
类。
然后我想在一个单一的查询检索学生和地址数据 - 模拟天生我说的对的 Github的页面:
VAR数据=连接。查询<邮政,使用者>(SQL,(后,用户)=> {post.Owner =用户;});
无功后= data.First();
在这里,发表
和用户
检索,和后的所有者设置为用户 - 返回的类型是发表
- 正确? / p>
所以,在我的代码,我定义两个参数,一般查询
扩展方法 - 一个学生
作为第一个应当返还,以及地址
作为第二个,这将被存储到学生的实例:
VAR学生= _conn.Query<职业,地址>
(选S *,一。* FROM dbo.Student小号
INNER JOIN dbo.Address一个ON s.AddressID = a.AddressID
其中s.StudentenID = @Id
(STU,ADR)=> {stu.PrimaryAddress = ADR;}
新{ID = 4711});
麻烦的是 - 我得到在Visual Studio中的错误:
使用泛型方法
'Dapper.SqlMapper.Query(System.Data.IDbConnection,
字符串,
System.Func,
动感,System.Data.IDbTransaction,
布尔,字符串,整数?
System.Data.CommandType?)'需要6
类型参数
我真的不明白为什么小巧玲珑使用此重载6类型参数...
这是因为我改变了API和忘了更新的文档,我纠正了错误。
一定要看看的Tests.cs 的一个完整的先进的最新规范。
在特定的,以前需要在旧的API的动作< T,U>
来执行映射,麻烦的是,它感到既武断和缺乏灵活性。你不能完全控制的返回类型。新的API参加一个 Func键< T,U,V>
。所以,你可以控制你从后面映射器的类型,它并不需要是一个映射类型。
我只是绑住周围多映射一些额外的灵活性,这个测试应该清楚:
类Person
{
公众诠释PERSONID {搞定;组; }
公共字符串名称{;组; }
}
类地址
{
公众诠释AddressId {搞定;组; }
公共字符串名称{;组; }
公众诠释PERSONID {搞定;组; }
}
级额外
{
公众诠释标识{搞定;组; }
公共字符串名称{;组; }
}
公共无效TestFlexibleMultiMapping()
{
变种的sql =
@,选择
1为PERSONID,'鲍勃'作为名称,
2作为AddressId,'ABC街作为名称,如1 PERSONID,
3为ID,弗雷德作为名称
;
VAR personWithAddress = connection.Query<人,地址,额外的,元组LT;人,地址,额外>>
(SQL,(P,A,E)=> Tuple.Create(P,A,E),splitOn:AddressId,ID)。首先();
personWithAddress.Item1.PersonId.IsEqualTo(1);
personWithAddress.Item1.Name.IsEqualTo(鲍勃);
personWithAddress.Item2.AddressId.IsEqualTo(2);
personWithAddress.Item2.Name.IsEqualTo(ABC街);
personWithAddress.Item2.PersonId.IsEqualTo(1);
personWithAddress.Item3.Id.IsEqualTo(3);
personWithAddress.Item3.Name.IsEqualTo(弗雷德);
}
小巧玲珑的管道都通过一个单一的方法多的地图API,所以,如果事情失败,将在6参数作为结束语起来。另一块拼图是,我没有允许一些超级灵活的分裂,这是我刚才添加。
请注意,在 splitOn
参数将默认为编号
,这意味着它会采取一个名为列 ID
或标识
作为第一个对象的边界。然而,如果你需要上有说3路多映射不同的名称,多个主键的界限,现在你可以传递一个逗号分隔的列表。
所以,如果我们要解决上面,大概可以如下:
VAR学生= _conn.Query<职业,地址,学生>
(选S *,一。* FROM dbo.Student小号
INNER JOIN dbo.Address一个ON s.AddressID = a.AddressID
其中s.StudentenID = @Id
(STU,ADR)=> {stu.PrimaryAddress = ADR;返回STU;}
新{ID = 4711},splitOn:AddressID)FirstOrDefault();
Playing around with Dapper, I'm quite pleased with the results so far - intriguing!
But now, my next scenario would be to read data from two tables - a Student
and an Address
table.
Student
table has a primary key of StudentID (INT IDENTITY)
, Address
has an AddressID (INT IDENTITY)
. Student
also has an FK called AddressID
linking into the Address
table.
My idea was to create two classes, one for each table, with the properties I'm interested in. Additionally, I put an PrimaryAddress
property of type Address
onto my Student
class in C#.
I then tried to retrieve both student and address data in a single query - I mimick the sample that's given on the Github page:
var data = connection.Query<Post, User>(sql, (post, user) => { post.Owner = user; });
var post = data.First();
Here, a Post
and a User
are retrieved, and the owner of the post is set to the user - the type returned is a Post
- correct?
So in my code, I define two parameters to the generic Query
extension method - a Student
as the first which should be returned, and an Address
as the second, which will be stored onto the student instance:
var student = _conn.Query<Student, Address>
("SELECT s.*, a.* FROM dbo.Student s
INNER JOIN dbo.Address a ON s.AddressID = a.AddressID
WHERE s.StudentenID = @Id",
(stu, adr) => { stu.PrimaryAddress = adr; },
new { Id = 4711 });
Trouble is - I get an error in Visual Studio:
Using the generic method 'Dapper.SqlMapper.Query(System.Data.IDbConnection, string, System.Func, dynamic, System.Data.IDbTransaction, bool, string, int?, System.Data.CommandType?)' requires 6 type arguments
I don't really understand why Dapper insists on using this overload with 6 type arguments...
That would be cause I changed APIs and forgot to update the documentation, I corrected the error.
Be sure to have a look at Tests.cs for a full up-to-date spec.
In particular, the old API used to take in an Action<T,U>
to perform the mapping, the trouble was that it felt both arbitrary and inflexible. You could not fully control the return type. The new APIs take in a Func<T,U,V>
. So you can control the type you get back from the mapper and it does not need to be a mapped type.
I just tied up some additional flexibility around multi mapping, this test should make it clear:
class Person
{
public int PersonId { get; set; }
public string Name { get; set; }
}
class Address
{
public int AddressId { get; set; }
public string Name { get; set; }
public int PersonId { get; set; }
}
class Extra
{
public int Id { get; set; }
public string Name { get; set; }
}
public void TestFlexibleMultiMapping()
{
var sql =
@"select
1 as PersonId, 'bob' as Name,
2 as AddressId, 'abc street' as Name, 1 as PersonId,
3 as Id, 'fred' as Name
";
var personWithAddress = connection.Query<Person, Address, Extra, Tuple<Person, Address,Extra>>
(sql, (p,a,e) => Tuple.Create(p, a, e), splitOn: "AddressId,Id").First();
personWithAddress.Item1.PersonId.IsEqualTo(1);
personWithAddress.Item1.Name.IsEqualTo("bob");
personWithAddress.Item2.AddressId.IsEqualTo(2);
personWithAddress.Item2.Name.IsEqualTo("abc street");
personWithAddress.Item2.PersonId.IsEqualTo(1);
personWithAddress.Item3.Id.IsEqualTo(3);
personWithAddress.Item3.Name.IsEqualTo("fred");
}
Dapper pipes all the multi mapping APIs through a single method, so if something fails it will end up in the 6 param one. The other piece of the puzzle was that I did not allow for some super flexible splits, which I just added.
Note, the splitOn
param will default to Id
, meaning it will take a column called id
or Id
as the first object boundary. However if you need boundaries on multiple primary keys that have different names for say a "3 way" multi mapping, you can now pass in a comma separated list.
So if we were to fix the above, probably the following would work:
var student = _conn.Query<Student,Address,Student>
("SELECT s.*, a.* FROM dbo.Student s
INNER JOIN dbo.Address a ON s.AddressID = a.AddressID
WHERE s.StudentenID = @Id",
(stu, adr) => { stu.PrimaryAddress = adr; return stu;},
new { Id = 4711 }, splitOn: "AddressID").FirstOrDefault();
这篇关于无法获得多映射小巧玲珑工作的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!