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 regarding 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

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.