How does LINQ to SQL affect the architecture of your application?

During the past week, I have been converting a few applications to using Visual Studio 2008 and LINQ. It’s nice to be able to remove all of the SQL query strings from my code and replace them with nice and clean LINQ syntax. In the beginning, I was a bit confused on how to structure LINQ into the various layers of my applications, but after a few iterations, I think I have found a reasonably good structure.

Before using LINQ

Traditionally I have structured my application back-end into a Business Entities layer, a Data Access layer and Business Logic layer.

Business Entities

In the Business Entities layer, I create classes of simple objects that are used by many different parts of the application. Examples of such simple classes could be Product, Order and Shopping Cart. These classes typically contain simple constructors, some properties with setters and getters and maybe some functions to do some calculations on the data contained in the object.

An example:

public class Product

{

    public int ProductId { get; set; } // New VS 2008 syntax!

    public double Listprice { get; set; }

    public double Discount { get; set; }

   

    public double Price

    {

        get { return (Listprice - Discount); }

    }

}

Data Access Layer

The Data Access layer contains a lot of functions accessing the database. There would be functions for doing create, read, update and delete of records in the database. A lot of strings containing embedded SQL statements would be contained in this layer.

An example:

public Product GetProduct(int productId)

{

    Product product = null;

    string sqlCommand = @"

        SELECT ProductId, Listprice, Discount

        FROM Products

        WHERE (ProductId = ?)";

    OdbcConnection myConnection = new OdbcConnection(connectionString);

    OdbcCommand myCommand = new OdbcCommand(sqlCommand, myConnection);

    myCommand.Parameters.Add("ProductId", OdbcType.Int).Value = productId;

    OdbcDataAdapter myDataAdapter = new OdbcDataAdapter(myCommand);

    DataSet ds = new DataSet();

    myDataAdapter.Fill(ds, "Products");

    if (ds.Tables["Products"].Rows.Count == 1)

    {

        product = new Product();

        product.ProductId = (int) ds.Tables["Products"].Rows[0]["ProductId"];

        product.Listprice = (double)ds.Tables["Products"].Rows[0]["Listprice"];

        product.Discount = (double)ds.Tables["Products"].Rows[0]["Discount"];

    }

    return product;

}

Business Logic Layer

In the Business Logic layer there are functions that perform higher level business processing and will often invoke several operations across multiple functions from the Data Access layer.

An example:

public static Order CreateOrder(ShoppingCart shoppingCart, CommerceUser user)

{

    Customer customer = CustomerDAL.Find(shoppingCart.BillingAddress.Name, user.Email);

    if (customer == null)

    {

        customer = CustomerDAL.Create(

            shoppingCart.BillingAddress.Name,

            user.Email);

    }

    Order order = new Order(

        shoppingCart.UserGuid,

        customer.CustomerId,

        customer.Email,

        shoppingCart.BillingAddress.Copy(),

        shoppingCart.ShippingAddress.Copy(),

        shoppingCart.Comments);

    foreach (ShoppingCartItem item in shoppingCart.Items)

    {

        OrderItem orderItem = new OrderItem(

            (OrderItem.OrderItemType)item.ItemType,

            item.CatalogId,

            item.SKU,

            item.ProductVariantName,

            item.Quantity,

            item.Price,

            item.Discount,

            item.TaxRate);

        order.AddItem(orderItem);

    }

    // Check that everything is in stock

    foreach (OrderItem orderItem in order.OrderItems)

    {

        if (orderItem.ItemType == (int)OrderItem.OrderItemType.Product)

        {

            int quantityInStock = InventoryDAL.GetQuantityInStock(orderItem.CatalogId, orderItem.SKU);

            if (orderItem.Quantity > quantityInStock)

                throw new OutOfStockException("Item out of stock");

        }

    }

    OrderDAL.CreateOrder(order);

    // Reserve all the items in the order from the inventory

    foreach (OrderItem orderItem in order.OrderItems)

    {

        if (orderItem.ItemType == (int)OrderItem.OrderItemType.Product)

        {

            InventoryDAL.ReserveInventory(

                order.OrderId,

                orderItem.CatalogId,

                orderItem.SKU,

       orderItem.Quantity);

        }

    }

    OrderEvent orderEvent = new OrderEvent(

        DateTime.Now,

        OrderEvent.EventCode.OrderReceived,

        "");

    ExecuteOrderReceived(order, orderEvent);

    return order;

}

