SQLxD Part 18 — Expressions – Random IT Utensils

This is the eighteenth part of the SQLxD series. For your convenience you can find other parts in the table of contents in Part 1 – XML Transformation

Last time we saw tests for aggregates. Today we are going to implement expressions.

We can compare columns with different value types: numbers, dates, strings. However, it would be useful if we could first transform values in some manner, e.g., extract year part from date. This is the purpose of expressions. We already saw simple expressions working on rows when we were implementing comparing operators. Now let’s implement expressions working on columns:

using Model;

namespace QueryLogic.Expressions.CellExpressions
{
    public interface ICellExpression
    {
        ColumnHeader Source { get; }
        Cell Calculate(Row row);
    }
}

using Model;

namespace QueryLogic.Expressions.CellExpressions

{

public interface ICellExpression

{

ColumnHeader Source { get; }

Cell Calculate(Row row);

}

We can see that expression works on row and source column. Let’s implement identity expression:

using Model;

namespace QueryLogic.Expressions.CellExpressions
{
    public class GetOriginalCellCellExpression : ICellExpression
    {
        public ColumnHeader Source { get; private set; }

        public GetOriginalCellCellExpression(ColumnHeader source)
        {
            Source = source;
        }
        public Cell Calculate(Row row)
        {
            var cell = row.GetCell(Source);
            return new Cell(cell.ColumnHeader, cell.Value);
        }

        protected bool Equals(GetOriginalCellCellExpression other)
        {
            return Equals(Source, other.Source);
        }

        public override bool Equals(object obj)
        {
            if (ReferenceEquals(null, obj)) return false;
            if (ReferenceEquals(this, obj)) return true;
            if (obj.GetType() != GetType()) return false;
            return Equals((GetOriginalCellCellExpression) obj);
        }

        public override int GetHashCode()
        {
            return (Source != null ? Source.GetHashCode() : 0);
        }
    }
}

using Model;

namespace QueryLogic.Expressions.CellExpressions

{

public class GetOriginalCellCellExpression : ICellExpression

{

public ColumnHeader Source { get; private set; }

public GetOriginalCellCellExpression(ColumnHeader source)

{

Source = source;

}

public Cell Calculate(Row row)

{

var cell = row.GetCell(Source);

return new Cell(cell.ColumnHeader, cell.Value);

}

protected bool Equals(GetOriginalCellCellExpression other)

{

return Equals(Source, other.Source);

}

public override bool Equals(object obj)

{

if (ReferenceEquals(null, obj)) return false;

if (ReferenceEquals(this, obj)) return true;

if (obj.GetType() != GetType()) return false;

return Equals((GetOriginalCellCellExpression) obj);

}

public override int GetHashCode()

{

return (Source != null ? Source.GetHashCode() : 0);

}

Now let’s implement first expression doing something more sophisticated:

namespace QueryLogic.Expressions.CellExpressions
{
    public enum DatePart
    {
        Year,
        Month,
        Day,
        Hour,
        Minute,
        Second
    }
}

namespace QueryLogic.Expressions.CellExpressions

{

public enum DatePart

{

Year,

Month,

Day,

Hour,

Minute,

Second

}

using System;
using System.Globalization;
using Model;

namespace QueryLogic.Expressions.CellExpressions
{
    public class GetDatePartCellExpression : ICellExpression
    {
        private readonly DatePart _part;

        public GetDatePartCellExpression(ColumnHeader source, DatePart part)
        {
            _part = part;
            Source = source;
        }

        public ColumnHeader Source { get; private set; }

