Simple Validation with LINQ to SQL Classes

Simple Validation with LINQ to SQL Classes

  • Comments 59

In the last few posts on LINQ to SQL I've showed how to set up an object model using the O/R designer and how to handle a couple data binding scenarios with Comboboxes here and here. Last post on this topic we implemented a one-to-many data entry form and I showed how to work with stored procs as well as how to properly configure delete behaviors. In this post I want to explore how to easily add validation rules to our LINQ to SQL classes and how we can get these rules automatically displayed in the UI.

LINQ to SQL Classes -- A Closer Look

Classes that implement the IDataErrorInfo interface in conjunction with INotifyPropertyChanged are able to automatically notify UI objects like the ErrorProvider and the DataGridView to display validation errors. LINQ to SQL classes that are generated for you when you create your object models already implement the INotifyPropertyChanged interface and using partial methods we can easily add validation to our classes. For this example we'll expand the One-to-Many data entry form we built to include some business rules.

First let's open up the generated LINQ to SQL classes by opening the .Designer.vb file under the dbml file (if you don't see the designer file, just click the "show all files" button on the Solution Explorer tool strip first). If we take a look at our Order class we will see the following class definition:

<Table(Name:="dbo.Orders")>  _
Partial Public Class [Order]
    Implements System.ComponentModel.INotifyPropertyChanging, _
System.ComponentModel.INotifyPropertyChanged

As you can see, this class is just a plain old CLR object (POCO) that implements interfaces that notify when properties are changing or changed on the class. That's it. It's the DataContext that does the heavy lifting, knowing what objects have changes, which ones were added and removed and how to persist these to the database. It does its simple mapping via the attributes on the class and it's properties. Here's the property for CustomerID on our Order class:

<Column(Storage:="_CustomerID", DbType:="Int NOT NULL", UpdateCheck:=UpdateCheck.Never)>  _
    Public Property CustomerID() As Integer
        Get
            Return Me._CustomerID
        End Get
        Set
            If ((Me._CustomerID = value)  _
                        = false) Then
                If Me._Customer.HasLoadedOrAssignedValue Then
                    Throw New System.Data.Linq.ForeignKeyReferenceAlreadyHasValueException
                End If
                Me.OnCustomerIDChanging(value)
                Me.SendPropertyChanging
                Me._CustomerID = value
                Me.SendPropertyChanged("CustomerID")
                Me.OnCustomerIDChanged
            End If
        End Set
    End Property

Partial Classes and Methods

In order to add validation and business rules we can add code to the OnCustomerIDChanging and OnCustomerIDChanged methods. But we don't add them here in this generated file, instead we add them into the Partial Class. This is possible because of a new feature in Visual Studio 2008 called Partial Methods. Partial Classes were introduced in Visual Studio 2005 as a way to extend generated classes with additional functionality by allowing you to split classes across physical files. You can add new methods or properties in order to extend a generated class easily by using the Partial Class keyword and then writing your own code. The compiler will "merge" these files into one class.

In Visual Studio 2008 we can go a step further using Partial methods. Instead of raising and handling private events, partial methods can be used instead as a better performing and cleaner alternative. They are declared by creating a private method with an empty body and decorating it with the Partial keyword. The method may then be "re-implemented" elsewhere within its containing class. If the method is implemented, then the compiler will redirect all calls to the partial method to the implementing method. If the method is not implemented in its containing class, then the compiler silently removes any calls to it from the program.

If we take a look at the generated LINQ to SQL Order class again you can see there is a region called "Extensibility Method Definitions" that contain Partial methods. There will be On...Changed and On...Changing partial methods here for each of the properties on the class.

#Region "Extensibility Method Definitions"
    Partial Private Sub OnLoaded()
    End Sub
    Partial Private Sub OnValidate(action As System.Data.Linq.ChangeAction)
    End Sub
    Partial Private Sub OnCreated()
    End Sub
    Partial Private Sub OnCustomerIDChanging(value As Integer)
    End Sub
    Partial Private Sub OnCustomerIDChanged()
    End Sub
.
.
.

Notice that the Changing and Changed methods are called at the appropriate times in each of the property setters. This allows us to write clean business rules on these properties in our partial class by defining the partial method. However if we don't write any code for these partial methods, the calls to them are removed from the IL. To access the partial class code open the model (dbml file) in the O/R designer, select a class, right-click on the class name and select "View Code". This will create a file named after your model where the partial class code that we write can reside.

