LINQ to SQL in multi layered + service apps
In a previous post, I was explaining how surprised I was when I discovered that you lose LINQ to SQL change tracking features when working in a multi layered application or when you expose your business logic thru a service layer. Thanks to everyone (especially Barry Gervin, Rocky Lhotka, Julie Lerman and Rick Strahl) who pointed me to articles, blog posts and code samples, I was able to make sense of all of this.
Let’s take a look back at my rant and see how I can address each of these points. BTW, this is by no means the only way to achieve the goal of using LINQ to SQL in a multi layered application. It’s just simple and easy. Feel free to comment and share your findings and thoughts.
1-The presentation layer must reference the DAL directly because this is where the data mapping classes are located. Yuck!
Referencing the DAL objects from the presentation layer is bad because you’re creating a strong bond between these layers that are supposed to be decoupled. The rule states that the presentation layer must not be aware of the data access layer. What you really need to do is create a transport object layer that will be referenced by the BAL and the DAL. These objects are just plain POCOs (Plain Old CLR Objects) that will cross all the layers.
public class Customer
{
public string CustomerID { get; set; }
public string CompanyName { get; set; }
public string ContactName { get; set; }
public string City { get; set; }
}
In your LINQ query, just map the data directly back to your POCO. In this example, the data is mapped back to the Customer POCO.
public static List<TransportObjects.Northwind.Customer> SelectAllCustomers()
{
using (LINQ.NorthwindDataClassesDataContext ctx = new LINQ.NorthwindDataClassesDataContext())
{
var q = from c in ctx.Customers
select new TransportObjects.Northwind.Customer {CustomerID = c.CustomerID, ContactName = c.ContactName, City = c.City, CompanyName = c.CompanyName };
return q.ToList< TransportObjects.Northwind.Customer>();
}
}
2-By returning POCOs to the presentation layer instead you lose all the change tracking stuff provided by LINQ to SQL. Yuck!
One of the LINQ to SQL benefits is change tracking. Beside the actual data that is mapped to the objects, LINQ to SQL keeps a copy of that data so when you call the SubmitChanges method of the DataContext object, LINQ to SQL can figure out the objects that changed and can apply these changes (CRUD) back to the database. And yes, by having a transport objet, you are losing the change tracking benefit but it’s not the end of the world.
3-Exposing the BAL layer as a set of WCF services you lose the change tracking stuff. Yuck!
Change tracking IS state and state is bad in service oriented apps because by definition, they are supposed to be stateless. OK so how I’m supposed to return my POCOs back to the presentation layer using a WCF service layer? Simple, use the [DataContract] and [DataMember] attributes in your POCOs. Now they can cross the WCF service layer.
[DataContract]
public class Customer
{
[DataMember]
public string CustomerID { get; set; }
[DataMember]
public string CompanyName { get; set; }
[DataMember]
public string ContactName { get; set; }
[DataMember]
public string City { get; set; }
}
4-And what if I want to data bind my grid? Using the designer, I need to point to the DataClasses sitting in the DAL. Yuck!
No need to reference the LINQ classes to generate the correct bindings. After adding a service reference in your presentation layer, connect the BindingSource object to the WCF proxy classes then when you receive an array of POCOs from the WCF service layer, you only need to bind it to your grid.
5-OK, I'll lose the change tracking stuff. Now I'll have to reload each record before saving it? Yuck!
Yep, that’s one way to do it but you can apply business rules while doing that. You can of course do all of this in store procs.
That’s it! Not bad at all, right? Sure you lose the change tracking feature but you get the benefit of using LINQ to SQL in your DAL.
Drawbacks?
One drawback is when you have complex POCOs like the Order and OrderDetail ones with the Order object having a property of type OrderDetail. I haven’t found a way to return the Order object correctly stuffed with the OrderDetail data directly from the LINQ query. What I do is return an anonymous type then loop to build my POCOs, the drawback being that two object creation phases are needed: one for the anonymous types and one for the POCOs. I wouldn’t return thousands of records this way! I would simply use a store proc or plain old ADO.NET to retrieve the data, and then build the POCOs. If somebody has a clever way to do this, please post a comment!
[DataContract]
public class Order
{
[DataMember]
public int OrderID { get; set; }
[DataMember]
public string CustomerID { get; set; }
[DataMember]
public DateTime? OrderDate { get; set; }
[DataMember]
public DateTime? ShippedDate { get; set; }
[DataMember]
public string ShipCity { get; set; }
[DataMember]
public OrderDetail[] Detail { get; set; }
}
[DataContract]
public class OrderDetail
{
[DataMember]
public int OrderID { get; set; }
[DataMember]
public int ProductID { get; set; }
[DataMember]
public decimal UnitPrice { get; set; }
[DataMember]
public int Quantity { get; set; }
}
public static TransportObjects.Northwind.Order[] SelectCustomerOrders(string id)
{
using (LINQ.NorthwindDataClassesDataContext ctx = new LINQ.NorthwindDataClassesDataContext())
{
List< TransportObjects.Northwind.Order> ordersList = new List<TransportObjects.Northwind.Order>();
var q = from o in ctx.Orders
where o.CustomerID == id
select new { Detail = o.Order_Details, CustomerID = o.CustomerID, OrderDate = o.OrderDate, OrderID = o.OrderID, ShippedDate = o.ShippedDate, ShipCity = o.ShipCity };
foreach (var order in q)
{
TransportObjects.Northwind.Order tempOrder = new TransportObjects.Northwind.Order();
tempOrder.CustomerID = order.CustomerID;
tempOrder.OrderDate = order.OrderDate;
List<TransportObjects.Northwind.OrderDetail> ordersDetailList = new List< TransportObjects.Northwind.OrderDetail>();
foreach (var orderDetail in order.Detail)
{
TransportObjects.Northwind.OrderDetail tempOrderDetail = new TransportObjects.Northwind.OrderDetail();
tempOrderDetail.ProductID = orderDetail.ProductID;
ordersDetailList.Add(tempOrderDetail);
}
tempOrder.Detail = ordersDetailList.ToArray<TransportObjects.Northwind.OrderDetail>();
ordersList.Add(tempOrder);
}
return ordersList.ToArray<TransportObjects.Northwind.Order>();
}
}
Hope this helps!