Geek Answers Handbook

What are the pros and cons of using linq to sql versus creating my own datalayer?

I just started playing with Linq to SQL the other day, and I was curious if I would use it in future projects. I know that this will save me a lot of development time. I have seen many similar questions on this, but I have a few more specific questions.

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

  • Are generated SQL commands efficient? When I used SQL Server Profiler, I noticed that when I get a list of all the records, using linqDataSource to bind to the gridView, I will see two queries being executed. The first was

    SELECT COUNT(*) and then a SELECT TOP(PageSizeOfGrid).

    Why?

  • Would it be better to use an ObjectDataSource to get all the records from a stored procedure and cache them?

  • Entity Framework? I don’t know much about this, but I think it may be too hard for my needs. Most of my databases are fairly simple 10-20 tables that can have many different relationships. Is it worth the search?

Any ideas on any of these questions are welcome. Thanks!

+6
linq-to-sql
source share
3 answers

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

There is nothing bad. Similarly, you can make the generated classes inherit from your own base classes or implement other interfaces than the finished ones.

Are the generated SQL commands efficient?

Yes, but, as always, you need to keep an eye on him. If you write your queries in linq, then the same as good SQL queries, the generated sql will be very efficient. L2S is pretty good at optimizing in some scenarios, for example. it eliminates everything that can be eliminated on the client, etc. However, you can force it to generate bad SQL, just as you can write inefficient raw SQL queries manually. Click here for an example ...

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

I don’t know, I have never used LinqDataSource. I prefer to go with raw linq queries, I'm not a fan of automated data / object source controls. Hope someone else can shed some light on this one.

Would I be better off using an ObjectDataSource that gets all the records from the stored procedure and caches them?

Similar to the previous ... :)

Entity Framework? I don’t know much about this, but I think it may be too heavy for my needs. Most of my databases are pretty simple 10 - 20 tables, which can have a lot of relationships. Is it worth looking at?

Wait until the 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 not to fix the basic problems, but instead spend all their time and energy working in 4.0. Whether this person will be a worthy competitor / replacement for L2S remains to be seen. (I just tried beta 1, and I have the same problems as EFv1, primarily problems with bad generated SQL queries ... ( ex 1 ex 2 ex 3 , etc.)

+3
source share

If everything is in order to be able to focus only on SQL Server, I would use LINQ to SQL, since it helps a lot to create a clear and custom data level. If performance is a problem, you can selectively use direct SQL code or stored procedures to retrieve some objects.

About LINQ to Entities, I need to use it only once, in a project in which I had to use ADO.NET data services (the source classes generated by LINQ to SQL do not implement IUpdateable and, only when using this technology), and I do not I found any convincing advantages over LINQ to SQL (I am not saying that there are no such advantages, only I did not find them for my specific project, which simply had several database tables like yours).

+3
source share

Directly answer your question. But: I would never have written my data access library from scratch. This is a lot of time, and this is too common a problem that should not be solved for a single application.

There are quite a few alternatives, and you are likely to find a suitable solution for almost any project.

  • NHibernate
  • Linq to sql
  • Entity Framework
  • quite a few other ORM

You cannot say at all which one is “best,” but writing your own library is most likely the worst.

+3
source share

More articles:


All Articles