Trace Dapper.NET Source Code - Query Multiple Mapping

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.

1. Create a Mapping FUNC collection corresponding to the number of generic class parameters
2. The Mapping FUNC setup logic is the same as Query Emit IL
3. 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 Classto 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 query Cache 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

1. First, the field group by reverse order, the GetNextSplit method can be seen DataReader Index from large to small.
   
   

2. Then process the Mapping Emit IL Func of the type in reverse order

3. 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.