What would you choose if you could use any .NET DAL technology?

Question

I'm getting back into .NET development after a couple years and it seems that now, especially with LINQ, the way you access your data has changed and become much easier. For instance, in a ASP.NET MVC website, I can:

• Add Item
• add LINQ-to-SQL classes
• drag on database tables onto the LINQ-to-SQL Object Relational Designer, click save
• access and manipluate my data via LINQ one-liners (which I learned here: http://www.asp.net/learn/mvc/tutorial-11-cs.aspx)

This looks great, but how real-world is it?

• is the above LINQ-to-SQL scenario something that you use in real projects or is it just a quick scaffolding technology, i.e. what happens when you start adding, removing fields and tables in your database, how do the LINQ-to-SQL classes stay in sync?

And how do I make sense of all the new technologies in this space, e.g.

• Where does Subsonic fit in?
• Where does Astoria (ADO.NET Data Services) fit in?
• Where does NHibernate fit in?
• How can I use other databases with LINQ-to-SQL (I tried dragging a SQLite table on the Object Relational Designer and got a "not supported error) or is LINQ-to-SQL only for SQL Server?
• does LINQ-to-XML work like LINQ-to-SQL, e.g. can I drag in XML files onto a designer and then access them with LINQ, or do I need to write my own code for this?

• does LINQ-to-Entities work like LINQ-to-SQL i.e. automatically generated classes but just with more options?

• is ADO.NET with its DataTables and DataSets an old technology now that we have LINQ? Does LINQ-to-ADO.NET make sense?

• where does Azure fit in where you don't really even have RDBMS anymore

• where does ESB fit in when your UI is just talking RESTfully to WCF or speaking to web services?

Now that we have so many options, if you could choose any of these technologies for a project, which would you choose and why?

Answer 1

Initial responses (mostly on the LINQ stuff):

• LINQ to SQL is something you can use in real projects; I have done. We tended to have all our Data Access code in the partial class that is generated when you right click on the design surface and select "View Code", rather than scattered as one-liners throughout your code.
• With LINQ to SQL if you modify the database you need to remove and re-add the tables - this is a bit of limitation, with the Entity Framework you can "Update model from database" to auto add/remove these columns.
• It should probably have been called LINQ to MSSQL Server - it is tied directly to SQL Server. If you want to use similar tooling with other data sources, you could have a look at the Entity Framework - however this currently has other limitations - LINQ to SQL was as much as anything a proof of concept that this could work.
• ADO.NET Data Services provide you with a REST based interface to your ADO.NET objects - so you can call simple webservices to retrieve data, without having to write those services.
• No, there isn't a design surface for LINQ to XML - I guess someone could do something with an XSD though, that would be interesting ;)
• You could think of Azure as "An operating system in the cloud", it has a database for storage, although as you state, it's not relational, there's no joins, but you're still going to be querying it for results.

To answer your final question, I've not learnt enough about NHibernate or others to say, but I'd be happy to use LINQ to SQL for basic database access, but I've started looking at LINQ to Entities for my more complex stuff rather than the others - mostly because I like the pretty pictures.

Answer 2

Regarding SQLite: I've successfully used dbLinq to build LINQ queries to a SQLite database. It's still in it's infancy, so expect to have to fix some stuff in the generated classes, but it worked for my needs. It'll do I'd guess 85% of all the things that LinqToSql will do. And there are plenty of unit tests in the project to tell you what they know is failing.

dbLinq supports many other databases (MySQL, PostgreSQL, Firebird). I've not used it for anything other than SQLite.