Randolph Cabral’s Weblog

…beauty is just a dimmer switch away…

Exploring N-Tier Architecture with LINQ to SQL (Part 4 of n)

Posted by Randolph Cabral on Wednesday, May 21, 2008

Sanity Check

Now that we’ve completed the first of what I’m sure will be many coding iterations of our business entity layer solution it is now time to do some testing.  In this post we’ll take some time to make sure our approach holds up to a typical usage scenario.  To do this, I’ve put together a Visual Studio solution that contains a web application and a couple of class libraries.  One class library for our LINQ to SQL entities and another for our business entities.  We’ll attempt to build out the domain model and use it in our web UI layer to demonstrate how we could interact with the business entities.

Let’s start with the LINQ to SQL class library project.  In this project we have Northwind.dbml, Northwind.dbml.layout, and Northwind.designer.cs files.  The files and its contents were generated by the Visual Studio 2008 IDE using SQLMetal after I dragged the Customer, Order, Order_Detail, and Product tables on to the design surface from the Server Explorer.  I’ve taken a screen shot of the resulting diagram from the designer (below).  Each box represents a table in the database which in turn is generated into a corresponding LINQ to SQL class complete with mappings and a DataContext.

 

Business Context

Moving over to the business entity project, I’ve written a class for each LINQ to SQL entity as well as a business context class that wraps the DataContext class.  The following code sample implements the business context (in its entirety) in much the same way we explored in Part 2 of this series.  An item to note is the addition of two methods that handle attachment of disconnected entities for inserting and deleting at lines 97 and 108 respectively.  In order to support all business entity types, we use reflection to find the LinqEntity member and use the composed DataContext to invoke data store logic.

    1   public partial class NorthwindBusinessContext : IDisposable

    2   {

    3     public NorthwindBusinessContext()

    4     {

    5       DataContext = new NorthwindDataContext();

    6     }

    7 

    8     public event PersistStateEvent OnBeforePersistState;

    9     public event PersistStateEvent OnAfterPersistState;

   10 

   11     protected NorthwindDataContext DataContext { get; set; }

   12 

   13     #region Factory Methods

   14 

   15     public CustomerBus GetCustomerBusBy(string customerID)

   16     {

   17       var linqEntity = DataContext.GetTable<Customer>()

   18         .Single(entity => entity.CustomerID == customerID);

   19       var ret = new CustomerBus(linqEntity);

   20       return ret;

   21     }

   22 

   23     public IEnumerable<CustomerBus> GetAllCustomerBuses()

   24     {

   25       var ret = new Collection<CustomerBus>();

   26       var result = DataContext.GetTable<Customer>();

   27       foreach (var linqEntity in result)

   28       {

   29         var entity = new CustomerBus(linqEntity);

   30         ret.Add(entity);

   31       }

   32       return ret;

   33     }

   34 

   35     public OrderBus GetOrderBusBy(int orderID)

   36     {

   37       var linqEntity = DataContext.GetTable<Order>()

   38         .Single(entity => entity.OrderID == orderID);

   39       var ret = new OrderBus(linqEntity);

   40       return ret;

   41     }

   42 

   43     public IEnumerable<OrderBus> GetAllOrderBuses()

   44     {

   45       var ret = new Collection<OrderBus>();

   46       var result = DataContext.GetTable<Order>();

   47       foreach (var linqEntity in result)

   48       {

   49         var entity = new OrderBus(linqEntity);

   50         ret.Add(entity);

   51       }

   52       return ret;

   53     }

   54 

   55     public Detail GetDetailBy(int orderID, int productID)

   56     {

   57       var linqEntity = DataContext.GetTable<Order_Detail>()

   58         .Single(entity => entity.OrderID == orderID && entity.ProductID == productID);

   59       var ret = new Detail(linqEntity);

   60       return ret;

   61     }

   62 

   63     public IEnumerable<Detail> GetAllDetails()

   64     {

   65       var ret = new Collection<Detail>();

   66       var result = DataContext.GetTable<Order_Detail>();

   67       foreach (var linqEntity in result)

   68       {

   69         var entity = new Detail(linqEntity);

   70         ret.Add(entity);

   71       }

   72       return ret;

   73     }

   74 

   75     public ProductBus GetProductBusBy(int productID)

   76     {

   77       var linqEntity = DataContext.GetTable<Product>()

   78         .Single(entity => entity.ProductID == productID);

   79       var ret = new ProductBus(linqEntity);

   80       return ret;

   81     }

   82 

   83     public IEnumerable<ProductBus> GetAllProductBuses()

   84     {

   85       var ret = new Collection<ProductBus>();

   86       var result = DataContext.GetTable<Product>();

   87       foreach (var linqEntity in result)

   88       {

   89         var entity = new ProductBus(linqEntity);

   90         ret.Add(entity);

   91       }

   92       return ret;

   93     }

   94 

   95     #endregion

   96 

   97     public void DeleteOnPersistState(object entity)

   98     {

   99       var linqEntityRef = entity.GetType().GetProperty(“LinqEntity”,

  100         System.Reflection.BindingFlags.Instance

  101         | System.Reflection.BindingFlags.NonPublic

  102         | System.Reflection.BindingFlags.Public

  103         | System.Reflection.BindingFlags.FlattenHierarchy).GetValue(entity, null);

  104       var linqEntityType = linqEntityRef.GetType();

  105       DataContext.GetTable(linqEntityType).DeleteOnSubmit(linqEntityRef);

  106     }

  107 

  108     public void AddOnPersistState(object entity)

  109     {

  110       var linqEntityRef = entity.GetType().GetProperty(“LinqEntity”,

  111         System.Reflection.BindingFlags.Instance

  112         | System.Reflection.BindingFlags.NonPublic

  113         | System.Reflection.BindingFlags.Public

  114         | System.Reflection.BindingFlags.FlattenHierarchy).GetValue(entity, null);

  115       var linqEntityType = linqEntityRef.GetType();

  116       DataContext.GetTable(linqEntityType).InsertOnSubmit(linqEntityRef);

  117     }

  118 

  119     public void PersistState()

  120     {

  121       if (OnBeforePersistState != null)

  122         OnBeforePersistState(this, new EventArgs());

  123 

  124       DataContext.SubmitChanges();

  125 

  126       if (OnAfterPersistState != null)

  127         OnAfterPersistState(this, new EventArgs());

  128     }

  129 

  130     #region IDisposable Members

  131 

  132     public void Dispose()

  133     {

  134       DataContext.Dispose();

  135     }

  136 

  137     #endregion

  138   }

 

