Linq to SQL or Linq to DataSet?

.net linq linq-to-sql linq-to-dataset
Hemant picture Hemant · Mar 6, 2009 · Viewed 10.3k times · Source

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

RobS picture RobS · Mar 6, 2009

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.

Related questions

LINQ: When to use SingleOrDefault vs. FirstOrDefault() with filtering criteria

Consider the IEnumerable extension methods SingleOrDefault() and FirstOrDefault() MSDN documents that SingleOrDefault: Returns the only element of a sequence, or a default value if the sequence is empty; this method throws an exception if there is more than one element …

Read More
LINQ - Left Join, Group By, and Count

Let's say I have this SQL: SELECT p.ParentId, COUNT(c.ChildId) FROM ParentTable p LEFT OUTER JOIN ChildTable c ON p.ParentId = c.ChildParentId GROUP BY p.ParentId How can I translate this into LINQ to SQL? I got …

Read More
How to select only the records with the highest date in LINQ

I have a table, 'lasttraces', with the following fields. Id, AccountId, Version, DownloadNo, Date The data looks like this: 28092|15240000|1.0.7.1782|2009040004731|2009-01-20 13:10:22.000 28094|61615000|1.0.7.1782|2009040007696|2009-01-20 13:11:38.000 28095|95317000|1.0.7.1782|2009040007695|2009-01-20 13:10:18.000 28101|15240000|1.0.7.1782|2009040004740|2009-01-20 14:10:22.000 28103|61615000|1.0.7.1782|2009040007690|2009-01-20 14:11:38.000 28104|95317000|1.0.7.1782|2009040007710|2009-01-20 14:10:18.000 How can I, in LINQ to SQL, only get …

Read More