        public Cell Calculate(Row row)
        {
            Cell cell = row.GetCell(Source);
            if (cell.GetTypeCode() != TypeCode.DateTime)
            {
                throw new InvalidOperationException("Value is not date");
            }

            DateTime time = Convert.ToDateTime(cell);
            string value;
            switch (_part)
            {
                case DatePart.Year:
                    value = time.Year.ToString(CultureInfo.InvariantCulture);
                    break;
                case DatePart.Month:
                    value = time.Month.ToString(CultureInfo.InvariantCulture);
                    break;
                case DatePart.Day:
                    value = time.Day.ToString(CultureInfo.InvariantCulture);
                    break;
                case DatePart.Hour:
                    value = time.Hour.ToString(CultureInfo.InvariantCulture);
                    break;
                case DatePart.Minute:
                    value = time.Minute.ToString(CultureInfo.InvariantCulture);
                    break;
                case DatePart.Second:
                    value = time.Second.ToString(CultureInfo.InvariantCulture);
                    break;
                default:
                    throw new ArgumentOutOfRangeException();
            }

            return new Cell(cell.ColumnHeader, value);
        }

        protected bool Equals(GetDatePartCellExpression other)
        {
            return _part == other._part && Equals(Source, other.Source);
        }

        public override bool Equals(object obj)
        {
            if (ReferenceEquals(null, obj)) return false;
            if (ReferenceEquals(this, obj)) return true;
            if (obj.GetType() != GetType()) return false;
            return Equals((GetDatePartCellExpression)obj);
        }

