Tuesday, August 28, 2007

LINQ To SQL ≠ N-Tier Architecture?

Ok, LINQ seems pretty wonderful, and I can see the utility of it - and with regards to SharePoint we'll probably end up using it when Microsoft comes out with an official LINQ to SharePoint, or the community does. Heck, it may even replace (or at least abstract) the dreaded CAML code....

With LINQ, you get:

  • Design-time type checking of data queries
  • Code syntax that is consistent across objects, XML, and relational databases (and, really, anything else)
  • Queries are integrated right into the .NET code frameworks
  • Full IntelliSense support when coding what used to be a bunch of string values

All good stuff. But what I don't understand is how it fits with traditional N-Tier architecture. Is this more of the same old "you can write 70% less code if you don't mind your web page directly accessing your database" trend, or can we actually use this in a properly architected application?

I've been following along on Scott Guthrie's blog; he has a detailed series of posts about using LINQ, but he hasn't yet addressed where LINQ might fit in the N-Tier architecture.

Rick Strahl is already blogging about some of the limitations when trying to combine LINQ in the middle tier with POCO (Plain Old C# Objects), but it isn't clear if this is a beta 2 problem or a design limitation. Here's his conclusion:

The more I look at LINQ the more I'm coming to the conclusion that using LINQ in a middle tier - especially in a generic business object architecture - is not going to work well. There are many little problem issues that when all added up point at more problems being created than solved by the entity generation and easy CRUD layer.

Manning Publishing's upcoming LINQ In Action book has an early access program that lets you download PDFs and view chapters as the authors write them (the ink stains on the PDF are still wet!). It's a great read. Incidentally they're the folks behind LINQ to Amazon, which was one of the earliest LINQ extensions that showed up on the 'net.

Chapter 14 of this book talks about how to integrate LINQ into applications. It makes the point that LINQ "out of the box" is more of a Rapid Application Development tool - which goes back to the "70% less code" approach Microsoft is so fond of. However, the book suggests specific concerns that should be noted when trying to use LINQ in a DAL in the traditional way.

LINQ defers calls to the data source until the query is enumerated over, so if you're returning entities or queries to a business layer (in the format IQueryable<Whatever>, you might be introducing a dependency (your business layer is actually calling a LINQ to SQL query at runtime). You can turn off lazy loading by setting the DataContext's DeferredLoadingEnabled property to false, and also by explicitly loading an entity's child records before returning the entity from the DAL.

So, if I understand correctly:

  • LINQ to XML or XSD doesn't seem to foil N-Tier architecture, since LINQ is essentially a tactical solution to make it easier to read or write XML, and doesn't cross any layer boundaries.
  • LINQ to Objects is an in-memory LINQ technique, that again shouldn't cross any boundaries. Your method signatures will consume and provide POCO, and you use LINQ to iterate over them completely within the Presentation or Business layer. So far, so good.
  • The real issue seems to be LINQ to SQL...and issues can be avoided if the LINQ to SQL code is confined entirely to the DAL, lazy loading is turned off, and disconnected entities and lists are passed back out of the DAL to the Business tier. But if you do this, the only apparent advantages of LINQ to SQL over any other ADO.NET data access method is the consistency of coding and the design-time safety of the strongly typed queries (which you can already get with Strongly Typed Datasets!)

Please, can anybody help me understand what an appropriate N-Tier architecture might look like with LINQ To SQL?


  1. After goofing around with Haloscan's comments I somehow managed to hide Scott Guthrie's reply to this, so I'm repasting/reposting them:

    "LINQ to SQL data model entities don't maintain any connection state - which means you can pass them across tiers or logical business classes. You can also serialize/deserialize them across a web-service or other remoting technology.

    I'll be covering how to-do this (and then re-attach them to a database context) in an upcoming blog post in my series. I think you'll find this provides a pretty clean and flexible way to implement a N-tier model.

    Hope this helps,

    scottgu | Homepage | 08.28.07 - 5:06 pm "

    Thanks for the response, Scott, I'm looking forward to reading your blog posting on this topic.

  2. After running into this problem too, the best way we could find of dealing with this for now, is this excellent post by Keith Craig:


  3. I'm struggling with this problem myself. I riffed on the nice attack in codeproject for a good solution. I didn't like generating the entire database xml with sqlmetal, so I placed a dbml designer that can be changed in the data transfer layer. I did separate the DataContext object and place it in the DataLayer so the presentation layer can't party with the DataContext class and create ad hoc sql. Entities are used to transfer data between layers, but not for constructing queries, that is reserved for calls to stored procedures. Note that you can customize EntityGetAll() stored procs to return the Entity by dropping the stored proc on the entity in the dbml designer. Queries don't get changed outside the datalayer. Only stored procedures are utilized (CRUD) to return data transfer objects generated by the sql designer. The business objects set the contracts for the Data Access Objects which are then implemented in the DataLayer.

  4. Good post. I've been googling for a solid solution to this very question and unfortunately (after many hours) haven't found one. I notice this post is a few months old, has Scott posted his response yet?

  5. I haven't seen a post on this yet on Scott Guthrie's blog, although I might have missed it. I think we'll all end up figuring it out (as Jeff, Keith, Rick Strahl, and many others are experimenting) but it would be nice to have some Microsoft guidance on how they foresee this being used in n-tier apps.

  6. In light of this discussion, it might be good to revisit the definition, advantages, and purpose of N-tier architectures.

    If we're talking logical tiers, not physical, then I don't see why you can't use LINQ and still have plenty of tiers (if you want them).

  7. Hi Ben, I guess my worry is that with LINQ I'll inadvertantly introduce some tight coupling or dependencies I hadn't expected to. You raise an interesting point: what do you think a multi-tiered logical architecture might look like using LINQ? Does it look any different from a normal Presentation/Business Layer/Data Access Layer structure? Or am I worrying too much about something that isn't an issue?

  8. I have written an article related to this on codeproject at

    Please have a look and post your valuable comments / suggestions there.

  9. This link no longer functions

  10. Hi Anonymous - if you mean this CodeProject link:http://www.codeproject.com/KB/dotnet/NorthwindNTierBlueprint.aspx I guess it was deleted by the author.

  11. I have not been able to design a suitable n-tier architecture using LINQ and I am not really sure about using LINQ any further. Maybe we have to wait some more time for this new style of functioning to be stabilized for enterprise development.

  12. Hi Sarp, this is very interesting - what sort of problems did you run into?

  13. Hi All,

    Currently, I am working on an N-Tier Application and evaluating Linq-to-SQL to write the DAO Layer. After a thorough read from different blogs and books, I have decided to take out the Linq-to-SQL and use Microsoft Enterprise Library for data access.

    However I am also re-considering usage of linq-to-sql in the following way.

    1. Restrict Linq-to-SQL generated classes only to DAL layer
    2. Define POCO (plain old clr objects) to transfer the data from data access layer to
    business layer and inturn to presentation layer via WCF services.

    Here is my application data flow.

    Presentation Layer  Controller WCF Proxy  WCF Services  Business layer  Data Access Layer

    What I would like to know is.

    1. I need to convert linq-to-sql objects to POCO objects before returning from DAL Layer. I feel this is extra cost. Is there any other alternative way?
    2. Am I using linq correctly?
    3. Is there any solution to this problem

    Your help is appreciated.


  14. Hi Hari, converting to POCO is a slight overhead. To maintain your separation of layers I guess you'll have to create a wrapper class in the DAL for each business entity you will be returning data to. However you protect yourself from later dependencies that LINQ to SQL might introduce in other layers. I haven't seen any other way so far.

    LINQ to SQL might still be useful to you for its design time type safety and consistency (especially if you are using LINQ elsewhere, such as for XML or in memory), and if you are only using SQL Server and don't plan to switch.

    If you are already using Enterprise Library for other reasons, such as for caching, logging, and exception handling, I would wonder what benefits LINQ to SQL is really giving you that the Enterprise Library Data Access block wouldn't.

    However, just to play the Devil's Advocate - maybe you don't need to worry so much about LINQ to SQL adding a dependency in your business layer. It looks from your architecture like the real split is between your Presentation / Controller tiers and your Business / DAL tiers. Maybe you can tolerate having LINQ to SQL possibly blurring the line between the BL and DAL, in return for its ease of use / productivity gains? It isn't 100% proper, but maybe it's a small thing to risk?

  15. Thank you very much Nick. Appreciate it.

    I am still evaluating this. We have not yet started the development. I considered DAAB based on my previous experience and in fact team experience too.

    Currently, I am planning to implement one module which will have its DAL written in LINQ-to-SQL. Based on the results I will try to stick with either ADO.NET or LINQ-To-SQL. After lot of reading and developing same application using Northwind database (results are very positive) , it looks little scary. Is’n it?

    However, I do have the following other questions. Again your help is highly appreciated.

    1. Datacontext instance should be created for unit of work, in my case request based, for the obvious reason to avoid instantiation time. So I need to take care of this while designing the datacontext itself. Is it too much maintainability to create different datacontexts which my have pointing to same tables? (Currently I am moving every datacontext to a separate namespace to avoid conflicts)


  16. Hi Hari, I can't advise on this I'm afraid as I haven't tried multiple DataContexts yet. In my own framework I am using pure ADO.NET for the moment (only because I do not want to introduce any dependencies). However I am about to try LINQ to SQL in one of my providers, because my provider layer returns POCO so I am doing that extra step anyway. If my framework were a commercial product on a deadline I would probably be using EntLib for the moment, only because I am most comfortable with it.

  17. Thanks for your response Nick. Could you please suugest me a better way to convert linq entities to POCO's before returning from Data Access layer? How are you doing?


  18. Hi Hari, I can't suggest a better way - after some thought I am leaving LINQ to SQL out of my framework for the moment. I feel it is not adding enough functionality to some pretty standard DAL calls for me to justify it. I am quite happily using LINQ in other layers - for instance a lot of LINQ to XML in the presentation layer to make web feeds and things like that.

  19. Here's an article on using Reflection to create runtime objects for your DataTransferObjects.


  20. Thanks Anonymous - I've read the article and it is quite interesting. I don't have any personal experience writing code directly in IL but that might be a good experience for this kind of dynamic DAL requirement. Have you used this before in any of your projects?

  21. Has anyone heard back from Scott regarding the posts? I hope Microsoft hurry's with a few blogs on this n-tier confusion!

  22. I think we need to urge ScottGu to complete his LINQ-to-SQL series as promised!

  23. We have just released Signum Framework, an open source framework with a fully expressive Linq Provider for building N-Tier applications.

    Our model is clean as simple, no XML needed, allows serialization and has validation integrated on it, but follows a entities-first approach, meaning that you have to model the entities first (as C# classes) and then the DB is generated for you, not the other way around.

    Here's an example of the architecture of a clien-server application:

    FAQ about the ORM:

    And some cool videos explaining how easy is to use it:

  24. Well explained. Basics of LINQ Architecture can be found here http://www.techbubbles.com/net-framework/linq-architecture/

  25. Thanks Bandarupalli, good article.

  26. Hi Nick,

    Actually I think you ARE worrying too much about this and the n-tiered app is structured exactly how you think it is. Front-Business-Data, passing the Linq/Model Objects back and forth through the tiers.

    Ive successfully created an n-tiered linq-sql site, with full review/comment system, and much more. The membership DB is really the only managed code Im using, and I could (fairly) easily uncouple that if I felt it neccessary.

  27. Fair enough Liam, I tend to worry about this stuff too much. I suppose at the end of the day you have to be at least slightly coupled to *something*. I'm glad to hear you've been able to use Linq2SQL in an n-tier site. I imagine the same scenario would apply for Entity Framework now that it's replacing Linq 2 SQL.

  28. Here is a link that provides good information about LINQ to SQL best practices



Note: only a member of this blog may post a comment.