1

What are the downsides and limitations of using Linq to Sql verses writing a more traditional data layer calling stored procs/using dynamic sql through the .NET SQL Server data provider?

The advantages are well documented but I’ve found little discussion of the real world issues people have experienced.

Please note I’m not talking about comparing with O/R mappers such as NHibernate and Subsonic.

Keith Harrison
  • 1,567
  • 14
  • 22

3 Answers3

3

There are a few - not sure if those matter to you:

  • limited to SQL Server as backend
  • requires at least .NET 3.5 to run
  • somewhat limited in that tables are mapped strictly on a 1:1 basis (one table = one class)

But again - those are just limitations, but a lot of folks (myself included) can live with those no problem - at least for a certain type of project.

If you need more flexibility (more database backends, more granular mapping), you should definitely look at NHibernate or later on at Entity Framework 4. They offer more power and more punch - but they're also a tad harder to learn.

ON the other hand, Linq-to-SQL also has massive pros:

  • visual designer makes it really easy to use
  • using LINQ, you're much more productive than using straight ADO.NET and sprocs

But I'm sure you're well aware of those pro sides, right? :-)

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • No support for bulk transactions – Alan Dec 31 '09 at 11:17
  • Also it's really easy to write queries with rearry bad DB performance. – Alan Dec 31 '09 at 11:18
  • @Alan: that's very easy in inline SQL or stored procedures, too! – marc_s Dec 31 '09 at 11:21
  • @Alan: true, Linq-to-SQL doesn't cover **all** aspects - but it's quite a powerful solution for 80,90% of the cases - querying data and presenting it to the user. – marc_s Dec 31 '09 at 11:22
  • 1
    Actually one can use MySQL with LINQ to SQL using the MySQL DLL from SUN. – Todd Moses Dec 31 '09 at 16:27
  • @Todd: do you mean just plain LINQ, or the actual "Linq-to-SQL" with the visual designer and all?? I had never heard of Linq-to-SQL being opened to other database providers..... the plain LINQ as a language construct - sure. But not the designers and code generators of Linq-to-SQL – marc_s Dec 31 '09 at 16:53
  • Devart released LINQ to Oracle, LINQ to MySQL, LINQ to PostgreSQL, and LINQ to SQLite implementations. – Devart Jan 04 '10 at 12:24
1

The biggest problem I've found is that you seem to be required to drag and drop database tables onto a designer, and it's not easy to access the dbml text directly. There have been many times I've added a column to a table and wanted to update it, only to have to delete the table from the designer, re-add it, and remap any custom associations I've done.

If someone can tell me how to easily get at the dbml text, I'd love to hear how to do this.

Another thing that bugs me is if I get a "string or binary data may be truncated" error, I can't tell which column caused the error, making me play a trial and error game.

ristonj
  • 1,590
  • 1
  • 12
  • 15
0

The major downside of Linq to SQL is the uncertainty surrounding it with the advent of the ADO.Net Entity Framework - unless the situation has changed, there has been strong rumours that Linq to SQLs future is cloudy as MS are concentrating on the Entity Framework and moving away from Linq to SQL.

I have already moved to the Entity Framework, and until the situation becomes clearer I am using Linq to SQL sparingly on major projects.

  • 2
    Linq-to-SQL is still fully supported in .NET 4 and even gets a bunch of bugfixes and feature improvements - http://damieng.com/blog/2009/06/01/linq-to-sql-changes-in-net-40 – marc_s Dec 31 '09 at 11:11
  • 4
    Rumours of Linq-to-SQL's death have been greatly exaggerated - and repeating them over and over again doesn't make them any more true.... – marc_s Dec 31 '09 at 11:12