Limiting LINQ String Field Lengths

We recently had an issue where a string value in our LINQ object was changed and became longer than the database field it represented. Whenever we tried to apply the changes to the database we would get an error. Now, i know that the correct solution is to implement the proper checks earlier on in the change cycle but it just erks me a little that the LINQ object doesn’t say: “Hey, you, coder… you can’t set a value that long to this property because it’s too long” when it clearly know what it is.

If you look at the definition of one of the Columns you will see that it has an Attribute of type ColumnAttribute. This attribute stores all the information required to map the field back to the database.

[Column(Storage="_AColumn", DbType="VarChar(50) NOT NULL", CanBeNull=false)]
public string AColumn
{
	get
	{
		return this._AColumn;
	}
	set
	{
		if ((this._AColumn != value))
		{
			this.OnNameChanging(value);
			this.SendPropertyChanging();
			this._AColumn = value;
			this.SendPropertyChanged("AColumn");
			this.OnNameChanged();
		}
	}
}

We can clearly see from this that our LINQ object does know the column length so i decide to write a routine to trim off the excess fat. The routine, when we SubmitChanges during OnValidate, uses reflection to get the field length and trim the value to fit. Now this could also easily be used to notify you of fields that are too long as well.

To do this we have to extend our LINQ Data Context. The actual DataContext’s name is MyDataContext. So we will name the extended one MyDataContext2… creative huh? We then enumerate through the class’ Properties and then each Properties Column attributes.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Web;
using System.Reflection;
using System.Data.Linq.Mapping;

public class MyDataContext2 : MyDataContextDataContext
{
	public MyDataContext2 ()
		: base(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString)
	{
	}
}    

public partial class MyTable
{
	partial void OnValidate(System.Data.Linq.ChangeAction action)
	{
		//Trim all the string fields so that we don't get DB errors.

		//Check each property in the table
		foreach (MemberInfo memInfo in (typeof(MyTable)).GetProperties())
		{
			//Only Loop through Column attributes
			foreach (object attribute in memInfo.GetCustomAttributes(typeof(ColumnAttribute), true))
			{
				ColumnAttribute ca = (ColumnAttribute)attribute;
				PropertyInfo propInfo = (PropertyInfo)memInfo;

				//Only limit varchar values
				if (ca.DbType.ToLower().Contains("varchar"))
				{
					string dbType = ca.DbType.ToLower();
					string varchar = "varchar(";
					int noStart = dbType.ToLower().IndexOf(varchar) + varchar.Length;
					int noEnd = dbType.IndexOf(")", noStart);

					string sLength = dbType.Substring(noStart, noEnd - noStart); //strings are stored as VarChar(XXX) NOT NULL

					int iLength = 0;
					int.TryParse(sLength, out iLength);

					string value = string.Empty;

					if (propInfo.GetValue(this, null) != null)
						value = propInfo.GetValue(this, null).ToString();

					if (value.Length > iLength)
						propInfo.SetValue(this, value.Substring(0, iLength), null);
				}
			}
		}
	}
}

Related posts:

1. Linq to SQL select and update oddity
2. Linq – SubmitChanges does not work
3. CSV parsing – the easy way!
4. Asp.Net MVC convert View to Word Document
5. Sharepoint – Getting Photos from People and Groups