Transforming one-to-many Sql into Nested Class
Back in the days of Classic ASP, when you had a one too many relationship displaying the data was pretty painful. You would do things like
var reference_number_old = "$##%@"; while (recordsExist) { reference_number = String(rs(0)); if (reference_number_old != reference_number) { Response.Write(reference_number); reference_number_old = reference_number; } //Do more stuff } |
Sooo very very painful, and that’s only grouping by one column (reference number).
Now however with OO programming and the advance of Linq there is a much nicer and easier way to deal with this Master-Detail type one-to-many relationship.
Lets see how we do it now, still using our own custom sql.
Firstly here is our class to represent our flat data straight out of the database.
public class FlatCustomerAndOrders { public int ReferenceNumber { get; set; } public string Name { get; set; } public string Address { get; set; } public int OrderId { get; set; } public string ProductName { get; set; } public string Description { get; set; } public decimal Amount { get; set; } } |
Even filling this class used to be a bit painful before Linq. You would have to create a connection, create a reader, then instantiate the FlatCustomerAndOrders class. Set the properties then add the object to a list while records could still be read. Here’s how I do it now.
DataContext dc = new DataContext(new SqlConnection(connString)); IEnumerable<FlatCustomerAndOrders> flat = dc.ExecuteQuery<FlatCustomerAndOrders>("select c.referencenumber, c.name," + "c.address, o.orderid, o.productname, o.description, o.amount from customers " + "c inner join orders o on c.referencenumber = o.referencenumber"); |
Thats its. We have our List of FlatCustomerAndOrders.
The next step is to organise the data so that the data is nested so its easy to display with just two foreach loops. The two classes:
public class Customer { public int ReferenceNumber { get; set; } public string Name { get; set; } public string Address { get; set; } public List<Order> Orders { get; set; } } public class Order { public int OrderId { get; set; } public string ProductName { get; set; } public string Description { get; set; } public decimal Amount { get; set; } } |
To fill these two class we do some clever Linq using the GroupBy method. Here’s the code.
IList<Customer> Customers = flat.GroupBy(cust => new { cust.ReferenceNumber, cust.Name, cust.Address }) .Select(c => new Customer() { ReferenceNumber = c.Key.ReferenceNumber, Name = c.Key.Name, Address = c.Key.Address, Orders = c.Select(o => new Order() { OrderId = o.OrderId, ProductName = o.ProductName, Description = o.Description, Amount = o.Amount }).ToList() }).ToList(); |
And that’s it. It may look like a bit of a mouthful but its quite simple really.
It just takes the flat list and applies the groupby method to it grouping by the three customer columns. It then builds a new list to fill the Orders property with.
Its then super easy to display the data like so.
foreach (Customer c in Customers) { //Display the customer details foreach (Order o in c.Orders) { //Display the orders for each customer } } |
Well I hope this helps you as much as it has helped me.
Cheers
Schotime
[...] Click Here to Keep Reading… [...]