Business Entity

Below is a sample of the CustomerBus business entity.  I’ve removed some of the properties and associations that implement the same pattern for brevity.  Each of the business classes implemented in this project employ the design constructs we explored in Part 3 of this series.  Additionally, I’ve written an IValidateable interface and a SimpleDataValidator class that reads the custom attributes found on each public property and populates a collection of validation errors when any of the tests fail.  More on validation in a future post.  In this post we’ll focus on the business context and the business entities.

    1   public partial class CustomerBus : IEntity, IValidateable

    2   {

    3     public CustomerBus()

    4     {

    5       Validators.Add(new SimpleDataValidator());

    6     }

    7 

    8     internal CustomerBus(Customer linqEntity) : this()

    9     {

   10       LinqEntity = linqEntity;

   11     }

   12 

   13     private Customer _linqEntity = null;

   14     internal Customer LinqEntity

   15     {

   16       get

   17       {

   18         if (_linqEntity == null)

   19           _linqEntity = new Customer();

   20 

   21         return _linqEntity;

   22       }

   23       set

   24       {

   25         _linqEntity = value;

   26       }

   27     }

   28 

   29     #region Properties

   30 

   31     [Required(“‘CustomerID’ is a required field.”)]

   32     [MaxLength(5, “The ‘CustomerID’ field cannot accept more than 5 characters.”)]

   33     public virtual string CustomerID

   34     {

   35       get

   36       {

   37         return LinqEntity.CustomerID;

   38       }

   39       set

   40       {

   41         LinqEntity.CustomerID = value;

   42       }

   43     }

   44 

   45     [Required(“‘CompanyName’ is a required field.”)]

   46     [MaxLength(40, “The ‘CompanyName’ field cannot accept more than 40 characters.”)]

   47     public virtual string CompanyName

   48     {

   49       get

   50       {

   51         return LinqEntity.CompanyName;

   52       }

   53       set

   54       {

   55         LinqEntity.CompanyName = value;

   56       }

   57     }

   58     …

   59 

   60     #endregion

   61 

   62     #region Associations

   63 

   64     public virtual void AddOrderBus(OrderBus entity)

   65     {

   66       _Orders.Add(entity);

   67       LinqEntity.Orders.Add(entity.LinqEntity);

   68     }

   69 

   70     public virtual void RemoveOrderBus(OrderBus entity)

   71     {

   72       _Orders.Remove(entity);

   73       LinqEntity.Orders.Remove(entity.LinqEntity);

   74     }

   75 

   76     public virtual OrderBusCollection Orders

   77     {

   78       get

   79       {

   80         if (_Orders == null)

   81           _Orders = new OrderBusCollection(LinqEntity.Orders);

   82 

   83         return _Orders;

   84       }

   85     }

   86     private OrderBusCollection _Orders = null;

   87 

   88 

   89     #endregion

   90 

   91     #region IValidateable Members

   92 

   93     public void ClearValidationErrors()

   94     {

   95       ValidationErrors.Clear();

   96     }

   97 

   98     public bool HasValidationErrors

   99     {

  100       get { return (ValidationErrors.Count > 0); }

  101     }

  102 

  103     public List<IValidator> Validators

  104     {

  105       get { return _validators; }

  106     }

  107     private List<IValidator> _validators = new List<IValidator>();

  108 

  109     public void Validate()

  110     {

  111       foreach (var v in Validators)

  112       {

  113         v.Validate(this);

  114       }

  115 

  116       if (HasValidationErrors)

  117       {

  118         var errorMessages = string.Empty;

  119 

  120         foreach (var v in ValidationErrors)

  121           errorMessages += string.Format(“{0}”, v.GetValidationErrorMessage());

  122 

  123         if (ValidationErrorHandling == ValidationErrorHandling.ThrowException)

  124           throw new ValidationException(errorMessages);

  125       }

  126     }

  127 

  128     public ValidationErrorHandling ValidationErrorHandling

  129     {

  130       get

  131       {

  132         return _validationErrorHandling;

  133       }

  134       set

  135       {

  136         _validationErrorHandling = value;

  137       }

  138     }

  139     private ValidationErrorHandling _validationErrorHandling = ValidationErrorHandling.ThrowException;

  140 

  141     public List<IValidationError> ValidationErrors

  142     {

  143       get { return _validationErrors; }

  144     }

  145     private List<IValidationError> _validationErrors = new List<IValidationError>();

  146 

  147     #endregion

  148   }

 

