Dapper.Net One-To-Many mapping


While using Dapper.Net, most common and fundamental scenario is to load the result that form one-to-many relationship. After doing some searching and refactoring the idea multiple times, I get a refactored solution.

Scenario

Consider a simple customer-order scenario. A customer could have multiple orders, while a order will be associated with only one customer. The models are:

public class Customer
    {
        public String CustomerId { get; set; }
        public String CustomerName { get; set; }

        public IEnumerable<Order> Orders { get; set; }
    }

public class Order
    {
        public string OrderId { get; set; }
        public string CustomerId { get; set; }
    }

The SQL query to fetch both Customer and Order details is,

SELECT *
FROM Customers c JOIN Orders o
ON c.CustomerId = o.CustomerId
ORDER BY c.CustomerId

Solution

Now, the only issue is how to associate each order with its customer. For this we will need a mapper, to do the mapping of order to its customer. Consider a EntityOneToManyMapper as below:

public class EnittyOneToManyMapper<TP,TC,TPk>
    {
        private readonly IDictionary<TPk, TP> _lookup = new Dictionary<TPk, TP>();

        public Action<TP, TC> AddChildAction { get; set; }

        public Func<TP, TPk> ParentKey { get; set; }


        public virtual TP Map(TP parent, TC child)
        {
            TP entity;
            var found = true;
            var primaryKey = ParentKey(parent);

            if (!_lookup.TryGetValue(primaryKey, out entity))
            {
                _lookup.Add(primaryKey, parent);
                entity = parent;
                found = false;
            }

            AddChildAction(entity, child);

            return !found ? entity : default(TP);

        }
    }


where,
TP, parent class
TC, child class
TPk, type of the Id field of the parent class.

Map function consist of the actual logic of mapping parent and child entities.
This EntityOneTOManyMap class can be used as,
using (var conn = new SqlConnection(connectionString))
{

    var mapper = new EnittyOneToManyMapper<Customer, Order, string>()
    {
        AddChildAction = (c, o) =>
        {
            if (c.Orders == null)
                c.Orders = new List<Order>();

            c.Orders = c.Orders.Concat(new[] { o });
        },
        ParentKey = (c) => c.CustomerId
    };

                
    var customers = conn.Query<Customer, Order, Customer>(joinQuery,mapper.Map, splitOn: "OrderId")
            .Where(y => y != null);

    foreach (var c in customers)
    {
        PrintCustomer(c);
        Console.WriteLine("");
    }
}

Comments

  1. This comment has been removed by the author.

    ReplyDelete
  2. Can Titanium Rings Be Resized? | TITIAN ART
    In an attempt to titanium bikes provide the most sensitive materials in a titanium gr 2 natural ceramics glass surface, titanium rings can be titanium exhaust wrap resized babyliss titanium flat iron by using the following titanium fat bike

    ReplyDelete

Post a Comment

Popular posts from this blog

Using Entity Framework 6 Code First with Oracle 11g

Apache Spark UDF: Over Optimization Issue