What pros and cons are there using linq to sql vs creating my own datalayer?

Question

I just started playing with Linq to SQL the other day and was curious if I should use it in up coming projects. I know it will save me tons of developement time. I have seen many similar questions out there on the subject but I have a few more specific questions.

• Is there anything wrong with inheriting the classes generated in the .dbml file?
• Are the generated SQL commands efficient? When I used SQL Server Profiler I noticed when I would get a list of all records using a linqDataSource to bind to a gridView I would see two querries being executed. The first one was a SELECT COUNT(*) and then a SELECT TOP(PageSizeOfGrid). Why?
• Would I be better off using an ObjectDataSource getting all records from a stored procedure and caching them?
• Entity Framework? Don't know much about it but I think it may be too heavy for my needs. Most of my databases are fairly simple 10 - 20 tables that may have many to many relationships. Is it worth looking into?

Any ideas on any of this are appreciated. Thanks!

Answer 1

If it is OK to be able to target SQL Server only, I would jump into using LINQ to SQL since it greatly helps producing a clear and maintabale data layer. If performance is a concern, you can selectively use direct SQL code or stored procedures to retrieve some of the entities.

About LINQ to Entities, I have needed to use it only once, in a project in which I had to use ADO.NET Data Services (the raw LINQ to SQL generated classes do not implement IUpdateable and so are read-only when used via this technology), and I had not found any compelling advantage over LINQ to SQL (I'm not saying there are not such advantages, only that I didn't find them for my particular project, which just had a handful of database tables as yours).

Answer 2

Not directly an answer to your question. But: I would never ever write my own data access library from scratch. Its much to time consuming and it is a much too general problem not worth to be solved for a single application.

There a quite a few alternatives available, and you will most probably find an appropriate solution for almost any kind of project.

• NHibernate
• Linq to Sql
• Entity Framework
• quite a few other ORMs

You can't say in general which one is the "best" choice, but writing your own library is most probably the worst.

Answer 3

Is there anything wrong with inheriting the classes generated in the .dbml file?

No, nothing wrong. And likewise, you can make the generated classes inherit from your own base classes or implement other interfaces than the out-of-the-box ones.

Are the generated SQL commands efficient?

Yes, but as always you need to keep an eye on it. If you write your linq queries the same was as good SQL queries the generated sql will be very efficient. L2S is pretty good at optimizing under some scenarios, e.g. it eliminates anything that can be eliminated on the client etc. That said, it is possible to make it generate bad SQL just like it is possible to write inefficient raw SQL queries by hand. Click here for an example ...

When I used SQL Server Profiler I noticed when I would get a list of all records using a linqDataSource to bind to a gridView I would see two querries being executed. The first one was a SELECT COUNT(*) and then a SELECT TOP(PageSizeOfGrid). Why?

No idea, never used the LinqDataSource. I prefer to go with raw linq queries, I'm not a fan of automated data source controls/objects. Hopefully someone else can shed some light on that one.

Would I be better off using an ObjectDataSource getting all records from a stored procedure and caching them?

Same as previous... :)

Entity Framework? Don't know much about it but I think it may be too heavy for my needs. Most of my databases are fairly simple 10 - 20 tables that may have many to many relationships. Is it worth looking into?

Wait until next version of EF. It will be released as part of .net 4.0. The current version of EF is not ready for prime-time and for some odd reason Microsoft decided to not patch the base problems but instead put all their time and energy into working on 4.0. Whether that one will be a worthy competitor/replacement for L2S remains to be seen. (I have only tried beta 1, and that one suffers from the same issues as EFv1; primarily problems with poor generated SQL queries... (ex 1 ex 2 ex 3 etc)