.NET Char type to SQL CHAR(1) SQL Exception (Resolution)

Apr 8, 2010 at 10:00 PM


When attempting to compare a SQL char(1) column against a .net char type I kept receiving the error:
Conversion failed when converting the varchar value 'N' to data type int.

Here's my solution .... Please review and provide feedback if you have the time:
Table:
MyTable
TableId int
Code char(1)

Class:
MyCharClass
char Code

var arr = (from c in db.Coordinate where c.HorizIndicatorLf == 'Y' select c).AsEnumerable();

My solution (code change in SqlFormatter.Visit(Expression exp)

        protected override Expression Visit(Expression exp)
        {
            if (exp == null) return null;

            // check for supported node types first 
            // non-supported ones should not be visited (as they would produce bad SQL)
            switch (exp.NodeType)
            {

..........

                case ExpressionType.Convert:
		// convert char(1) to ASCII
		if (exp is UnaryExpression && ((UnaryExpression)exp).Operand is ColumnExpression)
		{
			ColumnExpression colExpression = ((UnaryExpression)exp).Operand as ColumnExpression;

			if (colExpression.Type == typeof(char))
			{
				this.Write(string.Format("ASCII({0})", colExpression.Name));
				return exp;
			}
		}
		return base.Visit(exp);