Why do people use linq to sql?

Question

Given the premise:

• There are competent sql programmers (correlary - writing sql queries are not an issue)
• There are competent application developers (correlary - there is simple/strong/flexible architecture for handling connections and simple queries from code)

Why do people use linq to sql?

• There is overhead added to each transaction
• There is strong likelihood of performance loss for moderate-complex calculations (DBs are made for processing sets and calculations and had teams of engineers working out optimization - why mess with this?)
• There is loss of flexibility (if you want to add another ui (non .NET app) or access method, you either have to put the queries back in the db or make a separate data access layer)
• There is loss of security by not having a centralized control of write/update/read on db (for example, a record has changed - if you allow applications to use linq to sql to update, then you cannot prove which application changed it or what instance of an application changed it)

I keep seeing questions about linq to sql and am wondering if I'm missing something.

Answer 1

I keep seeing questions about linq to sql and am wondering if I'm missing something.

It's not that you're missing something. It's that you have something most shops don't have:

There are competent sql programmers

Additionally, in your shop those competent sql programmers prefer to write sql.

---

Here's a point by point response:

There is overhead added to each transaction

Generally true. This can be avoided by translating the queries before they are needed to run using CompiledQuery for many (but not all!) scenarios.

There is strong likelihood of performance loss for moderate-complex calculations (DBs are made for processing sets and calculations and had teams of engineers working out optimization - why mess with this?)

Either you're writing linq, which is translated to sql, and then a plan is generated from the optimizer - or your writing sql from which a plan is generated by the optimizer. In both cases you are telling the machine what you want and it is supposed to figure out how to do it. Are you suggesting that subverting the optimizer by using query hints is a good practice? Many competent sql programmers will disagree with that suggestion.

There is loss of flexibility (if you want to add another ui (non .NET app) or access method, you either have to put the queries back in the db or make a separate data access layer)

A lot of people using linq are already SOA. The linq lives in a service. The non .NET app calls the service. Bada-bing bada-boom.

There is loss of security by not having a centralized control of write/update/read on db (for example, a record has changed - if you allow applications to use linq to sql to update, then you cannot prove which application changed it or what instance of an application changed it)

This is simply not true. You prove which application is connected and issuing sql commands the same way you prove which application is connected and calling a sproc.

Answer 2

Let me list you a few points:

1. There are small software companies or mid-sized companies who develop their software in-house who might rather focus on getting many application developers than getting a freelancer DB developer or even permanently hire one.
2. In most cases the overhead is a non-issue either due to the amount of data to be processed or due to the low traffic. Besides, when used properly, LINQ to SQL can perform as fast as most SQL queries + the associated .net code.
3. Many companies just stick with the Microsoft stack and they can only enjoy the integration. Some other company develops using SOA there's just no problem. The others aren't forced to choose LINQ-to-SQL and if they make that choice is their problem how to integrate it. Nobody ever said LINQ-to-SQL is a silver bullet :)
4. I believe security is gained with LINQ-to-SQL because I've bumped across lots of SQL queries taking in unescaped data with string concatenation etc and explaining the whole parametrized query idea has never been easy. Besides since all queries are eventually translated into SQL, unless the tracking issue you describe would happen via a stored procedure, there're again no problems at all.

I also believe your question can be posed more generally to address all ORMs and not just LINQ-to-SQL, and still most of what I said would hold true.