Thursday, May 29, 2008

Linq To SQL

Linq to SQL is an object relational mapper (ORM) that is included in .NET 3.5.  The Linq to SQL designer allows you to create objects from you database tables simply by dragging the tables from the server explorer to the design surface.  You can customize the generated types in the designer or write code in partial classes.  Once you have defined your model, you can query it with Linq syntax and the SQL provider will generate the appropriate database calls for you.

One limitation of Linq to SQL is a given object can only be populated from one table.  This means that your object will map almost one to one with your database tables.  For anything but the simplest applications, you don't want to work with the database tables.  For these situations, you could use Linq to SQL to populate domain objects.  Keep in mind that there is a performance overhead in creating these objects as opposed to using raw ADO.NET.  Rico Mariani has a good series of blog posts about Linq to SQL performance, but I suggest profiling your application if you think it will be a problem.


merlin981 said...

Even though the designer allows tables to be used, it is still considered best practice to use stored procedures. LINQ to SQL does highly optimize the SQL code, but it must still be compiled by the SQL Server before execution (not to mention the fact that calling on tables uses deferred execution).

Stored procedures allow the sql commands to be compiled and optimized on the SQL Server itself, which will result in faster execution. Also, sprocs can query multiple tables, databases, and servers.

LINQ Exchange - Learn LINQ and Lambda Expressions

Robin Clowers said...

Hi merlin, thanks for your comment. I started to refute your argument in this comment, but it turned into a whole post. If you have a minute, please check it out.