LINQ to SQL (Part 4 - Updating our Database)
page 6 of 12
by Scott Guthrie
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 86601/ 189


A transaction is a service provided by a database (or other resource manager) to guarantee that a series of individual actions occur atomically - meaning either they all succeed or they all don't, and if they don't then they are all automatically undone before anything else is allowed to happen.

When you call SubmitChanges() on your DataContext, the updates will always be wrapped in a Transaction.  This means that your database will never be in an inconsistent state if you perform multiple changes - either all of the changes you've made on your DataContext are saved, or none of them are.

If no transaction is already in scope, the LINQ to SQL DataContext object will automatically start a database transaction to guard updates when you call SubmitChanges(). Alternatively, LINQ to SQL also enables you to explicitly define and use your own TransactionScope object (a feature introduced in .NET 2.0).  This makes it easier to integrate LINQ to SQL code with existing data access code you already have.  It also means that you can enlist non-database resources into the transaction - for example: you could send off a MSMQ message, update the file-system (using the new transactional file-system support), etc - and scope all of these work items in the same transaction that you use to update your database with LINQ to SQL.

Validation and Business Logic

One of the important things developers need to think about when working with data is how to incorporate validation and business rule logic.  Thankfully LINQ to SQL supports a variety of ways for developers to cleanly integrate this with their data models. 

LINQ to SQL enables you to add this validation logic once - and then have it be honored regardless of where/how the data model you've created is used.  This avoids you having to repeat logic in multiple places, and leads to a much more maintainable and clean data model. 

Schema Validation Support

When you define your data model classes using the LINQ to SQL designer in VS 2008, they will by default be annotated with some validation rules inferred from the schema of the tables in the database.

The datatypes of the properties in the data model classes will match the datatypes of the database schema.  This means you will get compile errors if you attempt to assign a boolean to a decimal value, or if you attempt to implicitly convert numeric types incorrectly.

If a column in the database is marked as being nullable, then the corresponding property in the data model class created by the LINQ to SQL designer will be a nullable type.  Columns not marked as nullable will automatically raise exceptions if you attempt to persist an instance with a null value.  LINQ to SQL will likewise ensure that identity/unique column values in the database are correctly honored.

You can obviously use the LINQ to SQL designer to override these default schema driven validation settings if you want - but by default you get them automatically and don't have to take any additional steps to enable them.  LINQ to SQL also automatically handles escaping SQL values for you - so you don't need to worry about SQL injection attacks when using it.

Custom Property Validation Support

Schema driven datatype validation is useful as a first step, but usually isn't enough for real-world scenarios. 

Consider for example a scenario with our Northwind database where we have a "Phone" property on the "Customer" class which is defined in the database as an nvarchar.  Developers using LINQ to SQL could write code like below to update it using a valid telephone number:

Figure 10 

The challenge that we will run into with our application, however, is that the below code is also legal from a pure SQL schema perspective (because it is still a string even though it is not a valid phone number):

Figure 11

To prevent bogus phone numbers from being added into our database, we can add a custom property validation rule to our Customer data model class.  Adding a rule to validate phone numbers using this feature is really easy.  All we need to-do is to add a new partial class to our project that defines the method below:

Figure 12

The code above takes advantage of two characteristics of LINQ to SQL:

1) All classes created by the LINQ to SQL designer are declared as "partial" classes - which means that developers can easily add additional methods, properties, and events to them (and have them live in separate files).  This makes it very easy to augment the data model classes and DataContext classes created by the LINQ to SQL designer with validation rules and additional custom helper methods that you define.  No configuration or code wire-up is required.

2) LINQ to SQL exposes a number of custom extensibility points in its data model and DataContext classes that you can use to add validation logic before and after things take place.  Many of these extensibility points utilize a new language feature called "partial methods" that is being introduced with VB and C# in VS 2008 Beta2.  Wes Dyer from the C# team has a good explanation of how partial methods works in this blog post here.

In my validation example above, I'm using the OnPhoneChanging partial method that is executed anytime someone programmatically sets the "Phone" property on a Customer object.  I can use this method to validate the input however I want (in this case I'm using a regular expression).  If everything passes successfully, I just return from the method and LINQ to SQL will assume that the value is valid.  If there are any issues with the value, I can raise an exception within the validation method - which will prevent the assignment from taking place.

