What is the best data access paradigm for scalability?

Question

There are so many different options coming out of microsoft for data access. Which one is the best for scalable apps?

Linq

Should we be using Linq? It certainly seems easy but if you know your SQL does it really help. Also I hear that you can't run Async queries in ASP.NET using Linq. Therefore I wonder if it is really scalable? Are there any really big sites using Linq (With the possible exception of stackoverflow).

Entity Framework

Don't hear so much razzmatazz about the Entity Framework. Seems closer to the Object model I'm familure with.

Astoria/Dynamic Data

Should we be exposing our data as a service?

I'm pretty confused and thats before I get into the other ORM products like NHibernate. Any ideas or wisdom on which is better?

Answer 1

I think ADO.Net Data Services (formerly called Astoria) has a huge role to play. It fits nicely with the REST style architecture of the web.

Since web is scalable, I guess anything which follows its architecure is scalable too.. Also, you might want to keep a lookout for SQL Server Data Services..

Answer 2

Use whatever works for you. These are all easiest to set up if you already have a fairly normalized database (ie, good definition of primary keys and foreign keys). However, if you've got data that doesn't easily normalize, the Entity Framework is more flexible than LINQ to SQL, but it does take more work to configure.

Answer 3

We've been experimenting with LINQ in a clustered environment and it appears to be scaling well on the individual machines and across the cluster. Of the 3 options that you've provided I would say that LINQ is the better choice although each option has a slightly different target audience so you should define what you will be doing with the data before deciding on the acesss paradigm.

Answer 4

If you are talking about relational databases, then my vote is for encapsulating all of your data operations in stored procedures, regardless of how you access them from the other layers.

If you disable all read/write access to the database, except via stored procedures, you can hide your data model behind well defined contracts. The data model is free to change, just so the stored procedures still honor their inputs and outputs.

This gives DBAs total freedom to tune your application and make it scale. This is a very, very difficult task when SQL is being generated by a tool outside of the database.

Answer 5

I would suggest linq. It scales well on our site and is simple enough to use.

Answer 6

use stored procedures with LINQ...but don't let the sprocs turn into a data access layer!

Answer 7

I would recommend either NHibernate or Entity Framework. For large sites, I'd use ADO.NET Data Services. I wouldn't do anything large with LINQ to SQL. I think Stack Overflow might end up with some interesting scale problems being 2-tier rather than 3-tier, and they'll also have some trouble refactoring as the physical aspects of the database change and those changes ripple throughout the code. Just a thought.

Answer 8

Locking into stored procedures seems to be a waning way of thought these days, at least that have been my current observations. That way of thinking does lend itself to the ORM world since they are typically more affective going against tables directly but any ORM worth their salt will also allow the use of procs – sometime you have no choice.

There is plenty of opinions around EF and regardless of what anyone says, good or bad, it is a V1 product and with the rule of thumb that MS takes about 3 revs to get it right it might be prudent to wait for the next rev at least.

It seems that the biggest player out there in this space is NHibernate and there is plenty of support for this in the community. Linq, the language feature, shouldn’t be too far off in making its way to the NHibernate stack.