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

    So here is Linq-to-Sql. Our task is to find out which fields can have null values ​​and which cannot - solving this problem can, for example, help highlight required fields on a form, or just to validate the data before setting it in the properties of the object.
    Step one - create a simple table of the following form: Step two - create a project (for example, Console Application is enough). We add Model Linq-To-Sql to it, on which we add mapping to the LinqBugTable table, note that the price's Nullable property is set to False, it should be so, because this field is not nullable in the database: I won’t show the code for property yet price, but I will show for 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 from the ColumnAttribute of this property, we can find out when our field can have an empty value. We create a partial class for LinqBugTable, which will output information about its properties to us:
    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.
    Everything seems to be ready, but the result will be the following:

    Property: 'id', CanBeNull: 'True'
    Property: 'price', CanBeNull: 'True'
    Property: 'number', CanBeNull: 'False'


    As you can see for id and price we have True returns, 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 from the Column attribute, the CanBeNull property is not set, but, in general, bool variables are set to false, so maybe it's not scary (as I thought at first). It's good that there is a great Reflector program with which you can see what happens in the ColumnAttribute code: We see that in the constructor they initialize the field with true. In general, ColumnAttribute also has the CanBeNullSet property, which unfortunately is internal, with which it would be possible to find out whether the property is set or not. On reflection, I understood the logic: the price property returns a decimal type that simply cannot be null, and if I set the Nullable = True property in the Model Designer for this field, then it would be of the Nullable type


    . In general, for fields with ValueType types (structures), Linq-To-Sql does not generate a description associated with 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 the property is of type Nullable, then we return True, otherwise we will return the value CanBeNull taking into account the fact that the field type is not struct. Well, we hope that with ValueType property types are the only troubles that were in this scheme. ;)

    Also popular now: