7

I want to map complex object to dapper result from query which has two inner joins. I know we've solution to map one inner join but I want to map two inner joins result.

Here is the Scenario:

My Classes are:

public class Order { public int id { get; set; } public string order_reference { get; set; } public string order_status { get; set; } public List<OrderLine> OrderLines { get; set; } } public class OrderLine { public int id { get; set; } public int order_id { get; set; } public string product_number { get; set; } public List<OrderLineSize> OrderLineSizes { get; set; } } public class OrderLineSize { public int id { get; set; } public int order_line_id { get; set; } public string size_name { get; set; } }

Order has OrderLines as List and OrderLine as OrderLineSizes as List.

Now, Here is my query base on that I want to populate List<Order> with correct data:

SELECT * FROM orders_mstr o INNER JOIN order_lines ol ON o.id = ol.order_id INNER JOIN order_line_size_relations ols ON ol.id = ols.order_line_id

Here is what I tried so far:

var lookup = new Dictionary<int, Order>(); connection.Query<Order, OrderLine, Order>(@" SELECT o.*, ol.* FROM orders_mstr o INNER JOIN order_lines ol ON o.id = ol.order_id ", (o, ol) => { Order orderDetail; if (!lookup.TryGetValue(o.id, out orderDetail)) { lookup.Add(o.id, orderDetail = o); } if (orderDetail.OrderLines == null) orderDetail.OrderLines = new List<OrderLine>(); orderDetail.OrderLines.Add(ol); return orderDetail; }).AsQueryable(); var resultList = lookup.Values;

Using this, I can successfully map order object with OrderLine but I want to populate OrderLineSizes as well with correct data.

Improve this question

3 Answers 3

Reset to default
13

I tried my best and solve it.

Here is the more easy and accurate solution as per me.:

var lookup = new Dictionary<int, OrderDetail>(); var lookup2 = new Dictionary<int, OrderLine>(); connection.Query<OrderDetail, OrderLine, OrderLineSize, OrderDetail>(@" SELECT o.*, ol.*, ols.* FROM orders_mstr o INNER JOIN order_lines ol ON o.id = ol.order_id INNER JOIN order_line_size_relations ols ON ol.id = ols.order_line_id ", (o, ol, ols) => { OrderDetail orderDetail; if (!lookup.TryGetValue(o.id, out orderDetail)) { lookup.Add(o.id, orderDetail = o); } OrderLine orderLine; if (!lookup2.TryGetValue(ol.id, out orderLine)) { lookup2.Add(ol.id, orderLine = ol); orderDetail.OrderLines.Add(orderLine); } orderLine.OrderLineSizes.Add(ols); return orderDetail; }).AsQueryable(); var resultList = lookup.Values.ToList();
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

This code will not work actually if we use the domain classes of OP. It is due to OrderLines and OrderLineSizes are null, hence, you will get NullReferenceException in the runtime on lines: orderDetail.OrderLines.Add(orderLine); and orderLine.OrderLineSizes.Add(ols);. Initialize each of them next line to lookup.Add and lookup2.Add, or simply use C#6 auto-property initializer on the class level.
5

This can also be done by QueryMultiple but because your question includes INNER JOIN I don't use it.

public Dictionary<int, Order> GetOrderLookup() { var lookup = new Dictionary<int, Order>(); const string sql = @" SELECT o.id, o.order_reference, o.order_status, ol.id, ol.order_id, ol.product_number, ols.id, ols.order_line_id, ols.size_name FROM orders_mstr o JOIN order_lines ol ON o.id = ol.order_id JOIN order_line_size_relations ols ON ol.id = ols.order_line_id"; List<Order> orders = null; using (var connection = OpenConnection(_connectionString)) { orders = connection.Query<Order, OrderLine, OrderLineSize, Order>(sql, (order, orderLine, orderLizeSize) => { orderLine.OrderLineSizes = new List<OrderLineSize> { orderLizeSize }; order.OrderLines = new List<OrderLine>() { orderLine }; return order; }, null, commandType: CommandType.Text).ToList(); } if (orders == null || orders.Count == 0) { return lookup; } foreach (var order in orders) { var contians = lookup.ContainsKey(order.id); if (contians) { var newLinesToAdd = new List<OrderLine>(); var existsLines = lookup[order.id].OrderLines; foreach (var existsLine in existsLines) { foreach (var newLine in order.OrderLines) { if (existsLine.id == newLine.id) { existsLine.OrderLineSizes.AddRange(newLine.OrderLineSizes); } else { newLinesToAdd.Add(newLine); } } } existsLines.AddRange(newLinesToAdd); } else { lookup.Add(order.id, order); } } return lookup; }
Improve this answer

2 Comments

@DanielTshuva Can you tell me why Order is in the connection.Query command twice? Thanks.
@johnny - In the select query the first statement is to get Order properties (id, order_reference, order_status) -> This for first Order, the second Order is the OUT type. Note, that we query for 3 Select statements for 3 types, but in the connection.Query we provider 4 type -> the last Type is the out Type. Hope you understand.
3

I decided to use Dapper to get a big data for calculating somethings. This is my dapper extension method to join 3 table in _RepositoryBase.cs file.

 public List<Tuple<T, T2, T3, T4>> QueryMultiple<T2, T3, T4>(string sql, object param) where T2 : class where T3 : class { using (var con = new SqlConnection(GetConnStr())) { if (con.State == ConnectionState.Closed) con.Open(); var query = con.Query<T, T2, T3, T4, Tuple<T, T2, T3, T4>>(sql, (t, t2, t3, t4) => Tuple.Create(t, t2, t3, t4), param); //if (query.Count() == 0) // return new List<T>(); var data = query.ToList(); con.Close(); con.Dispose(); return data; } }

Then, this function will help you to get sql joined data via dapper.

 public List<Table1> GetSqlJoinedDataViaDaper(int customerId) { var repo = new GenericRepository<T_LOOKUP>(); var sql = @"select table1.ID Table1ID, table1.*, table2.ID Table2ID, table2.*, table3.ID Table3ID, table3.* from dbo.Table1 table1 (nolock) left outer join dbo.Table2 table2 (nolock) on table2.ID=table1.FKTable2ID left outer join dbo.Table3 table3 (nolock) on table3.ID=table1.FKTable3ID where table1.CustomerID=@p1 "; var resultWithJoin = repo.QueryMultiple<Table1, Table2, Table3>(sql, new { p1 = 1, splitOn = "Table1ID,Table2ID,Table3ID" }).ToList(); var retval = new List<Table1>(); foreach (var item in resultWithJoin) { Table1 t1 = item.Item2; //After first split t1.Table2 = item.Item3; //After Table2ID split t1.Table3 = item.Item4; //After Table3ID split retval.Add(t1); } return retval; }

Summary: Write your select and insert split between tables. Say splits to Dapper and get your data. I used this and worked better than Entity Framework.

Improve this answer

1 Comment

I'm having issues with the QueryMultiple() function: The type or namespace name T could not be found. When I change it to List<Tuple<T, T2, T3>> QueryMultiple<T, T2, T3>(string sql, object param) it compiles, but the calling function does not execute as expected.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.