Linq to SQL or Linq to DataSet?

Question

I am new to Linq world and currently exploring it. I am thinking about using it in my next project that involves database interaction.

From whatever I have read, I think there are 2 different ways to interact with databases:

• Linq to SQL
• Linq to DataSet

Now the product that I am to work on, cannot rely on the type of database. For example, it might be deployed with SQL server/Oracle.

Now my questions are:

1. If I use Linq to SQL, am I stuck with SQL server only?
2. I think I can use Linq to DataSet for both SQL server and oracle. But will I loose something (ease of programming, performance, reliability etc) if I use Linq to DataSet for SQL server (compared to Linq to SQL offcourse).

Answer 1

You are correct about #1 - Linq to Sql will only work against SQL Server databases.

I'd go with the ADO Entity Framework if you want the ability to access different data sources (using different providers). You get similar flexibility in terms of using POCO-like entities and it's quite easy to extend for more advanced/complex implementations.

On my current project, we're using Linq to Sql and it's been fine, but we've had a number of issues to overcome. I've found it a little too simplistic at times in terms of extensibility. I wrote a (better) response regardsing Linq to Sql and the Entity Framework here.

With respect to question #2 - I'm not sure I'd like to go back to DataSets. IMHO they are more a thing of the past, but can be useful if you have a decent toolkit with specific controls (like Infragistics). However, I find their overhead too expensive for fast transactional systems. The implementation doesn't have half the functionality of Linq to Sql or the Entity Framework.

Answer 2

1. Yes, it's SQL Server only. Additionally, Microsoft have frozen L2S and will not refine it further. But it's a good framework, performs really well and is easy to use.
2. Linq to DataSet accesses datasets as enumerables after data has been fetched from the DB. Linq to SQL uses an IQueriable to actually build dynamic SQL queries. In many cases, L2S will perform much better, and save you from writing the DB code at all.

You should look into Linq to Entities. That's the most full-blown framework available. Right now, it's mainly for SQL Server, but you will have support for Oracle etc. in time.

Answer 3

1. You are not stuck with SQL server only. In theory you can build a linq provider for any database. There is a project on codeplex for Linq to Oracle http://www.codeplex.com/LinqToOracle. I have not tried it myself.

2. How will you get the data into the dataset? I don't suppose you are going to fetch an entire table into a dataset? That would be a serious performance problem. If you are going to use Linq to dataset you will need to write all the SQL queries yourself to get the data from the database, instead of letting linq create the sql queries. So you will actually first have to query the database and then query the dataset. Seems like double work to me...

Answer 4

When you use Linq to SQL, you'd be pretty much stuck with SQL Server, as far as I know.

If you use Linq to DataSet, you'll lose a bit of ease of programming: With Linq, you can use the Linq entities directly while with DataSets, you have to keep using the DataSet name (MyDataSet.Entity = new MyDataSet.Entity()), which gets old after a while. I think that that's the only sacrifice.

However, you can use it with e.g. Oracle (did that on a project). It's also pretty much drag-and-drop with a bit more control on the DataAdapter (as far as I know - I've never had to tweak Linq-to-SQL that much), you can specify (e.g.) which queries to use, etc.

Since you can still define relationships between tables in DataSets, you can still use Linq well enough, so you won't really see problems there.

I assume that reliability is as good with Linq-to-DataSet as with Linq-to-SQL (never had problems), performance seemed to be good enough, never could really profile it, though.

Answer 5

To answer your first question: no, there are other implementations of linq providers for oracle, for example:

LinqToOracle

DbLinq

The latter supporting more databases, like SqLite.

Also checkout the ADO.NET Entity Framework.

Answer 6

You are not stuck to SQL Server only. We offer LINQ to SQL implementations for Oracle, MySQL, PostgreSQL, and SQLite servers. Get more information here DataSets are not so convenient in comparison with LINQ to SQL technology. Check out the Entity Framework option as an alternative. You are able to write several storage models with one conceptual model and then use SQL Server and Oracle database in parallel.