Linq-to-Sql: Recognize nullable fields from metadata (or a story about a small bug)

So, before us Linq-to-Sql. We are faced with the task of finding out which fields can have null values ​​and which are not - solving this problem can, for example, help in highlighting mandatory fields on a form, or just to validate data before setting them into object properties.

Step one - create a simple table of the following form:



Step two - create a project (for an example, the Console Application will suffice). We add Model Linq-To-Sql to it, to which we add mapping to the LinqBugTable table, I note that the price of the Nullable property is set to False, it should be so, because this field is not nullable in the database:



I will not yet show the code for the property price, but I will show for the number:

[Column(Storage= "_number" , DbType= "VarChar(50) NOT NULL" , CanBeNull= false )]

public string number

{ get { .... } set { .... } }



* This source code was highlighted with Source Code Highlighter .

This shows that we can find out from the ColumnAttribute of this property when our field can be null. Create a partial class for LinqBugTable, which will display the information about its properties:

partial class LinqBugTable

{

public void WriteNullableInfo()

{

foreach (PropertyInfo property in GetType().GetProperties())

{

foreach (ColumnAttribute columnAttribute in property.GetCustomAttributes( typeof (ColumnAttribute), true ))

{

Console .WriteLine( "Property: '{0}', CanBeNull: '{1}'" , property.Name, columnAttribute.CanBeNull);

}

}

}

}



* This source code was highlighted with Source Code Highlighter .

It seems everything is ready, but the result will be as follows:



Property: 'id', CanBeNull: 'True'

Property: 'price', CanBeNull: 'True'

Property: 'number', CanBeNull: 'False'



As you can see for id and price we return True, although this, of course, is not so. Now let's look at the description of the price property in the class:

[Column(Storage= "_price" , DbType= "Money NOT NULL" )]

public decimal price

{ get { .... } set { .... } }



* This source code was highlighted with Source Code Highlighter .

As you can see, the CanBeNull property of the Column attribute is not set, but, in general, bool variables are set to false, so maybe this is not a problem (I thought at first). It is good that there is a wonderful Reflector program with which you can see what happens in the ColumnAttribute code:



We see that in the constructor they initialize the field to true. In general, the ColumnAttribute is still the CanBeNullSet property, which is unfortunately internal, with which it would be possible to know whether the property is set or not.

After thinking about it, I understood the logic: the price property returns the decimal type, which simply cannot be null, and if I set the Nullable = True property in the Model Designer of this field, then it would have the Nullable <decimal> type. In general, for fields with ValueType types (structures), Linq-To-Sql does not generate a description related to CanBeNull. Bottom line, we rewrite our method a bit:

public void WriteNullableInfo()

{

foreach (PropertyInfo property in GetType().GetProperties())

{

foreach (ColumnAttribute columnAttribute in property.GetCustomAttributes( typeof (ColumnAttribute), true ))

{

bool canBeNull = (Nullable.GetUnderlyingType(property.PropertyType) != null )

|| (columnAttribute.CanBeNull && !property.PropertyType.IsValueType);

Console .WriteLine( "Property: '{0}', CanBeNull: '{1}'" , property.Name, canBeNull);

}

}

}



* This source code was highlighted with Source Code Highlighter .

Now we check that if we have a property of type Nullable <T>, then we will return True, otherwise we will return the value CanBeNull, taking into account that the field type is not a struct. Well, we hope that with the property types of ValueType - this is the only trouble that was in this scheme. ;)