Github Link : Trace-Dapper.NET-Source-Code
How to use Query Multiple Mapping
Then explain the Dapper Multi Mapping
(multi-mapping) implementation and the underlying logic. After all, there can not always one-to-one relation in work.
How to use:
- You need to write your own Mapping logic and use it:
Query<Func>(SQL,Parameter,Mapping Func)
- Need to specify the generic parameter type, the rule is
Query<Func first type,Func second type,..,Func last type>
(supports up to six sets of generic parameters) - Specify the name of the cutting field
ID
, it is used by default , if it is different, it needs to be specified . - The order is
from left to right
For example: There is an order (Order) and a member (User) form, the relationship is a one-to-many relationship, a member can have multiple orders, the following is the C# Demo code:
void Main()
{
using (var ts = new TransactionScope())
using (var cn = new SqlConnection(@"Data Source=(localdb)\MSSQLLocalDB;Integrated Security=SSPI;Initial Catalog=master;"))
{
cn.Execute(@"
CREATE TABLE [User]([ID] int, [Name] nvarchar(10));
INSERT INTO [User]([ID], [Name])VALUES(1, N'Jack'),(2, N'Lee');
CREATE TABLE [Order]([ID] int, [OrderNo] varchar(13), [UserID] int);
INSERT INTO [Order]([ID], [OrderNo], [UserID])VALUES(1, 'SO20190900001', 1),(2, 'SO20190900002', 1),(3, 'SO20190900003', 2),(4, 'SO20190900004', 2);
");
var result = cn.Query<Order, User, Order>(@"
select * from [order] T1
left join [User] T2 on T1.UserId = T2.ID
", (order, user) =>
{
order.User = user;
return order;
}
);
ts.Dispose();
}
}
public class Order
{
public int ID { get; set; }
public string OrderNo { get; set; }
public User User { get; set; }
}
public class User
{
public int ID { get; set; }
public string Name { get; set; }
}
14. Support dynamic Multi Mapping
In the initial stage, the table structure is often changed or the one-time function and does not want to declare the class. Dapper Multi Mapping also supports the dynamic method.
void Main ()
{
using (var ts = new TransactionScope())
using (var connection = Connection)
{
const string createSql = @"
create table Users (Id int, Name nvarchar(20))
create table Posts (Id int, OwnerId int, Content nvarchar(20))
insert Users values(1, N'Jack')
insert Users values(2, N'Lee')
insert Posts values(101, 1, N'Jack's first day diary')
insert Posts values(102, 1, N'Jack's second day diary')
insert Posts values(103, 2, N'Lee's first day diary ')
" ;
connection . Execute ( createSql );
const string sql =
@"select * from Posts p
left join Users u on u.Id = p.OwnerId
Order by p.Id
";
var data = connection.Query<dynamic, dynamic, dynamic>(sql, (post, user) => { post.Owner = user; return post; }).ToList();
}
}
15. SplitOn distinguish type Mapping group
Split Default is used to cut the primary key, so default cut string is ID
, if the table structure PK name is ID can omit parameters, for example
var result = cn.Query<Order,User,Order>(@"
select * from [order] T1
left join [User] T2 on T1.UserId = T2.ID
", (order, user) => {
order.User = user;
return order;
}
);
If the primary key name is another name, specify the splitOn string name
and it corresponds to multiple names, it can be used ,
as a segmentation. For example, add a product table as Join:
var result = cn.Query<Order,User,Item,Order>(@"
select * from [order] T1
left join [User] T2 on T1.UserId = T2.ID
left join [Item] T3 on T1.ItemId = T3.ID
"
,map : (order, user,item) => {
order.User = user;
order.Item = item;
return order;
}
,splitOn : "Id,Id"
);
16. Query Multi Mapping underlying principle
First, a simple Demo.
- Create a Mapping FUNC collection corresponding to the number of generic class parameters
- The Mapping FUNC setup logic is the same as Query Emit IL
- Call the user's Custom Mapping Func, where the parameters are derived from the previously dynamically generated Mapping Func
public static class MutipleMappingDemo
{
public static IEnumerable<TReturn> Query<T1, T2, TReturn>(this IDbConnection connection, string sql, Func<T1, T2, TReturn> map)
where T1 : Order, new()
where T2 : User, new()
{
// 1. Create a Mapping FUNC collection corresponding to the number of generic class parameters
var deserializers = new List<Func<IDataReader, object>>();
{
// 2. The Mapping FUNC setup logic is the same as Query Emit IL
deserializers.Add((reader) =>
{
var newObj = new T1();
var value = default(object);
value = reader[0];
newObj.ID = value is DBNull ? 0 : (int)value;
value = reader[1];
newObj.OrderNo = value is DBNull ? null : (string)value;
return newObj;
});
deserializers.Add((reader) =>
{
var newObj = new T2();
var value = default(object);
value = reader[2];
newObj.ID = value is DBNull ? 0 : (int)value;
value = reader[4];
newObj.Name = value is DBNull ? null : (string)value;
return newObj;
});
}
using (var command = connection.CreateCommand())
{
command.CommandText = sql;
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
// 3. Call the user's Custom Mapping Func, where the parameters are derived from the previously dynamically generated Mapping Func
yield return map(deserializers[0](reader) as T1, deserializers[1](reader) as T2);
}
}
}
}
}
Support multiple groups of type + strongly typed return values
Dapper using multiple generic parameter methods for strongly typed multi-class Mapping
has disadvantage that it can not be dynamically adjusted and needs to be fixed.
For example, you can see that the image GenerateMapper method fix the strong transition logic in terms of the number of generic arguments, which is why Multiple Query has a maximum number of groups and can only support up to six.
Multi-Class generic caching algorithm
- Dapper use
Generic Class
to save multiple types of data by strong-type - And cooperate with inheritance to share most of the identity verification logic
- Provide available
override
GetType method to customize generic comparison logic to avoid non-multiple queryCache conflict
.
Select order of Dapper Query Multi Mapping is important
Because of SplitOn group logic depend on Select Order
, it is possible that attribute value wrong
when sequence is wrong .
Example: If the SQL in the above example is changed to the following, the ID of User will become the ID of Order; the ID of Order will become the ID of User.
select T2.[ID],T1.[OrderNo],T1.[UserID],T1.[ID],T2.[Name] from [order] T1
left join [User] T2 on T1.UserId = T2.ID
The reason can be traced to Dapper's cutting algorithm
First, the field group by
reverse order
, the GetNextSplit method can be seenDataReader Index
fromlarge to small
.
Then process the Mapping Emit IL Func of the type in
reverse order
Finally, it is reversed to
positive order
, which is convenient for the use of Call Func corresponding to generics later.
17. QueryMultiple underlying logic
Example:
using (var cn = Connection)
{
using (var gridReader = cn.QueryMultiple("select 1; select 2;"))
{
Console.WriteLine(gridReader.Read<int>()); //result : 1
Console.WriteLine(gridReader.Read<int>()); //result : 2
}
}
Advantages of using QueryMultiple:
- Mainly reduce the number of Reqeust
- Multiple queries can
share the same set of parameter
The underlying implementation logic of QueryMultiple:
- The underlying technology is ADO.NET-DataReader-MultipleResult
- QueryMultiple gets DataReader and encapsulates it into GridReader
- The Mapping dynamic method is only created when the Read method is called, and the Emit IL action is the same as the Query method.
- Then call ADO.NET
DataReader NextResult
to get the next set of query result -
DataReader will be released
if there is no next set of query results
Cache algorithm
The caching algorithm adds more gridIndex judgments, mainly for each result mapping action as a cache.
No delayed query feature
Note that the Read method uses buffer = true
the returned result is directly stored in the ToList memory
, so there is no delayed query feature.
Remember to manage the release of DataReader
When Dapper calls the QueryMultiple method, the DataReader is encapsulated in the GridReader object, and the DataReader will be recycled only after the last Read action
.
Therefore, if you open a GridReader > Read before finishing reading, an error will show: a DataReader related to this Command has been opened, and it must be closed first.
To avoid the above situation, you can change to the using
block, and the DataReader will be automatically released after running the block code.
Top comments (0)