Application Tier 

In the web application, we have a web.config file and a Default.aspx with its code file Default.aspx.cs.  We won’t be making any modifications to the HTML markup in the Default.aspx file because in this test all we want to do is run some basic reads and updates on the data store.  To be able to use the classes we’ve defined in the business library project we’ll add a reference to the project.

 

Now let’s take a look at the code written in the Page_Load() event handler.  Starting with line 10, we instantiate a business context object so that we can request a CustomerBus object with one of the factory methods.  We want to test the object tracking capabilities of the underlying DataContext so on line 12 we make sure the City property is updated.  To test the associations we loop through the Orders collection and modify the OrderDate property.  Taking this a step further, we do a similar operation and traverse the OrderDetails collection and update both quantity and price by incrementing each property.  Finally, a call to the PersistState() method completes this test.  Remember, each time we traverse the object graph’s entity associations, we incur a lazy-load query against the data store because such is the default behavior of LINQ to SQL.  You can easily modify this option by setting the DataContext DeferredLoadingEnabled property to false.  This won’t hurt our lazy-loading code in the business entity layer because the underlying DataContext will have preloaded the entities.  Our business entities will simply request the objects from the DataContext.

    1 using System;

    2 using Northwind.Entities;

    3 

    4 namespace L2SnTierTestWeb

    5 {

    6   public partial class _Default : System.Web.UI.Page

    7   {

    8     protected void Page_Load(object sender, EventArgs e)

    9     {

   10       var ctx = new NorthwindBusinessContext();

   11       var cust = ctx.GetCustomerBusBy(“ALFKI”);

   12       cust.City = (cust.City == “Berlin”) ? “Hamburg” : “Berlin”;

   13 

   14       foreach (var order in cust.Orders)

   15       {

   16         order.OrderDate = DateTime.Now;

   17         foreach (var orderdetail in order.OrderDetails)

   18         {

   19           orderdetail.Quantity++;

   20           orderdetail.Product.UnitPrice++;

   21         }

   22       }

   23 

   24       ctx.PersistState();

   25     }

   26   }

   27 }

 

SQL Profiler

