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

Advertisements
This entry was posted in .NET and tagged , , . Bookmark the permalink.

One Response to Transforming one-to-many Sql into Nested Class

  1. Pingback: Transforming one-to-many Sql into Nested Class - Adam Schroder

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s