CODEWITHSUNDEEP
.netdatabaselinq-to-sql
Erik Forbes
Erik Forbes

Reputation: 35861

What's wrong with Linq to SQL?

What's wrong with Linq to SQL?

Or - what about Linq to SQL would make it unsuitable for a project, either new or existing? I want to hear about why you would not choose Linq to SQL for a particular project - including what project parameters make it unsuitable.

Upvotes: 9

Views: 2244

Answers (11)

Karthik Hariharan
Karthik Hariharan

Reputation: 559

A true ORM should separate the design of your Business Entities from your persistence medium. That way you can refactor either one of them separately and only have to maintain the mapping between the two. This reduces the amount of application logic code that you need to maintain for database changes.

To accomplish this kind of persistence agnostic approach with Linq-to-SQL, you would have to use its generated classes at DTOs and maintain a mapping logic between your DTOs and your Entities.

There are much better ORMs for taking this approach (like NHibernate) that greatly reduce the need for DTOs.

Upvotes: 1

johnc
johnc

Reputation: 40223

It is not very adaptable to changes in the database schema. You have to rebuild the dbml layer and regenerate your data contexts.

Like any ORM (I am not getting into the debate as to whether it is an ORM or not), you do have to be aware what SQL is being generated, and how that will influence your calls.

Inserts are not batched, so can be high cost in performance.

It's being sunsetted in favour of Entity Framework

Despite the fact it is using a provider model that will allow providers to be built for other DBMS platforms, only SQL Server is supported.

[EDIT @ AugustLights - In my experience: ] Lazy loading may take a bit of hacking to get working.

That being said, I think it it is very handy if used correctly

Upvotes: 16

KristoferA
KristoferA

Reputation: 12397

The only thing I would label as a technical "showstopper" is if you want to use other RDBMSes than SQL Server. (although it can be worked around - see Matt Warren's blog @ http://blogs.msdn.com/mattwar/ )

Besides that, there are some pros and cons already listed in previous answers to your question. However, all of the negatives mentioned so far have workarounds so they are not really showstoppers.

A non-technical [potential] showstopper is the risk that MSFT will abandon it in favour of EF... More on that here: http://oakleafblog.blogspot.com/2008/05/is-adonet-team-abandoning-linq-to-sql.html

Although (in my opinion, ) the current state of EF is reason enough for them to continue work on L2S. So let's hope they do...

Upvotes: 1

bzlm
bzlm

Reputation: 9727

It doesn't appear to have any support for default values on DB columns.

Upvotes: 0

liammclennan
liammclennan

Reputation: 5368

  • There is no way to mix-n-match lazy loading / eager loading on a datacontext.
  • True persistance ignorance is very difficult.
  • Mapping options are limited. For example, there are no many-to-many relationships.
  • Resyncing the mapping with schema changes is painful.

Despite all of the above I think linq-to-sql is an excellent choice for many projects.

Upvotes: 4

anon
anon

Reputation:

This question was asked once before over here. But, in essence, LINQ to SQL generates sub-optimal execution plans in your database. For every different length of parameter you search for, it will force the creation of a different execution plan. This will eventually clog up the memory in your database that is being used to cache execution plans and you will start expiring older queries, which will need to be recompiled when they come up again.

As I mentioned in the question I linked to, it's a matter of what you're trying to accomplish. If you're willing to trade execution speed for development speed, LINQ to SQL might be a good choice. If you're concerned about execution speed, there are other ORMs/DALs/solutions available that may take longer to work with but will provide you with future proofing against schema changes and better performing solutions at the cost of additional development overhead.

Upvotes: -1

hurst
hurst

Reputation: 1740

For a project that needs to make use of databases other than SQL Server:

1) You are locked in to using SQL Server

For a project with complex entity relations and/or relations that change gradually over time:

2) You are locked in to 1-to-1 mapping of tables to classes

For a project that must use 1.x versions of .NET

3) Won't work with .NET 1.x

Upvotes: 9

RhysC
RhysC

Reputation: 1644

because you are not using 3.5... is that a valid answer?!?!?

Upvotes: 3

jalbert
jalbert

Reputation: 3097

It is difficult to mock while unit testing because of a lack of an interface on the System.Data.Linq.DataContext class. Here's a possible approach: Mocking LINQ to SQL DataContext.

Upvotes: 3

Grank
Grank

Reputation: 5292

A lot of the advantage to LINQ-to-SQL comes from supposedly being able to construct data queries right in your code-behind based on strongly-typed queryable/enumerable data objects from your dbml (which plays the role of a very limited DAL). So a consequence, as has already been mentioned, is that it encourages you somewhat towards playing outside strongly defined and separated layers or tiers to your application.
To counter that point, it should mean that you should be able to eliminate most or all of any business logic you were writing into stored procedures on the database, so then at least you only have to go to the code that deals with the data to change non-schema-impacting business rules... However, that breaks down a bit when you realise how complicated it can be to write a query with an outer join with aggregates with grouping, at least when you first approach it. So you'll be tempted to write the sprocs in the SQL you know that is so simple and good at doing those things rather than spend the extra time trying to figure out the LINQ syntax to do the same thing when it's just going to convert it to ugly SQL code anyway...
That having been said, I really do love LINQ, and my esteem for it vastly increased when I started ignoring this "query syntax is easier to read" sentiment I've seen floating around and switched to method syntax. Never looked back.

Upvotes: 1

azamsharp
azamsharp

Reputation: 20066

Well, I have developed some applications using LINQ to SQL. One of the main problems that I find is having to layer your application. In LINQ to SQL the entity classes are tied very closely with the data access code. Also, there are some issues with DataContext which means that you can use a DataContext object to retrieve an item but you cannot transfer the item (object) to another DataContext (at least not easily).

LINQ to SQL will be useful if you don't care about layering your application properly and also if all you wanted is to create an application in a rapid manner also know as RAPID Application Development.

Upvotes: 1

Related Questions