At this point we can open up SQL Profiler and inspect the resulting queries.  On the reads, we can see a total twenty of queries, one for the initial customer query and one for each lazy-loading relationship.  Again, if you want the object graph to preload, setting a property on the DataContext is all that is required.  On the update side, which starts at line 130, we are greated with a very nice set of batched updates that handle customers, orders, and details.   This is consistent with using LINQ to SQL out of the box.  Only now we’re working with our own business entities where we can add domain specific logic.

    1 exec sp_reset_connection

    2 go

    3 exec sp_executesql N‘SELECT [t0].[CustomerID], [t0].[CompanyName], [t0].[ContactName], [t0].[ContactTitle], [t0].[Address], [t0].[City], [t0].[Region], [t0].[PostalCode],

    4 [t0].[Country], [t0].[Phone], [t0].[Fax]

    5 FROM [dbo].[Customers] AS [t0]

    6 WHERE [t0].[CustomerID] = @p0′, N‘@p0 nvarchar(5)’, @p0 = N‘ALFKI’

    7 go

    8 exec sp_reset_connection

    9 go

   10 exec sp_executesql N‘SELECT [t0].[OrderID], [t0].[CustomerID], [t0].[EmployeeID], [t0].[OrderDate], [t0].[RequiredDate], [t0].[ShippedDate], [t0].[ShipVia], [t0].[Freight],

   11 [t0].[ShipName], [t0].[ShipAddress], [t0].[ShipCity], [t0].[ShipRegion], [t0].[ShipPostalCode], [t0].[ShipCountry]

   12 FROM [dbo].[Orders] AS [t0]

   13 WHERE [t0].[CustomerID] = @p0′, N‘@p0 nvarchar(5)’, @p0 = N‘ALFKI’

   14 go

   15 exec sp_reset_connection

   16 go

   17 exec sp_executesql N‘SELECT [t0].[OrderID], [t0].[ProductID], [t0].[UnitPrice], [t0].[Quantity], [t0].[Discount]

   18 FROM [dbo].[Order Details] AS [t0]

   19 WHERE [t0].[OrderID] = @p0′, N‘@p0 int’, @p0 = 10643

   20 go

   21 exec sp_reset_connection

   22 go

   23 exec sp_executesql N‘SELECT [t0].[ProductID], [t0].[ProductName], [t0].[SupplierID], [t0].[CategoryID], [t0].[QuantityPerUnit], [t0].[UnitPrice], [t0].[UnitsInStock],

   24 [t0].[UnitsOnOrder], [t0].[ReorderLevel], [t0].[Discontinued]

   25 FROM [dbo].[Products] AS [t0]

   26 WHERE [t0].[ProductID] = @p0′, N‘@p0 int’, @p0 = 28

   27 go

   28 exec sp_reset_connection

   29 go

   30 exec sp_executesql N‘SELECT [t0].[ProductID], [t0].[ProductName], [t0].[SupplierID], [t0].[CategoryID], [t0].[QuantityPerUnit], [t0].[UnitPrice], [t0].[UnitsInStock],

   31 [t0].[UnitsOnOrder], [t0].[ReorderLevel], [t0].[Discontinued]

   32 FROM [dbo].[Products] AS [t0]

   33 WHERE [t0].[ProductID] = @p0′, N‘@p0 int’, @p0 = 39

   34 go

   35 exec sp_reset_connection

   36 go

   37 exec sp_executesql N‘SELECT [t0].[ProductID], [t0].[ProductName], [t0].[SupplierID], [t0].[CategoryID], [t0].[QuantityPerUnit], [t0].[UnitPrice], [t0].[UnitsInStock],

   38 [t0].[UnitsOnOrder], [t0].[ReorderLevel], [t0].[Discontinued]

   39 FROM [dbo].[Products] AS [t0]

   40 WHERE [t0].[ProductID] = @p0′, N‘@p0 int’, @p0 = 46

   41 go

   42 exec sp_reset_connection

   43 go

   44 exec sp_executesql N‘SELECT [t0].[OrderID], [t0].[ProductID], [t0].[UnitPrice], [t0].[Quantity], [t0].[Discount]

   45 FROM [dbo].[Order Details] AS [t0]

   46 WHERE [t0].[OrderID] = @p0′, N‘@p0 int’, @p0 = 10692

   47 go

   48 exec sp_reset_connection

   49 go

   50 exec sp_executesql N‘SELECT [t0].[ProductID], [t0].[ProductName], [t0].[SupplierID], [t0].[CategoryID], [t0].[QuantityPerUnit], [t0].[UnitPrice], [t0].[UnitsInStock],

   51 [t0].[UnitsOnOrder], [t0].[ReorderLevel], [t0].[Discontinued]

   52 FROM [dbo].[Products] AS [t0]

   53 WHERE [t0].[ProductID] = @p0′, N‘@p0 int’, @p0 = 63

   54 go

   55 exec sp_reset_connection

   56 go

   57 exec sp_executesql N‘SELECT [t0].[OrderID], [t0].[ProductID], [t0].[UnitPrice], [t0].[Quantity], [t0].[Discount]

   58 FROM [dbo].[Order Details] AS [t0]

   59 WHERE [t0].[OrderID] = @p0′, N‘@p0 int’, @p0 = 10702

   60 go

   61 exec sp_reset_connection

   62 go

   63 exec sp_executesql N‘SELECT [t0].[ProductID], [t0].[ProductName], [t0].[SupplierID], [t0].[CategoryID], [t0].[QuantityPerUnit], [t0].[UnitPrice], [t0].[UnitsInStock],

   64 [t0].[UnitsOnOrder], [t0].[ReorderLevel], [t0].[Discontinued]

   65 FROM [dbo].[Products] AS [t0]

   66 WHERE [t0].[ProductID] = @p0′, N‘@p0 int’, @p0 = 3

   67 go

   68 exec sp_reset_connection

   69 go

   70 exec sp_executesql N‘SELECT [t0].[ProductID], [t0].[ProductName], [t0].[SupplierID], [t0].[CategoryID], [t0].[QuantityPerUnit], [t0].[UnitPrice], [t0].[UnitsInStock],

   71 [t0].[UnitsOnOrder], [t0].[ReorderLevel], [t0].[Discontinued]

   72 FROM [dbo].[Products] AS [t0]

   73 WHERE [t0].[ProductID] = @p0′, N‘@p0 int’, @p0 = 76

   74 go

   75 exec sp_reset_connection

   76 go

   77 exec sp_executesql N‘SELECT [t0].[OrderID], [t0].[ProductID], [t0].[UnitPrice], [t0].[Quantity], [t0].[Discount]

   78 FROM [dbo].[Order Details] AS [t0]

   79 WHERE [t0].[OrderID] = @p0′, N‘@p0 int’, @p0 = 10835

   80 go

   81 exec sp_reset_connection

   82 go

   83 exec sp_executesql N‘SELECT [t0].[ProductID], [t0].[ProductName], [t0].[SupplierID], [t0].[CategoryID], [t0].[QuantityPerUnit], [t0].[UnitPrice], [t0].[UnitsInStock],

   84 [t0].[UnitsOnOrder], [t0].[ReorderLevel], [t0].[Discontinued]

   85 FROM [dbo].[Products] AS [t0]

   86 WHERE [t0].[ProductID] = @p0′, N‘@p0 int’, @p0 = 59

   87 go

   88 exec sp_reset_connection

   89 go

   90 exec sp_executesql N‘SELECT [t0].[ProductID], [t0].[ProductName], [t0].[SupplierID], [t0].[CategoryID], [t0].[QuantityPerUnit], [t0].[UnitPrice], [t0].[UnitsInStock],

   91 [t0].[UnitsOnOrder], [t0].[ReorderLevel], [t0].[Discontinued]

   92 FROM [dbo].[Products] AS [t0]

   93 WHERE [t0].[ProductID] = @p0′, N‘@p0 int’, @p0 = 77

   94 go

   95 exec sp_reset_connection

   96 go

   97 exec sp_executesql N‘SELECT [t0].[OrderID], [t0].[ProductID], [t0].[UnitPrice], [t0].[Quantity], [t0].[Discount]

   98 FROM [dbo].[Order Details] AS [t0]

   99 WHERE [t0].[OrderID] = @p0′, N‘@p0 int’, @p0 = 10952

  100 go

  101 exec sp_reset_connection

  102 go

  103 exec sp_executesql N‘SELECT [t0].[ProductID], [t0].[ProductName], [t0].[SupplierID], [t0].[CategoryID], [t0].[QuantityPerUnit], [t0].[UnitPrice], [t0].[UnitsInStock],

  104 [t0].[UnitsOnOrder], [t0].[ReorderLevel], [t0].[Discontinued]

  105 FROM [dbo].[Products] AS [t0]

  106 WHERE [t0].[ProductID] = @p0′, N‘@p0 int’, @p0 = 6

  107 go

  108 exec sp_reset_connection

  109 go

  110 exec sp_executesql N‘SELECT [t0].[OrderID], [t0].[ProductID], [t0].[UnitPrice], [t0].[Quantity], [t0].[Discount]

  111 FROM [dbo].[Order Details] AS [t0]

  112 WHERE [t0].[OrderID] = @p0′, N‘@p0 int’, @p0 = 11011

  113 go

  114 exec sp_reset_connection

  115 go

  116 exec sp_executesql N‘SELECT [t0].[ProductID], [t0].[ProductName], [t0].[SupplierID], [t0].[CategoryID], [t0].[QuantityPerUnit], [t0].[UnitPrice], [t0].[UnitsInStock],

  117 [t0].[UnitsOnOrder], [t0].[ReorderLevel], [t0].[Discontinued]

  118 FROM [dbo].[Products] AS [t0]

  119 WHERE [t0].[ProductID] = @p0′, N‘@p0 int’, @p0 = 58

  120 go

  121 exec sp_reset_connection

  122 go

  123 exec sp_executesql N‘SELECT [t0].[ProductID], [t0].[ProductName], [t0].[SupplierID], [t0].[CategoryID], [t0].[QuantityPerUnit], [t0].[UnitPrice], [t0].[UnitsInStock],

  124 [t0].[UnitsOnOrder], [t0].[ReorderLevel], [t0].[Discontinued]

  125 FROM [dbo].[Products] AS [t0]

  126 WHERE [t0].[ProductID] = @p0′, N‘@p0 int’, @p0 = 71

  127 go

  128 exec sp_reset_connection

  129 go

  130 SET TRANSACTION ISOLATION LEVEL READ COMMITTED;BEGIN TRANSACTION

  131 go

  132 exec sp_executesql N‘UPDATE [dbo].[Customers]

  133 SET [City] = @p1

  134 WHERE [CustomerID] = @p0′, N‘@p0 nchar(5),@p1 nvarchar(6)’, @p0 = N‘ALFKI’, @p1 = N‘Berlin’

  135 go

  136 exec sp_executesql N‘UPDATE [dbo].[Orders]

  137 SET [OrderDate] = @p1

  138 WHERE [OrderID] = @p0′, N‘@p0 int,@p1 datetime’, @p0 = 10643, @p1 = ‘May 21 2008 12:28:08:667AM’

  139 go

  140 exec sp_executesql N‘UPDATE [dbo].[Orders]

  141 SET [OrderDate] = @p1

  142 WHERE [OrderID] = @p0′, N‘@p0 int,@p1 datetime’, @p0 = 10692, @p1 = ‘May 21 2008 12:28:08:677AM’

  143 go

  144 exec sp_executesql N‘UPDATE [dbo].[Orders]

  145 SET [OrderDate] = @p1

  146 WHERE [OrderID] = @p0′, N‘@p0 int,@p1 datetime’, @p0 = 10702, @p1 = ‘May 21 2008 12:28:08:680AM’

  147 go

  148 exec sp_executesql N‘UPDATE [dbo].[Orders]

  149 SET [OrderDate] = @p1

  150 WHERE [OrderID] = @p0′, N‘@p0 int,@p1 datetime’, @p0 = 10835, @p1 = ‘May 21 2008 12:28:08:687AM’

  151 go

  152 exec sp_executesql N‘UPDATE [dbo].[Orders]

  153 SET [OrderDate] = @p1

  154 WHERE [OrderID] = @p0′, N‘@p0 int,@p1 datetime’, @p0 = 10952, @p1 = ‘May 21 2008 12:28:08:693AM’

  155 go

  156 exec sp_executesql N‘UPDATE [dbo].[Orders]

  157 SET [OrderDate] = @p1

  158 WHERE [OrderID] = @p0′, N‘@p0 int,@p1 datetime’, @p0 = 11011, @p1 = ‘May 21 2008 12:28:08:697AM’

  159 go

  160 exec sp_executesql N‘UPDATE [dbo].[Order Details]

  161 SET [Quantity] = @p2

  162 WHERE ([OrderID] = @p0) AND ([ProductID] = @p1)’, N‘@p0 int,@p1 int,@p2 smallint’, @p0 = 10643, @p1 = 28, @p2 = 28

  163 go

  164 exec sp_executesql N‘UPDATE [dbo].[Order Details]

  165 SET [Quantity] = @p2

  166 WHERE ([OrderID] = @p0) AND ([ProductID] = @p1)’, N‘@p0 int,@p1 int,@p2 smallint’, @p0 = 10643, @p1 = 39, @p2 = 34

  167 go

  168 exec sp_executesql N‘UPDATE [dbo].[Order Details]

  169 SET [Quantity] = @p2

  170 WHERE ([OrderID] = @p0) AND ([ProductID] = @p1)’, N‘@p0 int,@p1 int,@p2 smallint’, @p0 = 10643, @p1 = 46, @p2 = 15

  171 go

  172 exec sp_executesql N‘UPDATE [dbo].[Order Details]

  173 SET [Quantity] = @p2

  174 WHERE ([OrderID] = @p0) AND ([ProductID] = @p1)’, N‘@p0 int,@p1 int,@p2 smallint’, @p0 = 10692, @p1 = 63, @p2 = 33

  175 go

  176 exec sp_executesql N‘UPDATE [dbo].[Order Details]

  177 SET [Quantity] = @p2

  178 WHERE ([OrderID] = @p0) AND ([ProductID] = @p1)’, N‘@p0 int,@p1 int,@p2 smallint’, @p0 = 10702, @p1 = 3, @p2 = 19

  179 go

  180 exec sp_executesql N‘UPDATE [dbo].[Order Details]

  181 SET [Quantity] = @p2

  182 WHERE ([OrderID] = @p0) AND ([ProductID] = @p1)’, N‘@p0 int,@p1 int,@p2 smallint’, @p0 = 10702, @p1 = 76, @p2 = 28

  183 go

  184 exec sp_executesql N‘UPDATE [dbo].[Order Details]

  185 SET [Quantity] = @p2

  186 WHERE ([OrderID] = @p0) AND ([ProductID] = @p1)’, N‘@p0 int,@p1 int,@p2 smallint’, @p0 = 10835, @p1 = 59, @p2 = 28

  187 go

  188 exec sp_executesql N‘UPDATE [dbo].[Order Details]

  189 SET [Quantity] = @p2

  190 WHERE ([OrderID] = @p0) AND ([ProductID] = @p1)’, N‘@p0 int,@p1 int,@p2 smallint’, @p0 = 10835, @p1 = 77, @p2 = 15

  191 go

  192 exec sp_executesql N‘UPDATE [dbo].[Order Details]

  193 SET [Quantity] = @p2

  194 WHERE ([OrderID] = @p0) AND ([ProductID] = @p1)’, N‘@p0 int,@p1 int,@p2 smallint’, @p0 = 10952, @p1 = 6, @p2 = 29

  195 go

  196 exec sp_executesql N‘UPDATE [dbo].[Order Details]

  197 SET [Quantity] = @p2

  198 WHERE ([OrderID] = @p0) AND ([ProductID] = @p1)’, N‘@p0 int,@p1 int,@p2 smallint’, @p0 = 10952, @p1 = 28, @p2 = 15

  199 go

  200 exec sp_executesql N‘UPDATE [dbo].[Order Details]

  201 SET [Quantity] = @p2

  202 WHERE ([OrderID] = @p0) AND ([ProductID] = @p1)’, N‘@p0 int,@p1 int,@p2 smallint’, @p0 = 11011, @p1 = 58, @p2 = 53

  203 go

  204 exec sp_executesql N‘UPDATE [dbo].[Order Details]

  205 SET [Quantity] = @p2

  206 WHERE ([OrderID] = @p0) AND ([ProductID] = @p1)’, N‘@p0 int,@p1 int,@p2 smallint’, @p0 = 11011, @p1 = 71, @p2 = 33

  207 go

  208 exec sp_executesql N‘UPDATE [dbo].[Products]

  209 SET [UnitPrice] = @p1

  210 WHERE [ProductID] = @p0′, N‘@p0 int,@p1 money’, @p0 = 3, @p1 = $19.0000

  211 go

  212 exec sp_executesql N‘UPDATE [dbo].[Products]

  213 SET [UnitPrice] = @p1

  214 WHERE [ProductID] = @p0′, N‘@p0 int,@p1 money’, @p0 = 6, @p1 = $34.0000

  215 go

  216 exec sp_executesql N‘UPDATE [dbo].[Products]

  217 SET [UnitPrice] = @p1

  218 WHERE [ProductID] = @p0′, N‘@p0 int,@p1 money’, @p0 = 28, @p1 = $63.6000

  219 go

  220 exec sp_executesql N‘UPDATE [dbo].[Products]

  221 SET [UnitPrice] = @p1

  222 WHERE [ProductID] = @p0′, N‘@p0 int,@p1 money’, @p0 = 39, @p1 = $27.0000

  223 go

  224 exec sp_executesql N‘UPDATE [dbo].[Products]

  225 SET [UnitPrice] = @p1

  226 WHERE [ProductID] = @p0′, N‘@p0 int,@p1 money’, @p0 = 46, @p1 = $21.0000

  227 go

  228 exec sp_executesql N‘UPDATE [dbo].[Products]

  229 SET [UnitPrice] = @p1

  230 WHERE [ProductID] = @p0′, N‘@p0 int,@p1 money’, @p0 = 58, @p1 = $22.2500

  231 go

  232 exec sp_executesql N‘UPDATE [dbo].[Products]

  233 SET [UnitPrice] = @p1

  234 WHERE [ProductID] = @p0′, N‘@p0 int,@p1 money’, @p0 = 59, @p1 = $64.0000

  235 go

  236 exec sp_executesql N‘UPDATE [dbo].[Products]

  237 SET [UnitPrice] = @p1

  238 WHERE [ProductID] = @p0′, N‘@p0 int,@p1 money’, @p0 = 63, @p1 = $52.9000

  239 go

  240 exec sp_executesql N‘UPDATE [dbo].[Products]

  241 SET [UnitPrice] = @p1

  242 WHERE [ProductID] = @p0′, N‘@p0 int,@p1 money’, @p0 = 71, @p1 = $30.5000

  243 go

  244 exec sp_executesql N‘UPDATE [dbo].[Products]

  245 SET [UnitPrice] = @p1

  246 WHERE [ProductID] = @p0′, N‘@p0 int,@p1 money’, @p0 = 76, @p1 = $27.0000

  247 go

  248 exec sp_executesql N‘UPDATE [dbo].[Products]

  249 SET [UnitPrice] = @p1

  250 WHERE [ProductID] = @p0′, N‘@p0 int,@p1 money’, @p0 = 77, @p1 = $22.0000

  251 go

  252 COMMIT TRANSACTION

  253 go

 

