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("");
}
}
great
ReplyDeleteThis comment has been removed by the author.
ReplyDeleteCan Titanium Rings Be Resized? | TITIAN ART
ReplyDeleteIn 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