So to write some business rules for our classes in this example we're going to place code in the On...Changing methods and then implement IDataErrorInfo so that we can have the UI automatically display the validation messages. To make it easier to implement IDataErrorInfo on all our LINQ to SQL classes I'm going to create a base class that we can inherit from called BaseBusiness.

Implementing IDataErrorInfo

IDataErrorInfo requires us to implement only two properties, one called Error and one default property Item that both return a string. Error is used to describe what is wrong with the entire object. For instance, if we are displaying this object in a row of a DataGridView this property indicates what error message appears on the row header. If you use DataSets, this corresponds to the DataRow.RowError property. The Item property is used to determine the error message for a specific property (or column) that is passed in. In order to collect these messages for each property on our object (i.e. column in our table) we can use a generic dictionary of strings and either add or remove messages from the dictionary depending on the validation rules. Here's an example implementation of IDataErrorInfo on our base class:

Imports System.ComponentModel

''' <summary>
''' Base class for our LINQ to SQL classes.
''' This class demonstrates one way to implement the IDataErrorInfo
'''  interface so that the ErrorProvider and DataGridView can display
'''  validation errors in the UI.
''' </summary>
''' <remarks></remarks>
Public Class BaseBusiness
    Implements IDataErrorInfo

    'This dictionary contains a list of our validation errors for each field
    Private validationErrors As New Dictionary(Of String, String)

    Protected Sub AddError(ByVal columnName As String, ByVal msg As String)
        If Not validationErrors.ContainsKey(columnName) Then
            validationErrors.Add(columnName, msg)
        End If
    End Sub

    Protected Sub RemoveError(ByVal columnName As String)
        If validationErrors.ContainsKey(columnName) Then
            validationErrors.Remove(columnName)
        End If
    End Sub

    Public Overridable ReadOnly Property HasErrors() As Boolean
        Get
            Return (validationErrors.Count > 0)
        End Get
    End Property

    Public ReadOnly Property [Error]() As String _
        Implements System.ComponentModel.IDataErrorInfo.Error
        Get
            If validationErrors.Count > 0 Then
                Return String.Format("{0} data is invalid.", TypeName(Me))
            Else
                Return Nothing
            End If
        End Get
    End Property

    Default Public ReadOnly Property Item(ByVal columnName As String) As String _
        Implements System.ComponentModel.IDataErrorInfo.Item
        Get
            If validationErrors.ContainsKey(columnName) Then
                Return validationErrors(columnName).ToString
            Else
                Return Nothing
            End If
        End Get
    End Property
End Class

Next we need to inherit from this class in all our LINQ to SQL Classes. Back in our partial class file we can inherit all our LINQ to SQL classes from BaseBusiness:

Partial Class Customer
    Inherits BaseBusiness

End Class

Partial Class Product
    Inherits BaseBusiness

End Class

Partial Class OrderDetail
    Inherits BaseBusiness

End Class

Partial Class Order
    Inherits BaseBusiness

End Class

Writing the Business Rules

Now we need to write our business rules into these classes. Let's take Order as an example. There are a couple rules I want to implement here:

  1. The OrderDate cannot be after the ShipDate
  2. The Customer must be specified

There are a couple places that we need to run these rules. One is when any of these fields change and the other is before the changes are submitted to the database. We need to handle both places because the user doesn't necessarily change all the properties and raise the On...Changing event. We need to handle the case when the user doesn't fill out a field and immediately attempts to save. This partial method is called OnValidate on our LINQ to SQL classes. This method is called automatically by the DataContext right before it attempts to submit the changes to the database when SubmitChanges is called.

Because you have to run the rules in both of these situations it's probably easier to create a private method that checks each rule and call that method from both Partial On...Changing and OnValidate methods. If the rule is broken then we just call AddError to add the error message to the dictionary, otherwise we call RemoveError to remove it from the dictionary. Here's the code for my Order Partial class:

Partial Class Order
    Inherits BaseBusiness

    Private Sub OnCustomerIDChanging(ByVal value As Integer)
        Me.CheckCustomerID(value)
    End Sub

    Private Sub OnOrderDateChanging(ByVal value As Date?)
        Me.CheckOrderDate(value)
    End Sub

    Private Sub OnShipDateChanging(ByVal value As Date?)
        Me.CheckShipDate(value)
    End Sub

    Private Sub OnValidate(ByVal action As System.Data.Linq.ChangeAction)
        Me.CheckCustomerID(Me.CustomerID)
        Me.CheckOrderDate(Me.OrderDate)
        Me.CheckShipDate(Me.OrderDate)

        If Me.HasErrors Then
            Throw New ValidationException(Me.Error)
        End If
    End Sub

    Private Sub CheckCustomerID(ByVal value As Integer)
        If value < 1 Then
            Me.AddError("CustomerID", "Customer cannot be empty.")
        Else
            Me.RemoveError("CustomerID")
        End If
    End Sub

    Private Sub CheckOrderDate(ByVal value As Date?)
        If value.HasValue Then
            If value > Me.ShipDate Then
                Me.AddError("OrderDate", "Order date cannot be after the ship date.")
            Else
                Me.RemoveError("OrderDate")
            End If
        End If
    End Sub

    Private Sub CheckShipDate(ByVal value As Date?)
        If value.HasValue Then
            If value < Me.OrderDate Then
                Me.AddError("ShipDate", "Ship date cannot be before the order date.")
            Else
                Me.RemoveError("ShipDate")
            End If
        End If
    End Sub

    Public Overrides ReadOnly Property HasErrors() As Boolean
        Get
            If Not MyBase.HasErrors Then
                'Returns True if any order details are invalid 
                For Each detail In Me.OrderDetails
                    If detail.HasErrors Then
                        Return True
                    End If
                Next
            End If
            Return MyBase.HasErrors
        End Get
    End Property

End Class

So here we are calling our validation methods from the On...Changing as well as the OnValidate partial methods. If you throw an exception from OnValidate then that will halt the SubmitChanges from going further. I created my own ValidationException class that we can use to check from our save code on the form. I also included any Order Detail errors in the HasErrors property of this Order class. This means that if any order details have errors then this Order also reports that HasErrors is True.

Displaying Validation Errors in the UI

Now that we have our business rules implemented we can hook up the UI to display these messages. Objects that are being displayed in a DataGridView will automatically pick up our validation messages but for simple controls like textboxes we need to hook up an ErrorProvider. Just drag the ErrorProvider from the toolbox onto your form and then specify the BindingSource as the DataSource property, for this example it's the OrderBindingSource. That's it. The ErrorProvider will look for any error messages on our objects through the IDataErrorInfo interface. This occurs when a property changes. For instance, if I run the form now and change the Order Date to be after the Ship Date, an error will be displayed as I tab off of the Order Date:

In order to display the messages after validation fails when we attempt to submit changes to the database, we need to write some code to refresh the display. In this scenario we also need to check the OrderBindingSource's position to make sure that the user is sitting on the invalid Order so the visuals are clear as to what needs fixing. This is why I created my own exception class called ValidationException and added a property to our BaseBusiness class called HasErrors, so that we could easily handle this case.

So back in our form we'll write the following Save code:

Private Sub OrderBindingNavigatorSaveItem_Click() _
    Handles OrderBindingNavigatorSaveItem.Click

    Me.Validate()
    Me.OrderBindingSource.EndEdit()
    Me.OrderDetailsBindingSource.EndEdit()

    Try
        db.SubmitChanges()

        MsgBox("Your data was saved.")

    Catch ex As ValidationException
Me.DisplayErrors() MsgBox("Please correct the errors on this form before saving.") Catch ex As Exception MsgBox(ex.ToString) End Try End Sub ''' <summary> ''' Displays any error information and navigates to the first error row ''' </summary> ''' <remarks></remarks> Private Sub DisplayErrors() Me.ErrorProvider1.UpdateBinding() Me.OrderDetailsDataGridView.Refresh()
    If Me.OrderBindingSource.Position > -1 Then
        Dim currentOrder As Order = CType(Me.OrderBindingSource.Current, Order)

        If Not currentOrder.HasErrors Then
            'The error is not in view so navigate to it
            For i = 0 To Me.OrderBindingSource.Count - 1
                Dim order As Order = CType(Me.OrderBindingSource(i), Order)
                If order.HasErrors Then
                    Me.OrderBindingSource.Position = i
                    Exit Sub
                End If
            Next
        End If
    End If
End Sub

To test this, add a new Order then navigate away from it. When you click Save the validation will fail and you will be positioned back on the order that failed.

I placed the code for this article (including the previous article code on this topic) into a Code Gallery project for you to play with.

Enjoy!

Leave a Comment
  • Please add 7 and 2 and type the answer here:
  • Post
  • Hi hip london,

    ScottGu has a lot of posts on this subject. Try this feed: http://weblogs.asp.net/scottgu/archive/tags/LINQ/ASP.NET/SQL+Server/default.aspx

    HTH,

    -B

  • The LINQ To SQL designer-generated classes contain 3 different ways to implement validation based on

  • Thanks for a super article.  One question though.  I want to put some business logic and rules into the classes linqtosql generated by using the partial 'changing' methods.  However, these methods also fire when the linqtosql object is being populated from the database.  Is there a way to work out in the 'changing' method wether or not the change of the property was set by the database or not?

  • Hi James,

    Take a look at the OnLoaded method. I think that is called when the database values are being populated. You may be able to set a flag in there.

    HTH,

    -B

  • Displaying Data Validation Messages in WPF

  • Beth, Very nice. I like but...

    In the code below you show how to validate the CustomerID. My question is where are you checking that the CustomerId is an integer? What if this is a text box field on a web page that allows the user enter anything they want? Are you depending on a validator on the web page to catch this?

       Private Sub CheckCustomerID(ByVal value As Integer)

           If value < 1 Then

               Me.AddError("CustomerID", "Customer cannot be empty.")

           Else

               Me.RemoveError("CustomerID")

           End If

       End Sub

  • Hi Mark,

    The CustomerID Property is defined as an Integer so by the time we get to this validator the property setter is running. You should restrict the input on the textbox or binding itself on the client as well.

    HTH,

    -B

  • Hi Beth,

    This really a fantastic guidelines for me as a beginner in N-Tier Application.

    At least, now, i'm able to view to whole picture of N-Tier Layer & neccessary code & objects involved.

    I can't go far just refering to MSDN guidelines.

    Thanks to you!

    -LC

  • Hello Beth,

    Thanks for a great manual, but i have a small problem: the OnChanging or any other events are never called from the Linq.dbml. I tried a simple test with OnLoaded (with a msgbox) and it doesnt show. Everything else though works fine. The data is loaded, I can change it, delete it or add new data. Does it have something to do with the fact that i have an SQLExpress database? Im desperate :S

  • Hi Beth!

    great article, but i been looking a way to validate duplicate data, I mean, when added a new record i want to check if is exist, it works (doing a query directly to the database trough linq). but what happen if i have a then thousands of records?, the performance goes down (i think) there is any way to do this directly trough the datacontext  because i'm little confuse with linq (i newbie with it) or just be a problem between the keyboard and the chair?

    thanks.. x)

  • Hi Rodrigo,

    As long as you have proper indexes set up on the database on the fields you are querying and you are also limiting the results with a Where clause, then the query will perform well. However you may want to validate them all in one query when SubmitChanges is called rather than everytime a new item is added.

    HTH,

    -B

  • Hi,

    I want the explanation of LINQ to SQL and how to use

    it in asp.net 2.0 programming wise..

    Have a Great day!.

    Thanks,

    Guna Sundari.

  • Hi Gunasundari,

    Check out ScottGu's blog posts on this topic:

    http://weblogs.asp.net/scottgu/archive/2007/08/27/linq-to-sql-part-8-executing-custom-sql-expressions.aspx

    or visit www.asp.net

    HTH,

    -B

  • Beth

    I am unable to get the error provider to show.

    Here is my scenario:

    I am using a user control, stored procs and multiple projects opposed to yours where you do it on one.  I am able to add errors to the validation exception but I am unable to get the error provider to show.

    Any insight?

  • Hi Matt,

    Check to make sure you set up your partial classes like I explained above. Call your validation code from the xxxChanging events and make sure you add the error strings to the dictionary (AddError) before xxxChanged events are fired. You'll also need to make sure that the ErrorProvider's datasource is set to the same BindingSource that your controls are bound to. If you are referencing the project that contains your partial LINQ to SQL types directly this should work fine. If you are building an n-tier application and have a service layer in between your client and data access layers you'll need to either write the validation on the client proxy types or share the LINQ to SQL types on the client and service projects. You can see this post for an example of how to share types on the client and server:

    http://blogs.msdn.com/bethmassi/archive/2008/04/14/linq-to-sql-n-tier-smart-client-part-2-building-the-client.aspx

    HTH,

    -B

Page 3 of 4 (59 items) 1234