Isolating the Data Store: A Brief Discussion on Microsoft Data Access and Domain Modeling

Recently, I had a geek acquaintance waxing on about the frustrations he was experiencing upgrading an application from DataSets to Entity Framework entities. He asked how I dealt with changes in data technology in my own applications. I told him it was not really a huge problem for me, as I did not consume data access “objects” outside of the data tier. This caused a bit of a “deer in the headlight” look that convinced me we needed a paradigm shift talk.

There are three basic data access technologies in .NET, if you leave out the roll your own and third party solutions. They are:

  1. DataSets – This is the oldest and is basically a nice XML container for any number of “tables". It is a very flexible method of data access and is also quite easy to serialize, especially if the consumer is also .NET.
  2. LINQ to SQL – LINQ to SQL was created to fill in the gap between DataSets and Entity Framework. I have a personal affinity against LINQ to SQL, especially in web applications, but that is a story for another day.
  3. Entity Framework – Entity Framework (EF) is the latest Microsoft technology and also the least mature. In the latest incarnation, EF has the ability to map to POCO (Plain Old CLR Objects), but I would not use the POCO bits as a Domain model.

If you get one thing from the above, it is that you cannot easily trade one of the object models for another if you have bound the “objects” to the UI layer. That is the reason for this blog entry. So let’s look at what we do and why and then examine another way.

What we do

Let’s suppose we are building an application for the shipping department. The problem is the old shipping application is not aware of the cheapest way to ship to a customer, so we need an application that will examine different methods and figure the lowest price, based on the customer’s shipping constraints. The company already has an eCommerce application, a warehouse application (the old shipping application, as well), etc. This application is strictly concerned with shipping out the merchandise.

Using the methods I normally see, if we had to create the database, it might look something like this:


And this works well. We probably also have an order, order detail, a product table. But, this is a good start. Following the top down (UI to database) or bottom up (database to UI) approaches, we would probably end up creating an object for each of these tables. We might even use DataSets, LINQ to SQL or EF to create the objects we use in our user interface and merely ignore the parts we don’t wish to use for the shipping application.

The problem with this approach is updating from a DataSet coupled to the UI to an Entity is major surgery, a real PITA.

The Domain Driven Approach

In Domain Driven Design, we might look at this problem a bit differently. Since we only need a customer name and an address. We could well set up the customer to look like the following:

public class ShippingCustomer
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string AddressLine { get; set; }
    public string City { get; set; }
    public string State { get; set; }
    public string PostalCode { get; set; }

Now, that is not a customer in the traditional sense, but to ship to this customer, this is all we need. Now, how do we best fill the object? I would probably use a SQL statement like the following:

SELECT c.FirstName
    , c.LastName
    , a.AddressLine
    , a.City
    , sp.StateProvinceName
    , a.PostalCode
FROM Customer c
JOIN Customer_Address ca
    ON c.CustomerId = ca.CustomerId
JOIN AddressType at
    ON ca.AddressTypeId = at.AddressTypeId
JOIN Address a
    ON ca.AddressId = a.AddressId
JOIN StateProvince sp
    ON a.StateProvinceId = sp.StateProvinceId
WHERE at.AddressTypeName = 'Shipping'
AND c.CustomerId = @CustomerId

And, I can easily do this with any of the three data access methods. With the DataSet, I can create a DataTable and then use a custom TableAdapter to fill it. With LINQ to SQL, I will end up either connecting to a stored procedure or a rather complex LINQ to SQL statement. With the Entity Framework, I can model it in a variety of ways.

BUT … I am not going to use these objects in my business layer or user interface. Instead, I am going to map the objects to my domain object or set up some type of translation helper bits or perhaps even a layer.

The Repository and mapping

Okay, so let’s set this up with different types of methods of pulling data. My first step is to set up an Interface for the Repository(s):

public interface IShippingCustomerRepository
    ShippingCustomer GetById(int id);

Okay, cool. Per my last blog entry on repositories, we can also genericize this. Since this is only one method, it is a bit ridiculous, but it can go like this:

public interface IRepository <T,U>
    where T : class 
    where U : struct
    T GetById(U id);

public interface IShippingCustomerRepository : IRepository<ShippingCustomer, int>

This is a useful pattern when you have some custom methods, as in the previous blog entry, even if it looks a bit nauseating right here. Let’s implement this with a DataSet:

public class DataSetShippingCustomerRepository : IShippingCustomerRepository
    public ShippingCustomer GetById(int id)
        ShippingCustomerDataTable table = new ShippingCustomerDataTable();
        ShippingCustomerTableAdapter adapter = new ShippingCustomerTableAdapter();

        adapter.FillById(table, id);

        if (table.Rows.Count > 0)
            return DataSetMapper.MapShippingCustomer(table.Rows[0]);

        return null;

Not the greatest implementation, but the important concept is we are not passing back a DataRow, we are passing back a mapped object. We can do the same type of mapping for LINQ to SQL and EF. We then just need a mapper:

public class DataSetMapper
    public static ShippingCustomer MapShippingCustomer(ShippingCustomerDataRow row)
        return new ShippingCustomer() { FirstName=row.FirstName, row.LastName
            , AddressLine=row.AddressLine, City=row.City,  row.PostalCode, row.State}

Important Takeaway

Perhaps the examples in this entry aren’t the best, but they do illustrate that the application and UI use the domain model and not the bits thrown back from Microsoft. And, I can easily switch from one data model to another without rewriting the application. To put this another way, my data access and database are very loosely coupled to the application.

Peace and Grace,

Twitter: @gbworld


Leave a Reply

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

You are commenting using your 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

%d bloggers like this: