Channels ▼
RSS

Design

LINQ-to-SQL and T-SQL

Source Code Accompanies This Article. Download It Now.


Fine-Tuning LINQ-to-SQL Code

Any class in the LINQ-to-SQL object model is partial, meaning that its source code is not contained in a single class but it may span over two or more distinct source files. By leveraging the partial class (and partial method) mechanism, you can modify the behavior of LINQ-to-SQL and bend it to your wishes.

There are two main mechanisms of extensibility built into LINQ-to-SQL classes that relate to fine-tuning T-SQL code:

  • Partial methods in entity classes.
  • Partial methods in the data context class.

A partial method in a partial class is an optional method that is declared without an implementation. The following code shows the partial methods defined for the data context class:


partial void OnCreated();
partial void InsertCustomer(Customer instance);
partial void UpdateCustomer(Customer instance);
partial void DeleteCustomer(Customer instance);
partial void InsertOrder(Order instance);
partial void UpdateOrder(Order instance);
partial void DeleteOrder(Order instance);


A call to a partial method may exist everywhere within a partial class. At compile time, if a body for any partial methods is not found, the compiler replaces any call to a partial method with a no-op. In other words, partial methods are injection points in the body of a class for pieces of code that you want to customize. For example, the partial methods listed above let you change the way in which a customer (or an order) is inserted, updated, or deleted. If you implement any of those methods, you can specify your own T-SQL code for update operations. Here's an example:


partial void DeleteCustomer(Customer instance)
{
   this.ExecuteCommand("DELETE FROM customers WHERE customerid=@id",
            instance.CustomerID); 
}


Needless to say, you can use stored procedures here as well.

Another bunch of extensibility methods exist to let you validate changes entered into entity classes such as Customer and Order.


partial void OnLoaded();
partial void OnValidate(System.Data.Linq.ChangeAction action);
partial void OnCreated();
partial void OnCustomerIDChanging(string value);
partial void OnCustomerIDChanged();


For each property, you find a pair of OnXxxChanging and OnXxxChanged methods to let you control what happens before and after the assignment of a value to a property.

Finally, what if you need to run a complex query and find out that the LINQ-to-SQL autogenerated T-SQL is not the best possible? How can you replace a query? You just run a plain T-SQL query using ADO.NET commands and objects. LINQ and LINQ-to-SQL are useful and helpful, but they're just high-level tools built on top of ADO.NET.

Conclusion

LINQ-to-SQL can definitely let you do your database stuff without knowing much about T-SQL and SQL Server. For example, LINQ-to-SQL is an excellent shortcut for, say, developers with an Oracle background. At the same time, LINQ-to-SQL is a technology open enough to let SQL Server experts express their skills and ability and do things the way they want. LINQ is the query language and LINQ-to-SQL is an auto-generated object model based on a SQL Server table that can be queried using LINQ. As a whole, LINQ-to-SQL looks like a simple and SQL Server-specific O/RM tool capable of offering CRUD and query services with support for transactions and concurrency. To answer the initial question—are LINQ-to-SQL and T-SQL friends or enemies? They're not certainly enemies. So are they friends? LINQ-to-SQL certainly has an intimate knowledge of T-SQL, but the T-SQL it produces may not be optimal in all situations. The number of situations where this happens is really few. You should use LINQ-to-SQL extensively before you can safely conclude that it doesn't work for you.

The Entity Framework and .NET 3.5 SP1

With the .NET Framework 3.5 SP1, Microsoft officially introduced Version 1.0 of ADO.NET Entity Framework (EF) with a similar tool—LINQ-to-Entities. Both LINQ-to-SQL and Entity Framework are O/RM tools, though with different flavors and scopes. The Entity Framework is Microsoft's fully fledged O/RM tool that, in terms of features, may compete with commercial and open-source products such as NHibernate and LLBLGen Pro. LINQ-to-Entities is just the query language that maps an Entity Framework entity model to a LINQ syntax. EF is the big brother of LINQ-to-SQL. If you find that EF works for you, you might just want to opt for that and skip over LINQ-to-SQL. Even in this case, though, the considerations we made here about T-SQL remain largely valid.

—D.E.


Related Reading


More Insights






Currently we allow the following HTML tags in comments:

Single tags

These tags can be used alone and don't need an ending tag.

<br> Defines a single line break

<hr> Defines a horizontal line

Matching tags

These require an ending tag - e.g. <i>italic text</i>

<a> Defines an anchor

<b> Defines bold text

<big> Defines big text

<blockquote> Defines a long quotation

<caption> Defines a table caption

<cite> Defines a citation

<code> Defines computer code text

<em> Defines emphasized text

<fieldset> Defines a border around elements in a form

<h1> This is heading 1

<h2> This is heading 2

<h3> This is heading 3

<h4> This is heading 4

<h5> This is heading 5

<h6> This is heading 6

<i> Defines italic text

<p> Defines a paragraph

<pre> Defines preformatted text

<q> Defines a short quotation

<samp> Defines sample computer code text

<small> Defines small text

<span> Defines a section in a document

<s> Defines strikethrough text

<strike> Defines strikethrough text

<strong> Defines strong text

<sub> Defines subscripted text

<sup> Defines superscripted text

<u> Defines underlined text

Dr. Dobb's encourages readers to engage in spirited, healthy debate, including taking us to task. However, Dr. Dobb's moderates all comments posted to our site, and reserves the right to modify or remove any content that it determines to be derogatory, offensive, inflammatory, vulgar, irrelevant/off-topic, racist or obvious marketing or spam. Dr. Dobb's further reserves the right to disable the profile of any commenter participating in said activities.

 
Disqus Tips To upload an avatar photo, first complete your Disqus profile. | View the list of supported HTML tags you can use to style comments. | Please read our commenting policy.
 

Video