Hot Off the Presses: SOA Data Access!

I wrote an article for the December issue of MSDN Magazine, which has just hit the streets:

The title is: Flexible Data Access With LINQ To SQL And The Entity Framework, and you can download the accompanying code here.

The purpose of the article is to provide a practical approach for developing real-world data-driven applications with either LINQ to SQL or the Entity Framework that use a layered, n-tier architecture. Developers currently face daunting challenges when it comes to developing these types of applications, even though it is these types of apps are the bread and butter of what most developers do on a day-to-day basis. The main challenge seems to be making the client application completely persistent ignorant by shielding it from any traces of persistence technology and using POCO objects that are not required to derive from a base class or implement a particular interface. My approach compromises on this principle somewhat by having DTO’s that implement an interface called ITrackable that has a single property containing change state. Nevertheless, this represents just a small bit of metadata attached to each DTO as it travels from the client to the service, where it is interpreted by a Data Access Layer and persisted to a data store.

The one requirement for my approach to work is that both client and service must agree on the data contract, which includes change state information, but this is par for the course for most service-oriented applications and plays well with non-Microsoft platforms. Another approach would be for a standards body of some sort to issue a universally accepted wire format for passing change-tracking data. Microsoft implemented diffgrams for this purpose some years ago using ADO.NET Datasets, but the standard never gained industry acceptance. What we really need is a new standard, then the problem of tracking changes on the client and persisting them on the service become manageable. This is the direction where I think the Entity Framework, which is considering n-tier design options, team need to be headed.

About Tony Sneed

Sr. Software Solutions Architect, Hilti Global Application Software
This entry was posted in Technical. Bookmark the permalink.