Sorry for the long post.  I’ll try to break it up more next time.  The take away here is that our efforts to abstract away the LINQ to SQL entities and DataContext have been successful so far.  I didn’t want to get too wrapped up in the minutia, but for those of you who absolutely need to loosen the coupling between the business layer from the LINQ to SQL layer I would recommend extracting the interface out of each of the LINQ to SQL entities and its DataContext.  This would allow for the flexibility of dependency injection (interface injection in this case) and TDD at the business layer.  Keep in mind that at some point the benefits of this loose coupling versus the return may start to diminish depending on your project.  I’ll let you decide what’s best for your project.

In the next part of this series, we’ll take a look at what we can do to finally marry the ActiveRecord pattern to our abstracted model.  I will concede that at this point, there may be no need to come full circle with AR.  I’m completely sold on the idea of using the Unit of Work pattern (context) in lieu of AR.  What I grappled with at first was this issue of trust.  I needed to trust the context and relinquish control.  The AR pattern gave me ultimate control of what DML operations were called and when they were executed on the data store.  As a developer, you’re going to need to develop a trusting relationship with the context.  Then and only then will you be able to embrace its liberating capabilities.

Advertisements

14 Responses to “Exploring N-Tier Architecture with LINQ to SQL (Part 4 of n)”

  1. Alejandro said

    hi dude, nice code!! thanks…..just one thing:
    I disagree a little bit in the fact that you put all the business logic of all entities in NorthwindBusinessContext, I think is better to encapsulate the logic of each business entity in its own collection file, don’t you think???

  2. Alejandro said

    Another comment, i have heard that context class is not Serializable, so what happen if i want to connect my BLL with the presentation layer remotely, e.g. WCF…..?
    Thanks, bye

  3. Randolph Cabral said

    @Alejandro:

    I definitely do not recommend putting all business logic in the BusinessContext. On the contrary, I would recommend against it. The code sample I’ve provided shows only query and data manipulation logic in the BusinessContext which is the extent to how I think it should be used. I think the real problem here is my incongruous writing style. I’ll try to be more clear about my thoughts in future posts.

    re: Serializable
    The DataContext is not serializable and should not be. It’s not meant to be a long lived shared instance. Generally I recommend keeping the context around for the scope of a specific Unit of Work (i.e., displaying a grid of data, new user registration, or updating a shopping cart). In your specific case, where your presentation layer is using (I assume) deserialized remotable object instances , I would recommend attaching your objects to a new context on the return trip. I’m sure there many other ways of gracefully handling this issue, but for now this is what I can come up with.

    Hope this helps!
    -Randy

  4. Jornt said

    Hi Randolph, Firstly thank you for the articles. I really like your approach.
    Could you elaborate a bit on statepersistance? Where would the Add- and DeleteOnPersistState be called?
    Thanks!

  5. Randolph Cabral said

    @Jornt:

    AddOnPersistState and DeleteOnPersistState are directly mapped to the DataContext’s InsertOnSubmit and DeleteOnSubmit methods. They are used for flagging certain entities for insertion and/or deletion from the data store when the SubmitChanges method is called on the DataContext. I’m not doing anything particularly special in the BusinessContext for these methods. At this point it simply serves as a pass-thru mechanism to expose the underlying DataContext features.

    Hope this answers your question,
    -Randy

  6. Warren said

    I was wondering if you have a zip of this solution in Visual Studio 2008?

  7. Jon H said

    Thanks for a really nice and informative series! Will there be a complete, downloadable sample available (yes, yes,…plase)?
    I’m really looking forward to the post about the IValidateable and SimpleDataValidator.

    Great work!

  8. Randolph Cabral said

    @Warren & Jon H:

    I haven’t had the time to put together a zip of this project. I will when time permits. I apologize for the recent “radio silence”. I was recently promoted to Manager of the development team where I work, and have since found less and less time to blog. When I finally get myself organized and figure out how to be a manager, I’ll be sure to get back into the blogging stuff (which I really enjoy).

    Lately, I’ve been exploring the ins and outs of Dependency Injection and POCO relational mapping solutions. I will blog about my findings when time permits.

    Thanks,
    -Randy

  9. Kjetil said

    Great stuff!

    I’ve really learned a lot of these articles, thank you.

    I’m currently rebuilding the DAL for a project for using Linq to SQL. I’m exposing the DAL as a WCF service and have been searching for the best way to do this.

    Please let me have your opinion on the following:

    Instead of putting the BusinessEntities in separate files, I made new properties, decorated with ‘DataMember’, in the partial class file for the LinqEntities and exposed the ‘data properties’ through these. This way I have easy access to all my Linq Entities and I end up with a nice clean proxy with business objects…

    I haven’t really tested out all functionality yet, but it seems to work fine…

    S.
    Kjetil

  10. Eiffel said

    Hi S.Kjetil

    I am really interested with your last solution that consist to add extra properties decorated with data member provided from your Linq entity. have you got a complete sample about your solutoin. Thanks in advance.

    • Randolph Cabral said

      @Eiffel – I apologize for such a late response. The financial troubles with the economy have forced our organization to “right-size” and I have been engulfed in taking on additional resposibilities where I work. Hence my lack of blog posts over the last few year. I don’t have a sample of this project and had to abandon it to focus on internal development efforts. I look forward to blogging again as soon as possible.

  11. Vassago said

    Sorry for the late post – hopefully you still check this blog!

    I noticed that, for inserts and deletes, you are using reflection to load a property called “LinqEntity” in every business object. However, my experience with CSLA has taught me that business layer should be encapsulating /behaviour/, not data. A business object should not necessarily be restricted to utilising only one data object? You could easily have an object in the domain model that requires information from several different database tables (as is the case for my a particular class in my project).

    In this case, only the business object itself knows which Linq objects need to be added/deleted… or if adding/deleting is even applicable for that particular business object.

    How would your model me changed to cope with a business object that requires multiple Linq DAOs? I am currently leaning towards having my business objects implement IInsertable and IDeletable interfaces and calling InsertOnPersist() and DeleteOnPersist() on the business object itself instead of the business context. This seems wrong though as it’s putting DAL logic into the business objects 😦

    What do you think?

    • Randolph Cabral said

      @Vassago – Excellent question. One way to accomplish this is to modify the ‘LinqEntity’ property to be a List and rename it to ‘LinqEntities’. Then modify the persist methods in the context to loop over the objects in the List. See below code snip.


      public void AddOnPersistState(object entity)
      {
      var linqEntities = entity.GetType().GetProperty(“LinqEntities”,
      System.Reflection.BindingFlags.Instance
      | System.Reflection.BindingFlags.NonPublic
      | System.Reflection.BindingFlags.Public
      | System.Reflection.BindingFlags.FlattenHierarchy).GetValue(entity, null);

      foreach(var entity in linqEntities)
      {
      var entityType = entity.GetType();
      DataContext.GetTable(entity).InsertOnSubmit(entity);
      }
      }

      Clearly, there’s work to be done in the entity to resolve the correct entity from the list, but that can easily be resolved with some type checking.

      Hope this helps.
      -Randy

  12. Kamran Shahid said

    Can you also depict how to use Storedprocedure layer with it.

Sorry, the comment form is closed at this time.

 
%d bloggers like this: