Starting to construct a data access layer. Things to consider?

Question

Our organisation uses inline sql. We have been tasked with providing a suitable data access layer and are weighing up the pro's and cons of which way to go...

• Datasets
• ADO.net
• Linq
• Entity framework
• Subsonic
• Other?

Some tutorials and articles I have been using for reference:

• http://www.asp.net/(S(pdfrohu0ajmwt445fanvj2r3))/learn/data-access/tutorial-01-vb.aspx
• http://www.simple-talk.com/dotnet/.net-framework/designing-a-data-access-layer-in-linq-to-sql/
• http://msdn.microsoft.com/en-us/magazine/cc188750.aspx
• http://msdn.microsoft.com/en-us/library/aa697427(VS.80).aspx
• http://www.subsonicproject.com/

I'm extremely torn, and finding it very difficult to make a decision on which way to go. Our site is a series of 2 internal portals and a public web site. We are using vs2008 sp1 and framework version 3.5.

Please can you give me advise on what factors to consider and any pro's and cons you have faced with your data access layer.

PS. I found the subsonic web site's information a bit shallow, and a lot of emphasis seems to be placed on how 'cool' it and it's developers are. I'd much prefer a spoken explanation to their demo video than the music? Does anyone else agree?

Answer 1

Not quite an easy decision. I look for simplicity and flexibility. For example I don't quite like configuring NHibernate's xml files; I don't like the ton of code Entity Framework spews out; I am concerned about linq-to-sql end of life. I don't mind deriving from a base class (some people want the pure POCO objects) to support the mappings. I also want to be able to switch databases without changing my code, just changing my configuration. Other small things are good, such as: being able to map stored procedure results to objects; selecting subset of columns for performance reasons; being able to send a pure sql statement if necessary.

Answer 2

I would suggest Entity Framework 4 if you can go with .NET 4.0. It's a good ORM that will only get better as time goes on. You can write your queries, inserts etc. using Linq, which gives you a syntax that is reasonably close to the inline SQL you are using today. Obviously, it has MS behind it so you can expect lots of information and skilled developers to be available now and into the future.

If you have to stay with .NET 3.5, I would look at NHibernate. It is more mature than EF4 overall but lacks full support for Linq right now. Although you will be able to use Linq for relatively simple queries, you will have to go to the somewhat less SQL-like criteria API for more complex queries. I would also use Fluent NHibernate to allow in-code configuration of the mapping. It has strong community support and should have much better Linq support in the very near future. It also has the advantage of being open source.

Either of these ORMs will take care of mapping the objects in your system to the database. This will allow you to focus more of your effort on building the application logic. For that reason, I would not suggest using ADO.Net/Datasets. Linq2Sql is pretty good for what it does but is never going to be as strong as Nhibernate or EF4. Linq2Sql always looked like a first effort from MS and seems to be relegated to playing second fiddle to Entity Framework.

Answer 3

1. Datasets > too much overhead for what you get. This is generally considered an idea that was past it's prime the day it was released.

2. ADO.Net (or Enterprise Library) > my personal preference for a host of reasons. However, we never use inline sql for a host of other reasons (mainly security) as well, so your mileage may vary. One reason we do go this route is that our team is very knowledgable about SQL and every one of the generators either falls short on performance or you have to know a fair amount of sql to tweak them. With that choice we opt to just do it ourselves.

3. Linq > Skip this. New development on LINQ has basically stopped and the resources at MS were moved to the Entity Framework project. I've also found threading and other issues within the framework that haven't been resolved.

4. Entity Framework > Some people like this. You will still need a good amount of SQL knowledge to get the best out of it.

5. Subsonic, etc > Don't have enough experience with 3rd party ones to really tell you the pros/cons.

To sum up, we prefer control and have the knowledge to get the most out of it. We've tried the linq / EF route and weren't exactly impressed. I've seen others, and briefly worked with them, but they failed to be performant on anything but the most basic CRUD type statements.

If most of what you are doing is CRUD related, then EF, Subsonic, or similiar will work.

Some things to consider when evaluating:

• How does it handle ad-hoc queries?
• How does it handle data paging / sorting (SQL side or does it pull everything into the web server)?
• What are the memory leaks?
• Is threading a requirement? Write a simple app to test that in each of the ones you are evaluating. Run it a LOT under different memory / cpu conditions.

Good luck.

Answer 4

The question as stated right now is a little ambiguous. A "data layer" can be comprised of many, many, many different needs and desires.

I'll presume that SQL Server is the backing DB. But is it the only one? How homogenous is the data tier?

Are you just looking for a simple way to query and get data out? Or do you have the need for a full bore object relational mapping solution? Do you have needs beyond just querying? Migrations and scaffolding for example?

Are you willing or able to sacrifice time up front to ramp up to learn the more complex (but flexible) toolsets like NHibernate or Subsonic?

Having worked w/ varying degrees of complexity with EF, Linq to SQL, Subsonic, and NHibernate, none are "hard" to get going at first, but finding solutions to more complex problems or edge cases will vary between each. One can argue that EF and NH have the largest body of users and thus a little bit easy to find blogs/Stackoverflow posts to answer your questions. I had better luck with EF/NH in that regard. That said, Subsonic is a more approachable code base to roll up your sleeves and fix your own problems (IMO).

Personally, after having done the run around a few times, my preference now is to always start w/ what I view to be the simplest solution--Linq to SQL. I find its barrier to entry to be the lowest and it's the least "invasive". It however comes w/ notable restrictions (such as a dependence on SQL server). But for quick n' dirty, just get the job done, it's my favorite as I'd rather not spend inordinate amounts of time worrying about my data tier. Only once requirements come in that dictate a shift would I consider moving away--a recent proj for example needed to read/write to SQLite and SQL Server, and thus we went w/ NH.

Understanding what you actually need might help shape answers away from generic commentary.