15 Responses to Hot Off the Presses: SOA Data Access!

  1. Bob Bedell says:

    Have been playing with your code, and finally got it going. Very cool! Problem I had was that the Orders and OrderDetails tables in the default installation of Northwind don’t have a timestamp column named ‘Update’. So the GetCustomerOrders bit kept crashing. I added the:

    [ServiceBehavior(IncludeExceptionDetailInFaults =true)]

    attribute to the OrderService class and it told me that the ‘Update’ column was invalid. So I added ‘Update’ timestamp columns to the Orders and OrderDetails tables, and all is well.

    Just wanted to give any other readers experiencing the same dilemna the heads-up.

    Thanks again. I’m starting to get a feel fo this WCFWPFSOALINQADOENTITY stuff.

    Bob Bedell

  2. Tony says:

    Thanks Bob,

    I’m glad you pointed is out. I thought I had included instructions to add this column in the ReadMe file for the sample app, but alas it looks like I did not. I have now updated the ReadMe file to state that you must add a timestamp column called ‘Updated’ to both [Orders] and [Order Details] tables in the Northwind database. This is necessary for optimistic concurrency to work correctly.

    I’m glad you found the article interesting and useful, and I hope it will provide a springboard for developers to start building real-world n-tier apps using LINQ to SQL and the Entity Framework. Cheers,


  3. The article was awesome, thank you Tony

  4. Pingback: Tony and Zuzana’s World » 2008 Year in Review

  5. Mark says:

    Thanks for the great article! We very much like your SOA approach to EF. While reviewing the sample code I was puzzled why you appear to have deleted the SQL table relationship between Orders and Customers table. I have managed to get your sample app working but have been unable to recreate a similiar app using the same database and tables. The Entity Framework EDM wizard automatically creates a relationship between these tables whereas the EDM accompanying your sample code does not contain this relationship which has a significant impact throughout your sample code. Could you please explain why you took this approach rather than using the wizard generated EF EDM.

  6. Tony says:

    Hello Mark,

    Ah yes, you’ve discovered the missing relationship! What I did was to run the EF wizard but deselect the Customers table. Then right-click on the diagram, select “Update Model from Database” and select the Customers table, removing the association between Customer and Order in the diagram. The end result is that the Order entity contains the CustomerID foreign key, making it much easier to update the Customer of an Order simply by setting the CustomerID property.

    The reason I went to all that trouble was that, at the time, I did not know how to update an Order’s Customer property in a disconnected fashion. The proper way to go about it is to set the EntityKey property of the Order’s CustomerReference property, like so:

    order.CustomerReference.EntityKey = new EntityKey
    (“NorthwindEntities.CustomerSet”, “CustomerID”, od.CustomerID);

    I will shortly post a new version of the article sample app which contains this and other improvements.

  7. Mark says:

    Thanks for the explanation & thanks once again for the excellent article – will watch out for the new version.
    Kind Regds

  8. Bob Bedell says:

    Hi again Tony,

    I LOVE this app. Here it is a month latter and I’m still playing with it in a variety of incarnations pretty much daily (along with the new CSLA 3.6). Thank you for making it so comprehensive, i.e. all the CRUD operations, 1:M and M:M relationships, etc. Most “sample” apps don’t go to the lengths you have. Thanks for covering ALL the bases for once in a digestable app.

    Wanted to point out one critical typo in the EntityDataProvider’s UpdateOrder method. When you build your list of new OrderDetails to be inserted, you have:

    List insertedDetails =
    (from od in order.OrderDetails
    where od.TrackingState == TrackingInfo.Updated
    select od).ToList();

    TrackingInfo.Updated should be TrackingInfo.Created (As it stands you’ll get duplicate primary key violations).

    Sure would be nice to be able to toggle off PropertyChanged and ListChanged notifications when simply fetching data, wouldn’t it. Nice thing about the WinForms version is that BindingList has a RaiseListChangedEvents property that at least allows you to toggle off list changed events. I havn’t stumbled across a WPF counterpart yet though. Fetching is SLOW, and I suspect supressing all the event notifications would be a major performance enhancement.

    Thanks again. Back at it tomorrow probably.


  9. Bob Bedell says:

    I should say that the initial fetch is SLOW. Subsequent fetches are considerably faster. Is some sort of initial request compilation going on in the way ASP.NET pages are compiled on first request? Looking forward to the revised version, too!!

  10. Bob Bedell says:

    Then again, as far as supressing event notifications goes, it occurs to me that you do provide a Tracking property. I’ll play around a bit with toggling it at some point in the fetch sequence. Thats probably how RaiseListChangedEvents change is implemented anyway.

  11. Tony says:

    Hello Bob,

    Thanks for finding that typo … don’t know how we missed that one! 🙂

    The way I’ve configured the service reference in each client is to use the change tracking collection for ALL lists. If you have classes that you’re not updating (such as Customer and Product) you can configure the service reference to return List instead and then convert it to the ChangeTrackingCollection (or ChangeTrackngList) only for the items you want to track changes on.

    In terms of performance, the initial slow fetch is most likely due to WCF setting up the communication channel. The performance impact of event notifications is negligibl because .NET events are fired only if there are one or more handlers.

    I’m happy the sample app is helpful to folks wanting to use LINQ in n-tier apps. I have learned quite a bit about Entity Framework since writing the article, and I plan to incorporate those lessons in a future version of the sample app (coming in the next couple of weeks!).


  12. Bob Bedell says:

    Hi Tony,

    was revisitng your app, doing some work with WCF. Where would you do this?

    “The proper way to go about it is to set the EntityKey property of the Order’s CustomerReference property, like so:

    order.CustomerReference.EntityKey = new EntityKey
    (”NorthwindEntities.CustomerSet”, “CustomerID”, od.CustomerID);”

    Appreciate it if you have a moment to respond, though I certainly also understand if its been a while since you revisited this.


  13. Tony says:

    Sorry that I neglected t update the article code :(, but I’ve been busy wrapping my arms around EF 4.0, and seeing as it hopefully won’t be too long before its release, I figured it would be better to write an article on doing n-tier with EF 4.0, which supports POCO’s out of the box with self-tracking entities (see my DevelopMentor newsletter article at

    That said, you’ll be glad to know I wrote a sample app in EF 3.5 that does exactly what you’re asking about:
    In here you’ll see that you set the Reference propery’s EntityKey in the Create and Update methods of your service. This is necessary in order to relate a new or updated Product with a particular category. As you can see, it is quite clumsy and will no longer be necessary in EF 4.0. But that’s how you do it for now.

  14. Bob Bedell says:

    Hi Tony,

    I went off on a WCF tangent, but am back on EF tonight and will spend some time with you code ASAP.

    Thanks for the resources. Looking forward to the POCO article.


  15. Pingback: Trackable Entities: N-Tier Support for Entity Framework | Tony Sneed's Blog

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.