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!