What are the advantages of LINQ to SQL?

Question

I've just started using LINQ to SQL on a mid-sized project, and would like to increase my understanding of what advantages L2S offers.

One disadvantage I see is that it adds another layer of code, and my understanding is that it has slower performance than using stored procedures and ADO.Net. It also seems that debugging could be a challenge, especially for more complex queries, and that these might end up being moved to a stored proc anyway.

I've always wanted a way to write queries in a better development environment, are L2S queries the solution I've been looking for? Or have we just created another layer on top of SQL, and now have twice as much to worry about?

Answer 1

I must say they are what you have been looking for. It takes some time getting used to it, but once you do you can't think of going back (at least for me). Regarding linq vs. stored procedures, you can have poor performance on either if you build it wrong. I moved to linq to sql some stored procedures of a client that were awfully coded, so the time dropped from 20secs (totally unaceptable for a web app) to < 1 sec. And much much less code then the stored procedure solution.

Update 1: Also you get a lot of flexibility, as you can limit the columns of what you are getting and it will actually only retrieve that. On the stored procedure solution you have to define a procedure for each column set you are getting, even if the underlying queries are the same.

Answer 2

We switched over to LINQ2Entity over the Entity Framework environment recently. Before, we had basic SQLadapters. Since the database we are working with is rather small, I cannot comment on the performance of LINQ.

I must admit though, writing queries have become a lot easier, and the addition of Entities, does enable strong typing.

Answer 3

Just a few quick thoughts.

LINQ in general

• Query in-memory collections and out-of-process data stores with the same syntax and operators
• A declarative style works very well for queries - it's easier to both read and write in very many cases
• Neat language integration allows new providers (both in and out of process) to be written and take advantage of the same query expression syntax

LINQ to SQL (or other database LINQ)

• Writing queries where you need them rather than as stored procs makes development a lot faster: there are far fewer steps involved just to get the data you want
• Far fewer strings (stored procs, parameter names or just plain SQL) involved where typos can be irritating; the other side of this coin is that you get Intellisense for your query
• Unless you're going to work with the "raw" data from ADO.NET, you're going to have an object model somewhere anyway. Why not let LINQ to SQL handle it for you? I rather like being able to just do a query and get back the objects, ready to use.
• I'd expect the performance to be fine - and where it isn't, you can tune it yourself or fall back to straight SQL. Using an ORM certainly doesn't remove the need for creating the right indexes etc, and you should usually check the SQL being generated for non-trivial queries.

It's not a panacea by any means, but I vastly prefer it to either making SQL queries directly or using stored procs.

Answer 4

Advantages L2S offers:

• No magic strings, like you have in SQL queries
• Intellisense
• Compile check when database changes
• Faster development
• Unit of work pattern (context)
• Auto-generated domain objects that are usable small projects
• Lazy loading.
• Learning to write linq queries/lambdas is a must learn for .NET developers.

Regarding performance:

• Most likely the performance is not going to be a problem in most solutions. To pre-optimize is an anti-pattern. If you later see that some areas of the application are to slow, you can analyze these parts, and in some cases even swap some linq queries with stored procedures or ADO.NET.
• In many cases the lazy loading feature can speed up performance, or at least simplify the code a lot.

Regarding debuging:

• In my opinion debuging Linq2Sql is much easier than both stored procedures and ADO.NET. I recommend that you take a look at Linq2Sql Debug Visualizer, which enables you to see the query, and even trigger an execute to see the result when debugging.
• You can also configure the context to write all sql queries to the console window, more information here

Regarding another layer:

• Linq2Sql can be seen as another layer, but it is a purely data access layer. Stored procedures is also another layer of code, and I have seen many cases where part of the business logic has been implemented into stored procedures. This is much worse in my opinion because you are then splitting the business layer into two places, and it will be harder for developers to get a clear view of the business domain.