LINQ-to-SQL and T-SQL

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.