If it's bad to use inline SQL, how does using LINQ to perform queries differ in practice?

Question

What's the general consensus on using LINQ to perform queries and manipulation inline and how does this differ to embedding SQL statements into your code (which is considered a no no)?

Answer 1

Ignoring all the non-SQL related uses of LINQ and just considering LINQ-to-SQL.

LINQ queries are objects that retain a query definition and the equivalent SQL is only instantiate at the moment the query is actually iterated. That permits components to be properly separated and allow a pipeline style processing where queries are returned by lower layers (eg. by repositories) and transformed by higher components on the stack. The query can accommodate new filters, joins and other 'artifacts' from each component in the processing pipe, until it reaches the final form that is eventually iterated. This manipulation is impossible in practice using straight text SQL.

Another advantage of LINQ is that its syntax is easier to comprehend for non-database developers. SQL syntax expertise is a surprisingly hard to find asset. Few developers are willing to spend time learning subtler points of SQL beyond SELECT * FROM ... WHERE .... The linq syntax is closer to the .Net everyday environment and leverages the existing skill set at that layer (eg. lambda expressions) as opposed to forcing the developer to learn the proper SQL for the targeted back end (T-SQL, PL-SQL etc). Given that the linq expression expresses the desired result pretty much straight in relational algebra, the providers have a much easier job into generating the appropriate SQL (or even generate straight the execution plan!). Compare this with the impossible task 'generic' database drivers had to face preciously with SQL text. Remember ODBC escape syntax, anyone?

Answer 2

You're right: "Embedding SQL statement in your code is not bad." It is terrible.

Doing so makes your application highly coupled, brittle and difficult to maintain. At least, if you have it all in procs, when you alter something, you can do a search on dependancies.

stored procs, I feel really need to go away too for those procs that are handling business logic.

The reason is that business logic belongs in the business layer - not the data layer.

Of course, if the generated Linq to SQL turns out something messy & slow, you've got to use a proc.

One of the great advantages of Linq To SQL is that you can chain together filters - you're where clause is just added dynamically via your logic as required. You don't need to create new procs just because you want to call for a set of data based on multiple params.

Answer 3

Embedding SQL statement in your code is not bad. It all depend on which layer of your application you are writing them.

Anything that is related to reading and writing to the data store, should be on the Data Access Layer.

Answer 4

Two advantages of "Linq To Sql" versus (the common worst-case of) "inline SQL".

Parameters are treated as parameters, not just as concatenated text -- this overcomes SQL injection problems.

The separate model layer allows for compile time checking that the generated sql will match the schema. (In a worst case of inline sql, you'd have no way to know at compile time if you were referencing a column that had been deleted, for example)