Custom Entity Object Validation Support

Property level validation as used in the scenario above is very useful for validating individual properties on a data model class.  Sometimes, though, you want/need to validate multiple property values on an object against each other. 

Consider for example a scenario with an Order object where you set both the "OrderDate" and the "RequiredDate" properties:

Figure 13

The above code is legal from a pure SQL database perspective - even though it makes absolutely no sense for the required delivery date of the new order to be entered as yesterday. 

The good news is that LINQ to SQL in Beta2 makes it easy for us to add custom entity level validation rules to guard against mistakes like this from happening.  We can add a partial class for our "Order" entity and implement the OnValidate() partial method that will be invoked prior to the entity's values being persisted into the database.  Within this validation method we can then access and validate all of the data model class properties:

Figure 14

Within this validation method I can check any of the entity's property values (and even obtain read-only access to its associated objects), and raise an exception as needed if the values are incorrect.  Any exceptions raised from the OnValidate() method will abort any changes from being persisted in the database, and rollback all other changes in the transaction.

View Entire Article

User Comments

Title: Good Article   
Name: Vishnu Shanmugam
Date: 2010-12-11 12:46:40 AM
Excellent article.
Title: very clear   
Name: tarun
Date: 2010-08-13 7:31:17 AM
thanks for the clear explanation
Title: this is terrible   
Name: mac
Date: 2010-08-02 4:56:22 PM
this is by far the worst display of CRUD i have seen yet.
Title: How can I insert dates   
Name: Ernie Smart
Date: 2010-03-17 1:08:05 PM
How can I parse or convert dates? Let's say I have a text box (txtDate) to enter the dates and the database has a field called OrderDate. If either of the follwoing two:
OrderDate = Convert.ToDateTime(txtDate)
OrderDate = DateTime.Parse(txtDate)
I get System.FormatException: String was not recognized as a valid DateTime error.
If I do OrderDate = DateTime.Now, the code executes correctly.
How can convert the date entered in the text box so the program will work?
Title: i can't find add,update or delete methods for my data context.   
Name: Enow Mbi
Date: 2010-02-19 11:10:15 AM
I can't figure out why i can't find,add,update or delete methods of my data context despite the fact that my table has a primary key.
Title: More Article   
Name: twinsf
Date: 2010-01-28 2:09:59 AM
thank youu for helpfull
Title: Bad advice   
Name: Simon
Date: 2008-12-28 11:59:06 PM
The partial class validation logic you demonstrate is not testable and is therefore useless to me. Isn't it better advice to put the validation logic in business objects (as Rocky Lhotka would urge). Or alternatively, to put it in a service layer or repository that is testable. Baking your validation logic into these proprietary, tightly coupled, LINQ DTOs seems wrong.
Title: Create items together   
Name: Tom
Date: 2008-12-24 1:49:56 PM
Is there a way to create a category and product together? From what I've seen, beverages.Products would be null, so you can't call "Add" on it. You also can't set Products equal to a new EntitySet, because the setter assumes a non-null EntitySet already which it calls "Assign" on.

Title: Very helpful   
Name: Alexander
Date: 2008-11-13 9:20:30 PM
Just great. Killed a few days with msdn stuff trying to comprehend logic of Entity Insert/Update/Delete customisatiton. Just got it from this article in a couple of minutes.
Thank you.
Title: Linq doesn't update   
Name: Moez Tounsi
Date: 2008-10-07 12:15:53 PM
Following the example above, ling will add a new record with the new parameters.
We have to handle the deletion of the old element.
Title: Excellent   
Name: Wayne
Date: 2008-07-23 12:27:59 AM
Excellent article, Well done, a really good read.
Title: Thanks a lot   
Name: Andi
Date: 2007-12-02 11:25:10 AM
Thank you for this great article!!!

Community Advice: ASP | SQL | XML | Regular Expressions | Windows

©Copyright 1998-2019  |  Page Processed at 2019-03-24 5:43:22 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search