Friday, October 10, 2008

How to do the laziest loading in C# and SQL Server (Part 1)

Mapping POCO (Plain Old CLR Object) to relational database could be a bit messy and a degradation of our application performance in terms of relational loading. There are several types of relationships in the database which we try to simulate with the object oriented language, these relationships are as follows :

  1. One - To - One (one row to one row)
  2. One - To Many (one row to many rows)
  3. Many - To -One (many rows to one row. Inverse of the One - To Many)
  4. Many - To - Many (Many rows to Many rows).
When we use the above in OOP, we come up with Aggregation, inheritance, composition, containment etc. But the way the relational data is structured and stored is different from the way data is stored in the OOP world. Data are stored on the disk in the relational world while data are stored on the stack/and heap in the CLR (Common Runtime Language).

So, to maximize the usage of data when we are mapping relational to object oriented, we need to use a pattern called "Lazy Loading".

What is Lazy Loading.
Simply, lazy loading can be described as an approach for loading objects or data when we need them. This helps to save CPU cycles because we do not intend to load what we do not need, thereby we free our heap from unessary usage, and the almighty deallocator, the Gargbage collector can go to sleep. Simple lazy loading example can be described as follows :


public IList Customers
{
get{
if(null == customer)
customers = LoadCustomers();

return customers;
}
}


From the above code snippet, we are checking for null values and if lists of customers is not null, we load the already loaded lists of customers; else we load new ones. This is a classic lazy loading feature. This kind of lazy loading will still load all customers, except a stub that refers to the list that contains the data is returned. But in the case of the classic lazy-loading, we are loading everything and returning everything. (Thats Bad, isn't it).

The Concept of Laziest Loading

Let us assume the following relationships between two objects, Customer has many Orders . The relationships that we described above is a One - To - Many scenairo, One customer to many orders, the following class diagram and code depicts the relationship :

The relationships in the diagram says one instance on a customer class will have multiple instances of an order in its relationship bag. And one instance of an order, will have one instance of a customer class. Thats it.

The Perfect Solution

In the classic lazy loading scenario, we are loading everything once it is required, even if we do not intend to use everything in the list, we end up fetching objects into the list which they will all be allocated memory space on the heap. unwanted objects made its way to the CLR heap, thats not what we bargained for.

What if we create our own special list and a class that implements IEnumerator for a special case of doing the laziest loading. The following diagram depicts our new concepts of laziest loading of data structure using our custom built list (implements from IList) and custom iterator, inplements IEnumerator : The diagram below represents the concept of the laziest loading and relational to object transformation, using datareader and our custom enumerator class.
Looking at the diagram, we will realize that there are three sections, the Custom enumeration section, the Data Reader, and the the relationship sections. This sections mapped to themselves properly to achieve the laziest loading i have been talking about.

Datareader mapping with Custom Enumerator
When you create a custom enumerator, you will implement the IEnumerator interface, which will give you various method and properties to override, but in this article, i will be talking about three methods that are very useful in our lazy loading scenario. The idea is to lazily load objects from the database when they are iterated instead of when the whole list is called. For that to happen, we need an open data reader that we need to read when the GetEnumerator method of the list that use's this Enumerator is called.

Enumerator MoveNext Mapped to DataReader Read

When there is a call to our List (Probably you need to create a custom list that implements IList, and returns our custom Enumerator). When in a froeach loop, an iterator can know if it has data to iterate with the call to movenext method. And since we are fetching from the database, we need to call the Read method of our DataReader here, this will ensure that our dataReader moves it cursor to the next row in the list. This can also return false to indicate that there is no more row to read.


public bool MoveNext()
{
return dataReader.Read();
}


Current and DataReader Get[Type]
Enumerator Current property used to translate DataReader Get[DataType] to our domain or entity object. For example, let us look at the code snippet below :


public object Current
{
get
{
ProductOrder order = new ProductOrder();
order.OrderDate = Convert.ToDateTime(dataReader[1]);
order.OrderName = Convert.ToString(dataReader[2]);

return order;
}
}


Enumerator Dispose Method (If you implement IDisposable)

The disposed method is fired when you exit the foreach loop, or you read the list to the end, or you break from the list. It is a wise idea to close the datareader in the dispose section, and before you close the datareader, it is wise to keep moving the datareader till it gets to its last (In case the loop was exited with break statement). The connection shouldnt be close here, because you may be using it for other database operations (Especially if you are using MARS (Multiple Active Result Sets) new feature in "SQL 2005 YUKON". The following code snippet, depicts the kind of stuffs that you can do with the dispose method :


public void Dispose()
{
while(dataReader.Read())
{
//Do not do anything again here. This is called when you exit the loop
//By end of loop or break statement.
}

dataReader.Close();
}


This is just the beginning, so watch out for part two when we create lazy loading handlers delegates and when i introduce you all to Ghosting or Dynamic Proxy in C# using IL (Intermediate Language). Thanks.

You can now find the Dynamic Proxy Part 1 here

No comments: