Rocks Thoughts

LINQ TO SQL CanBeNull being ignored

<Column(Storage:="FirstName", DbType:="VarChar(50) NOT NULL", CanBeNull:=false)>)
Public Property FirstName As String
....

Without thinking about it too much what do you think this attribute does programmatically? If you guessed NOTHING then you're correct.

You see I was under the impression that LINQ TO SQL would prevent null values being sent to the database if the "CanBeNull" attribute was set to true. I was wrong. Maybe a little exception being thrown before sending the error proned SQL to the database would have been nice. Well, that didn't happen SO I created a method to do what LINQ TO SQL doesn't.

Simply pass in the object instance of the LINQ TO SQL generated class (not the datacontext, the class based on your table  i.e. User) and you'll get an exception if one of your string values is null. I check for empty as well but that's up to you to keep.

Imports System.Reflection
Imports System.Data.Linq

Public Class DataContextHelper
    Public Shared Sub ValidateCanBeNullColumns(ByVal instance As Object)
        Dim properties() As PropertyInfo = instance.GetType().GetProperties()
        
        If (properties.Length > 0) Then
            For Each propertyObject As PropertyInfo In properties
                For Each attributeObject As Object In propertyObject.GetCustomAttributes(False)

                    If attributeObject.GetType() Is GetType(Mapping.ColumnAttribute) Then
                        Dim columnAttributeObject As Mapping.ColumnAttribute = CType(attributeObject, Mapping.ColumnAttribute)
                        If (columnAttributeObject.CanBeNull) Then Continue For


                        Dim propertyValue As Object = propertyObject.GetValue(instance, Nothing)
                        If propertyValue Is Nothing OrElse propertyValue.ToString.Length = 0 Then
                            Throw New DataContextColumnInvalidException(String.Format("The property '{0}' cannot be null or empty", propertyObject.Name))
                        End If
                    End If

                Next
            Next
        End If
    End Sub
End Class

This method is using reflection to iterate over the properties of a generated LINQ TO SQL class and looking for a ColumnAttribute. If a ColumnAttribute is found, then we check the ColumnAttribute.CanBeNull property for true or false. If the value is true then we continue to the next property in the class. If the value is false we check the propertyvalue variable for null or empty. If the value IS null or empty we throw an exception. The exception class is below

Public Class DataContextColumnInvalidException
    Inherits System.ApplicationException

    Public Sub New(ByVal message As String)
        MyBase.New(message)
    End Sub
End Class

The best way to use this is by implementing a method that is fired when an insert is attempted on a LINQ TO SQL class. This is explained by Scott Guthrie on this page under the "Custom Entity Insert/Update/Delete Method Validation" section.

In a nutsshell, your data context class will ALREADY have a method defined under the "Extensibility Method Definitions" region:

#Region "Extensibility Method Definitions"
 
Partial Private Sub InsertUser(instance as User)

End Sub
...

#End Region

 You're going to create a partial class and “override” the partial method InsertUser (we're assuming the name of your context class is MyDataContext). 

Partial Class MyDataContext
     Private Sub InsertUser(instance as User)
          DataContextHelper.ValidateCanBeNullColumns(instance)
          Me.ExecuteDynamicInsert(instance)
     End Sub
End Class

All you do is call "ValidateCanBeNullColumns" before calling Me.ExecuteDynamicInsert and you'll get an error if a null value is detected.

Maybe an option to throw an error in v 1.5 Of LINQ TO SQL? That would be nice...

Technorati Tags: LINQ,LINQ TO SQL,.NET 3.5

posted @ Friday, February 22, 2008 12:58 AM