Do you really need a data access layer with LINQ?
Lately I’ve been giving a lot of thought to using LINQ to access my database instead of using NHibernate. I’ve been a little confused as to how LINQ would work in a data access layer, but I’m starting to think it makes sense as a replacement to the data access layer.
This article was inspired by the Google App Engine. Using the Django framework for database access is stupidly simple. They’re able to focus on getting something done, which is good enough in a lot of cases. Not every project has to be an N-Tier enterprise application.
Primarily, I write eCommerce websites. Consider the following diagram, which gives you a rough idea of how I’ve traditionally structured my applications:
Notice that a lot of code is being tested. The sweet side of me likes unit tests, but the wheat part of me is telling me to simplify code when possible to minimize the need for unit tests. I’ve traditionally wanted a lot of unit tests at the data access layer because it tends to be a source of a lot of issues due to it’s complexity. It’s complexity is a result of impedance mismatch.
Code that is a good candidate for unit testing:
- Logic/utility/static functions
- Code that will be used in a lot of places, and has a well defined contract
- Code that needs the highest levels of reliability
Now consider what happens when we use LINQ and the ADO.NET Entity Framework. The impendence mismatch has been minimized because the entity framework has automatically written our model code to match the database. It has also been my experience that the queries that are not trivial are usually not re-used. In other words, query complexity is inversely proportional to the frequency of re-use.
The end result is that our code has been greatly simplified. Now our unit tests can focus on the business logic. We’ll actually have more time for unit testing, which should lead to more stable code where it is needed most.
Sure, our UI will contain what are basically database queries. The fact is that many pages simply need a specific set of data that will populate a drop down list for example. I know that it might give people a bad feeling (as it does to myself), but I think it’s something we need to get over. If you want to test the page, in most cases you can just run it, and see that it’s working. In conjunction with a good web testing tool, it would be easy to have high levels of reliability.
Obviously there are a lot of places where this is a bad idea. I’m certainly not condoning a complete lack of a data access layer for every application. I’m applying the 80/20 rule. Places it might not make sense, or where it’s gray:
-
A logic class that needs to make frequent, repeated database calls.
-
An application that requires a certain level of testability.
-
An application that contains some extremely common queries that may or may not be trivial.
Will said,
Wrote on May 7, 2008 @ 7:48 am
I’m going to make some popcorn, brb.
superjason said,
Wrote on May 7, 2008 @ 10:09 am
Will, are you back yet? If so, did you bring enough for everyone?
skain said,
Wrote on May 7, 2008 @ 11:59 am
Your title is very misleading. You’re really talking about using the Entity Framework as a data access layer. And that’s unsurprising since that’s exactly what it is. LINQ by itself CANNOT replace a data access layer. It simply provides a nice foundation for one. Whether you use Entity Framework or roll your own.
I vote fail on this article.
Peter said,
Wrote on May 7, 2008 @ 12:08 pm
Is this trolling? I don’t think “unit test” means the same thing to you as it means to proponents of unit testing. And if it does, I don’t think your “new improved 3 boxes instead of 4″ architecture makes for easier unit testing, instead it will be much more difficult.
I work with SharePoint, so I can appreciate your “let’s not make a DAL for every app ever” approach. I can appreciate that.
But are you really saying “mushing database access code into the UI improves unit testing?” Because, like it or not, that’s how this post reads.
I’ll try to end on a positive note: change “unit test” to “automated test” and I think this post will read a great deal better.
superjason said,
Wrote on May 7, 2008 @ 12:16 pm
Skain, true, it’s a little bit of LINQ and Entity Framework. I tend to group them together, because I’m either going to use both, or none.
Peter, I do mean automated unit testing with a tool such as NUnit, and I’m not suggesting less unit testing in general.
Chad Myers said,
Wrote on May 7, 2008 @ 12:27 pm
I’m curious how you define ‘data access layer’. Could you expand on that a little?
The point of NHibernate is to reduce the impedance mismatch (or at least hide it inside of NHibernate), allowing you to have a rich domain model that is not tied to relational database thinking and a relational database model that is not tied to object oriented thinking.
This is supposed to make testing easier because you don’t have to test NHibernate other than to do some integration tests to make sure the mappings are correct, but most of this can be automated.
Whereas Linq2Sql and the ADO.NET EF bleed the relational model as much as possible into your application and make it considerably difficult to have a rich domain model. What you end up with is a relational model codegen’d into your object oriented application and you’ve now pulled the impedance mismatch up into your domain logic and this is almost always a Bad Thing(TM).
Now you necessarily cannot test your domain entity logic without having to drag the ADO.NET EF into it and all its configuration and, quite possibly, having to have a database connection which will KILL you with friction when trying to do test-driven design and development. Fast feedback and low friction are virtues in unit testing, and adding all the overhead of having to muck with relational databases will kill your productivity quickly.
So I guess I’m thinking that maybe you’re approaching your Data Access Layer and Business Logic Layer design from a perspective that doesn’t facilitate easy testing and that’s where your pain is coming from.
I’m afraid that adding linq2sql or ADO.NET EF will only further plunge you into testing problems as your ability to lift out bits of code and test them independently becomes more and more impossible due to how much linq2sql and EF bind you to the database.
Have you heard of Eric Evans’ Domain-driven Design (DDD) book? I highly recommend it as it goes about helping you to build a rich domain model and treat persistence as a separate concern rather than building stratospheric ‘layers’ which dramatically complicate de-coupled design, reuse, and testing.
Ben Scheirman said,
Wrote on May 7, 2008 @ 1:01 pm
get ‘em Chad!
Seriously, why don’t we put one of the more complex pieces of an application and NOT write tests around it.
That doesn’t make sense at all.
You quoted Django, and I can speak a bit to rails. They have simplified data access but it doesn’t limit its testability because they are implemented with highly flexible languages.
LINQ is great, but the underlying data source still has loads of complexity. Unit testing this is crucial.
Rob Conery said,
Wrote on May 7, 2008 @ 1:28 pm
@Chad: w00t an all-caps “KILL”! Consider yourself lucky SuperJ - I’ve been comment-lashed worse :).
This one thing you wrote got me:
>>The impendence mismatch has been minimized because the entity framework has automatically written our model code to match the database.<<<
It’s actually the reverse - it’s optimized :). You don’t want relational structures in your app :).
Anyway - the primary thing anyone needs to focus on is that a successful app NEVER stays small. If you’re writing a quick proto - go for it. Use SQLDataSource :).
The driver of this idea is code-reduction and allowing someone else to figure out WTF you’re trying to do. One thing I think everyone agrees on is not putting DB access code in your code behind :).
ActiveRecord is a nice, simple, lightweight pattern that might do what you’re after though.
Paco said,
Wrote on May 7, 2008 @ 2:18 pm
The argument between choosing for a 2 or 3 teer architecture should not be “what framework am I using now?” (linq in this case), but “do I want to implement the some other framework in the future easily?”.
When your business objects are all coupled to the data-access layer, you have to rewrite a lot of code when you want to use the futuristic next-gen ORM that is much better then linq five years later.
I don’t understand what unit-testing has to do with this point. Both 2 and 3 teer architectures are testable. You will probably need some more mocking with 2-teer. UI is also testable.
superjason said,
Wrote on May 7, 2008 @ 2:33 pm
Maybe I’m just not understanding, but consider the following query:
var orders = from order in db.Orders
where order.Type == 5
select order;
How can it possibly be beneficial to move that into a data access layer?
I probably should have described the scenario a little better. This is an e-commerce site that has dozens of pages, and they all work quite differently. I’ve bought into the whole layered approach, defining a data model, using NHibernate, etc. I live it every day. I get frustrated when a page with 4 drop down lists takes 5-10 hours because I have to do this:
- Create my NHibernate queries in my data access layer
- Write unit tests for those queries
- Call those DAL queries from the UI (with the hundreds of other queries in there)
- Debug it if it doesn’t work
Then, I have to do it all again to save it. There is SO much code I now have to maintain, and I don’t see how it’s worth it.
How can replacing 20 lines of code with 3 lines of LINQ not be beneficial? I can write the 3 lines of code in 30 seconds, and test it in another 30. That gives me time to make my DAL better for the times when I actually need it.
As I mentioned, this probably doesn’t apply to non-trivial examples.
The point is that some people seem to be too stuck in their old ways of thinking. My background is in enterprise applications, but the simplicity of some of the RoR and DJango code made me jealous, and opened my eyes.
When you have a hammer, everything looks like a nail.
Paco said,
Wrote on May 7, 2008 @ 4:40 pm
The main point is that the structure of your business objects is not the same as the structure of your database in 90% of the situations. And if the structure is the same now, you probably don’t want to change the UI code after every minor change in the database. Of course you can solve this problem by writing the Linq XML or [attributes] yourself instead of using the designer, but then you are back to the same difficulties as when you used NHibernate.
The main point about Linq is the delayed execution.
Have you seen the screencast of Rob Conery?
He has a nice way to seperate those things and also uses an ecomerce site as example.
I have also tried Rails. A dissapoint was I have to write 3 times more unit test then in C#, because a compiler already checks a lot of stuff.
superjason said,
Wrote on May 7, 2008 @ 4:47 pm
Our business objects match our database. It was written from the ground up that way. We use OR-mappers exclusively.
Code changes will trigger compile errors (when using Linq), and we’ll know where to make the changes.
I’ll check out the Linq screencast you mentioned. I would like to see a good ecommerce example.
logicalmind said,
Wrote on May 7, 2008 @ 5:17 pm
It all depends on project size. If you are a single developer writing all the code and all the pages then of course it’s going to be easiest for you to write the least amount of code. Once you introduce more developers to the project that starts to fall apart.
Are you suggesting that whoever is writing a page determine what linq queries they use and just write those in their page? Eventually you’ll come to the realization that common linq queries belong in a common class accessed by all pages. Separate people can write these separate layers as would be the case in projects with more than one developer. Once you do this you’re back to where you started except you’ve replaced nhibernate data access layer with linq for sql data access layer.
Chad Myers said,
Wrote on May 7, 2008 @ 6:57 pm
@superjason: Stop insulting people (re: hammer/nail) and try to consider why the ‘db.Orders’ might be a problem if you ever wanted to use that code against something other than a database (i.e. test data). If you’re happy with tightly binding all your code to your database and sure that you’ll never re-use that code for anything else, then why do any layers and just have one solid layer and put everything in the code behind or directly in the view?
superjason said,
Wrote on May 7, 2008 @ 8:35 pm
“Eventually you’ll come to the realization that common linq queries belong in a common class accessed by all pages” - I agree, but what I’ve seen, at least on the last site, common queries are more rare than I would like. Each page usually has different requirements. When I’m writing code, I’m very conscious of rewriting the same functionality. When that happens, of course I’m going to try to re-use my code.
Chad, I agree. I think I’ve unsuccessfully conveyed the situation that I’ve run into. I’ve tried to apply my background in large application development, to individual pages with their own unique functionality. I don’t regret insulting people, since I was including myself in that group. I’m simply suggesting that a hard and fast rule of trying desperately to separate out DAL logic might not reap the benefits we would expect.
In the site I just created, I had all the layers, and they were completely separated. I was able to test any layer regardless of the other layers. The site is reliable thanks to 700 unit tests, but at the expense of having 10x as much code to maintain. Am I really better off? I’m not so sure!
Honestly, I would have rather saved all of that time, and spent it on the critical pages. For example, I would have liked to had a full MVC unit testable checkout process.
teedyay said,
Wrote on May 8, 2008 @ 4:49 am
Mmm, thanks for the popcorn, Will!
There’s some quite strong opinions on this page, but I can see where you’re coming from: LINQ makes it so damn easy to write queries in the code behind your page, but something makes me feel a little uncomfortable doing that.
Just lately I’ve been trying to put most (all?) of the DB-accessing LINQ queries into the non-generated half of my DataContext class, just so it’s all together in one place, giving me my data access layer.
This is fine for encapsulation, but feels a bit like over-engineering when it’s a simple query I only use on one page.
LINQ’s DataContext has been quite a paradigm shift for me in terms of how I use ORM. For me at least, the jury’s still out on issues like this.
Dew Drop - May 8, 2008 | Alvin Ashcraft's Morning Dew said,
Wrote on May 8, 2008 @ 8:16 am
[...] Do You Really Need a Data Access Layer with LINQ? (Jason Young) [...]
Pay Day Loans said,
Wrote on July 8, 2008 @ 2:03 am
Pay Day Loans…
…