        public override int GetHashCode()
        {
            unchecked
            {
                return ((int)_part * 397) ^ (Source != null ? Source.GetHashCode() : 0);
            }
        }
    }
}

using System;

using System.Globalization;

using Model;

namespace QueryLogic.Expressions.CellExpressions

{

public class GetDatePartCellExpression : ICellExpression

{

private readonly DatePart _part;

public GetDatePartCellExpression(ColumnHeader source, DatePart part)

{

_part = part;

Source = source;

}

public ColumnHeader Source { get; private set; }

public Cell Calculate(Row row)

{

Cell cell = row.GetCell(Source);

if (cell.GetTypeCode() != TypeCode.DateTime)

{

throw new InvalidOperationException("Value is not date");

}

DateTime time = Convert.ToDateTime(cell);

string value;

switch (_part)

{

case DatePart.Year:

value = time.Year.ToString(CultureInfo.InvariantCulture);

break;

case DatePart.Month:

value = time.Month.ToString(CultureInfo.InvariantCulture);

break;

case DatePart.Day:

value = time.Day.ToString(CultureInfo.InvariantCulture);

break;

case DatePart.Hour:

value = time.Hour.ToString(CultureInfo.InvariantCulture);

break;

case DatePart.Minute:

value = time.Minute.ToString(CultureInfo.InvariantCulture);

break;

case DatePart.Second:

value = time.Second.ToString(CultureInfo.InvariantCulture);

break;

default:

throw new ArgumentOutOfRangeException();

}

return new Cell(cell.ColumnHeader, value);

}

protected bool Equals(GetDatePartCellExpression other)

{

return _part == other._part && Equals(Source, other.Source);

}

public override bool Equals(object obj)

{

if (ReferenceEquals(null, obj)) return false;

if (ReferenceEquals(this, obj)) return true;

if (obj.GetType() != GetType()) return false;

return Equals((GetDatePartCellExpression)obj);

}

public override int GetHashCode()

{

unchecked

{

return ((int)_part * 397) ^ (Source != null ? Source.GetHashCode() : 0);

}

We first extract cell and then calculate date part. In this manner we can implement other expressions:

using Model;

namespace QueryLogic.Expressions.CellExpressions
{
    public class CatenateStringCellExpression : ICellExpression
    {
        private readonly string _suffix;

        public CatenateStringCellExpression(ColumnHeader source, string suffix)
        {
            _suffix = suffix;
            Source = source;
        }

        public ColumnHeader Source { get; private set; }

        public Cell Calculate(Row row)
        {
            Cell cell = row.GetCell(Source);

            return new Cell(cell.ColumnHeader, (cell.Value ?? "") + _suffix);
        }

        protected bool Equals(CatenateStringCellExpression other)
        {
            return string.Equals(_suffix, other._suffix) && Equals(Source, other.Source);
        }

        public override bool Equals(object obj)
        {
            if (ReferenceEquals(null, obj)) return false;
            if (ReferenceEquals(this, obj)) return true;
            if (obj.GetType() != GetType()) return false;
            return Equals((CatenateStringCellExpression)obj);
        }

        public override int GetHashCode()
        {
            unchecked
            {
                return ((_suffix != null ? _suffix.GetHashCode() : 0) * 397) ^ (Source != null ? Source.GetHashCode() : 0);
            }
        }
    }
}

using Model;

namespace QueryLogic.Expressions.CellExpressions

{

public class CatenateStringCellExpression : ICellExpression

{

private readonly string _suffix;

public CatenateStringCellExpression(ColumnHeader source, string suffix)

{

_suffix = suffix;

Source = source;

}

public ColumnHeader Source { get; private set; }

public Cell Calculate(Row row)

{

Cell cell = row.GetCell(Source);

return new Cell(cell.ColumnHeader, (cell.Value ?? "") + _suffix);

}

protected bool Equals(CatenateStringCellExpression other)

{

return string.Equals(_suffix, other._suffix) && Equals(Source, other.Source);

}

public override bool Equals(object obj)

{

if (ReferenceEquals(null, obj)) return false;

if (ReferenceEquals(this, obj)) return true;

if (obj.GetType() != GetType()) return false;

return Equals((CatenateStringCellExpression)obj);

}

public override int GetHashCode()

{

unchecked

{

return ((_suffix != null ? _suffix.GetHashCode() : 0) * 397) ^ (Source != null ? Source.GetHashCode() : 0);

}

using System.Globalization;
using Model;

namespace QueryLogic.Expressions.CellExpressions
{
    public class GetLengthCellExpression : ICellExpression
    {
        public GetLengthCellExpression(ColumnHeader source)
        {
            Source = source;
        }

        public ColumnHeader Source { get; private set; }

        public Cell Calculate(Row row)
        {
            Cell cell = row.GetCell(Source);
            return (cell.Value == null)
                ? new Cell(cell.ColumnHeader, null)
                : new Cell(cell.ColumnHeader, cell.Value.Length.ToString(CultureInfo.InvariantCulture));
        }

        protected bool Equals(GetLengthCellExpression other)
        {
            return Equals(Source, other.Source);
        }

        public override bool Equals(object obj)
        {
            if (ReferenceEquals(null, obj)) return false;
            if (ReferenceEquals(this, obj)) return true;
            if (obj.GetType() != GetType()) return false;
            return Equals((GetLengthCellExpression)obj);
        }

        public override int GetHashCode()
        {
            return (Source != null ? Source.GetHashCode() : 0);
        }
    }
}

using System.Globalization;

using Model;

namespace QueryLogic.Expressions.CellExpressions

{

public class GetLengthCellExpression : ICellExpression

{

public GetLengthCellExpression(ColumnHeader source)

{

Source = source;

}

public ColumnHeader Source { get; private set; }

public Cell Calculate(Row row)

{

Cell cell = row.GetCell(Source);

return (cell.Value == null)

? new Cell(cell.ColumnHeader, null)

: new Cell(cell.ColumnHeader, cell.Value.Length.ToString(CultureInfo.InvariantCulture));

}

protected bool Equals(GetLengthCellExpression other)

{

return Equals(Source, other.Source);

}

public override bool Equals(object obj)

{

if (ReferenceEquals(null, obj)) return false;

if (ReferenceEquals(this, obj)) return true;

if (obj.GetType() != GetType()) return false;

return Equals((GetLengthCellExpression)obj);

}

public override int GetHashCode()

{

return (Source != null ? Source.GetHashCode() : 0);

}

using Model;

namespace QueryLogic.Expressions.CellExpressions
{
    public class GetSubstringCellExpression : ICellExpression
    {
        public GetSubstringCellExpression(ColumnHeader source, int startIndex, int length)
        {
            Source = source;
            StartIndex = startIndex;
            Length = length;
        }

        public Cell Calculate(Row row)
        {
            var cell = row.GetCell(Source);
            return (cell.Value == null)
                ? new Cell(cell.ColumnHeader, null)
                : new Cell(cell.ColumnHeader, cell.Value.Substring(StartIndex, Length));
        }

        public int Length { get; private set; }

        protected bool Equals(GetSubstringCellExpression other)
        {
            return Length == other.Length && Equals(Source, other.Source) && StartIndex == other.StartIndex;
        }

        public override bool Equals(object obj)
        {
            if (ReferenceEquals(null, obj)) return false;
            if (ReferenceEquals(this, obj)) return true;
            if (obj.GetType() != this.GetType()) return false;
            return Equals((GetSubstringCellExpression) obj);
        }

        public override int GetHashCode()
        {
            unchecked
            {
                int hashCode = Length;
                hashCode = (hashCode*397) ^ (Source != null ? Source.GetHashCode() : 0);
                hashCode = (hashCode*397) ^ StartIndex;
                return hashCode;
            }
        }

        public ColumnHeader Source { get; private set; }
        public int StartIndex { get; private set; }
    }
}

using Model;

namespace QueryLogic.Expressions.CellExpressions

{

public class GetSubstringCellExpression : ICellExpression

{

public GetSubstringCellExpression(ColumnHeader source, int startIndex, int length)

{

Source = source;

StartIndex = startIndex;

Length = length;

}

public Cell Calculate(Row row)

{

var cell = row.GetCell(Source);

return (cell.Value == null)

? new Cell(cell.ColumnHeader, null)

: new Cell(cell.ColumnHeader, cell.Value.Substring(StartIndex, Length));

}

public int Length { get; private set; }

protected bool Equals(GetSubstringCellExpression other)

{

return Length == other.Length && Equals(Source, other.Source) && StartIndex == other.StartIndex;

}

public override bool Equals(object obj)

{

if (ReferenceEquals(null, obj)) return false;

if (ReferenceEquals(this, obj)) return true;

if (obj.GetType() != this.GetType()) return false;

return Equals((GetSubstringCellExpression) obj);

}

public override int GetHashCode()

{

unchecked

{

int hashCode = Length;

hashCode = (hashCode*397) ^ (Source != null ? Source.GetHashCode() : 0);

hashCode = (hashCode*397) ^ StartIndex;

return hashCode;

}

public ColumnHeader Source { get; private set; }

public int StartIndex { get; private set; }

}

using Model;

namespace QueryLogic.Expressions.CellExpressions
{
    public class GetTrimmedCellExpression : ICellExpression
    {
        public GetTrimmedCellExpression(ColumnHeader source)
        {
            Source = source;
        }

        public ColumnHeader Source { get; private set; }

        public Cell Calculate(Row row)
        {
            Cell cell = row.GetCell(Source);
            return (cell.Value == null)
                ? new Cell(cell.ColumnHeader, null)
                : new Cell(cell.ColumnHeader, cell.Value.Trim());
        }

        protected bool Equals(GetTrimmedCellExpression other)
        {
            return Equals(Source, other.Source);
        }

        public override bool Equals(object obj)
        {
            if (ReferenceEquals(null, obj)) return false;
            if (ReferenceEquals(this, obj)) return true;
            if (obj.GetType() != GetType()) return false;
            return Equals((GetTrimmedCellExpression)obj);
        }

        public override int GetHashCode()
        {
            return (Source != null ? Source.GetHashCode() : 0);
        }
    }
}

using Model;

namespace QueryLogic.Expressions.CellExpressions

{

public class GetTrimmedCellExpression : ICellExpression

{

public GetTrimmedCellExpression(ColumnHeader source)

{

Source = source;

}

public ColumnHeader Source { get; private set; }

public Cell Calculate(Row row)

{

Cell cell = row.GetCell(Source);

return (cell.Value == null)

? new Cell(cell.ColumnHeader, null)

: new Cell(cell.ColumnHeader, cell.Value.Trim());

}

protected bool Equals(GetTrimmedCellExpression other)

{

return Equals(Source, other.Source);

}

public override bool Equals(object obj)

{

if (ReferenceEquals(null, obj)) return false;

if (ReferenceEquals(this, obj)) return true;

if (obj.GetType() != GetType()) return false;

return Equals((GetTrimmedCellExpression)obj);

}

public override int GetHashCode()

{

return (Source != null ? Source.GetHashCode() : 0);

}

Since we might want to chain expressions, let’s implement this;

using Model;

namespace QueryLogic.Expressions.CellExpressions
{
    public class ChainedCellExpression : ICellExpression
    {
        public ChainedCellExpression(ICellExpression leftCellExpression, ICellExpression rightCellExpression)
        {
            LeftCellExpression = leftCellExpression;
            RightCellExpression = rightCellExpression;
        }

        public ICellExpression LeftCellExpression { get; private set; }

        public ICellExpression RightCellExpression { get; private set; }

        public ColumnHeader Source
        {
            get
            {
                return LeftCellExpression.Source;
            }
        }

        public Cell Calculate(Row row)
        {
            return LeftCellExpression.Calculate(new Row(new[] { RightCellExpression.Calculate(row) }));
        }

        protected bool Equals(ChainedCellExpression other)
        {
            return Equals(Source, other.Source) && Equals(LeftCellExpression, other.LeftCellExpression) &&
                   Equals(RightCellExpression, other.RightCellExpression);
        }

        public override bool Equals(object obj)
        {
            if (ReferenceEquals(null, obj)) return false;
            if (ReferenceEquals(this, obj)) return true;
            if (obj.GetType() != GetType()) return false;
            return Equals((ChainedCellExpression)obj);
        }

        public override int GetHashCode()
        {
            unchecked
            {
                int hashCode = (Source != null ? Source.GetHashCode() : 0);
                hashCode = (hashCode * 397) ^ (LeftCellExpression != null ? LeftCellExpression.GetHashCode() : 0);
                hashCode = (hashCode * 397) ^ (RightCellExpression != null ? RightCellExpression.GetHashCode() : 0);
                return hashCode;
            }
        }
    }
}

using Model;

namespace QueryLogic.Expressions.CellExpressions

{

public class ChainedCellExpression : ICellExpression

{

public ChainedCellExpression(ICellExpression leftCellExpression, ICellExpression rightCellExpression)

{

LeftCellExpression = leftCellExpression;

RightCellExpression = rightCellExpression;

}

public ICellExpression LeftCellExpression { get; private set; }

public ICellExpression RightCellExpression { get; private set; }

public ColumnHeader Source

{

get

{

return LeftCellExpression.Source;

}

public Cell Calculate(Row row)

{

return LeftCellExpression.Calculate(new Row(new[] { RightCellExpression.Calculate(row) }));

}

protected bool Equals(ChainedCellExpression other)

{

return Equals(Source, other.Source) && Equals(LeftCellExpression, other.LeftCellExpression) &&

Equals(RightCellExpression, other.RightCellExpression);

}

public override bool Equals(object obj)

{

if (ReferenceEquals(null, obj)) return false;

if (ReferenceEquals(this, obj)) return true;

if (obj.GetType() != GetType()) return false;

return Equals((ChainedCellExpression)obj);

}

public override int GetHashCode()

{

unchecked

{

int hashCode = (Source != null ? Source.GetHashCode() : 0);

hashCode = (hashCode * 397) ^ (LeftCellExpression != null ? LeftCellExpression.GetHashCode() : 0);

hashCode = (hashCode * 397) ^ (RightCellExpression != null ? RightCellExpression.GetHashCode() : 0);

return hashCode;

}

And this is it. Next time we will test expressions.