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

Question

Whats the general consensus on using LINQ to perform quieries and manipulation inline and how does this differ to embedding SQL statements into your code which is considered a no no.

Answer 1

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 2

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)

Answer 3

For one thing LINQ statements are parameterized - inline queries are not necessarily so. For another, LINQ basically creates an ORM layer - allowing database objects to be treated essentially as objects within the code - providing compile time checking. An inline query on the other hand is a blob of text that will not break at compile time...

Answer 4

Looking at the other answers, maybe I misunderstood the question... but I'm talking about normal LINQ on collections in C#, not LINQ-to-SQL:

When you embed SQL statements in your code (as opposed to making them variables or otherwise parameterizing your SQL), you're making it much more difficult for yourself if you want to support a different database in the future. As old and standardized as SQL is, there are still many incompatibilities between vendors.

With LINQ, portability is no longer an issue -- your code is written in C#, and LINQ is C#.

Answer 5

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 6

From my little understanding, whether you use LINQ-to-SQL or any other ORM or your own code which wraps SQL calls, it is basically giving you an abstraction whereby you don't have to worry about SQL.

Also, if I am correct - LINQ to SQL supports SQL Server only.
I think, it lets you construct your criteria in a SQL like manner using LINQ syntax.

Personally, I don't know why someone would use LINQ to SQL.
Can an expert help me understand the need of it?

Answer 7

At some line of code, someone, sometime, will decide if it's appropriate to type the SQL. But instead of doing that, you'll use LINQ.

So it's not a matter if LINQ should be inlined when SQL should not. The question is where you want your data storage access representation to be. If you're writing LINQ at some line of code, and you don't feel confortable of this scope having access to the stored data, then it's the wrong place.

LINQ is just an abstraction for data manipulation. It's a shortcut for filtering sets. Just like databases understand SQL, the .NET environment has a language to talk with data.

What's the difference between LINQ and doing if statements inside a loop, on data that came from a file, or a network socket? None, it's just the syntax. If you SELECT * FROM table, filtering every row against a function, what's the difference from doing where c.SomeProperty < x with LINQ? Just the syntax.

Sure, LINQ knows and do more than simply returning rows from a database. It lets you instantiate objects from a collection, but so does = new ClassName(column1, colum2) from SQL statement results.

In short, if somewhere writing inline SQL in your code is a no-no, so it should be writing LINQ.

But this varies from project to project. Databases mostly understand SQL, so at least somewhere in your code, library or environment there are inlined SQLs.

Answer 8

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 9

Have worked extensively with embedded SQL, the real danger is that it is a code generation technology. You write C code (or whatever language) and then you switch to SQL, then back to C again. Later prior to compilation a prepocessor rewrites the code to be all C. The results can generate very confusing error messages. You are forced to learn the API to interact with the database which embedded SQL was supposed to free you from. Finally, you run the risk of embedding business rules into your queries confusing the business layer and data layer. Linq avoids some of these issue. First, Linq is part of the language. The constructs work more or less as a programmer expects. There is some SQL type syntax involved, but mostly the syntax is focused on the host language (i.e. C#). The compilation checks are done on the actual code you wrote. Therefor there is not need to translate the errors into the syntax before preprocessing. The results of the query are objects and collections, just as you would expect. There is still a danger of mixing business logic in the data layer however. Additionally, Linq has it's own issues with error messages where the field in question is often not included. Still it is a good compromise between the object world and the relational world.