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.
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???
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
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
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!
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
Warren said
I was wondering if you have a zip of this solution in Visual Studio 2008?
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!
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
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
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.
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