Using LINQ

How does the use of LINQ affect the above structure of the application? When you work with LINQ, the first thing you want to do is to make sure that your database is structured as well as possible. That is if you have the flexibility to change the schema at this point in time. LINQ auto-generates entity classes based on the database schema and the generated classes will be a lot easier to work with when the data model is nice and clean.

Where to add the “LINQ to SQL Classes” item?

When you want to use LINQ to SQL in your application from Visual Studio, the first thing you do is to add a “LINQ to SQL Classes” item in your project. To which layer does this item belong? My initial thought was to add this item to the Data Access layer, but after a little experimenting, I have decided that this item belongs to the Business Entity layer of the application. The reason behind this is that “LINQ to SQL Classes” auto-generates a lot of classes is very similar to the classes you traditionally write in the Business Entities layer. If you look closer, you will see that the classes generated are partial classes. This makes it possible to split the declaration of a single class into multiple source files. It’s therefore very easy to add functionality to the auto-generated classes.

Let’s take the Product class we started with as an example. The Product class that we originally created by hand contained four properties: ProductId, Listprice, Discount and GetPrice. If we look at a Products table in the database, we could expect ProductId, Listprice and Discount to be present as columns in the table, while the GetPrice would maybe be computed dynamically at run-time. LINQ to SQL would auto-generate a Product class for us with all the properties corresponding to the columns found in the Products table. We could then extend the auto-generated Product class like this in another source file (Products.cs):

public partial class Product

{

    public double Price

    {

        get { return (Listprice - Discount); }

    }

}

To be able to extend a class using partial classes, you need the two classes to be declared within the same namespace. Having the “LINQ to SQL Classes” item (with the auto-generated clases) in the Business Entities layer together with the extended classes you write by hand therefor makes a lot of sense.

How was our Business Entities layer affected by using LINQ? Well, a lot of code-lines that we had to write by hand before using LINQ could be removed and we were only left with the extensions to the partial classes that wee still need. All the interfaces could still be exactly the same as before and we would not have to change other parts of our application.

Rewrite of the Data Access Layer using LINQ

Now let’s take a look at how our Data Access layer is affected by using LINQ. Here is the new version of the GetProduct function:

static public Product GetProduct(int productId)

{

    CommerceDataContext db = new CommerceDataContext();

    Product product = (from p in db.Products where p.ProductId == productId select p).SingleOrDefault();

   

    return product;

}

Wow! That was a LOT simpler than the previous version not using LINQ. In addition the LINQ query above is type safe and compile-time checked. Again, the interface to our Data Access Layer classes / functions remains exactly the same and there is no need to rewrite other parts of the application. I can even convert the data access classes one by one and have the old SQL queries in some classes and LINQ queries in others while I’m rewriting the code.

Business Logic Layer

How is the Business Logic layer affected by using LINQ? In my case, the Business Logic layer was not affected at all. All the changes made have been internal to each class used. This is also a great benefit.

What do you think?

Comments

• Anonymous
  August 30, 2007
  DataContext implements IDisposable, so you should probably call it. I wrote a simple library to allow you to create strongly-typed CRUD method for LINQ objects here: http://www.atrevido.net/blog/2007/08/26/A+LINQ+To+The+CRUD.aspx This allows you to write stuff like: MyDatabase.Things.Insert(myThing) and MyDatabase.Things.GetByKey(thingId) Also, I define a simple helper function to encapsulate using a datacontext: var product = MyDatabase.Use(dc => dc.Products.First());

• Anonymous
  March 26, 2009
  Hello, there is something I dont like You are creating the LINq to SQL classes on the business layer, but in the data access layer you reference the Product entity, which is in the layer. This means that Business Logiclayer depends on Data Acess Logic Layer and Viceversa too which I dont think its architecturally correct.

• Anonymous
  January 24, 2011
  Looks good to me the way you have designed the layers. Simple and feasible design approach. I was looking for exactly something like this and I find this article. Thanks a lot !!

• Anonymous
  January 24, 2011
  The only thing missing in this article is the pictorial representation of layers. It would instantly click then to all fellow readers how the layers are designed. Layer to layer References UI -> Business Access Layer Business Access Layer -> Business Entities Layer + DAL DAL -> Business Entities Layer No circular references.