This is the seventh 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 how to handle FROM clause. Today we are going to implement filtering logic for handling WHERE.

We start with the clause logic:

using System.Linq;
using Model;
using QueryLogic.Predicates;
using QueryLogic.RelationProviding;

namespace QueryLogic.Filtering
{
    public class Where : IRelationProvider
    {
        public Where(IRelationProvider sourceRelation, IPredicate predicate)
        {
            SourceRelation = sourceRelation;
            Predicate = predicate;
        }

        public IPredicate Predicate { get; private set; }
        public IRelationProvider SourceRelation { get; private set; }

        public Relation CreateRelation(Node source)
        {
            var result = new Relation();

            foreach (Row row in SourceRelation.CreateRelation(source).Rows.Where(row => Predicate.KeepRow(row)))
            {
                result.AddRow(row);
            }

            return result;
        }

        protected bool Equals(Where other)
        {
            return Equals(Predicate, other.Predicate) && Equals(SourceRelation, other.SourceRelation);
        }

        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((Where)obj);
        }

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

As we can see, we simply iterate over rows, execute predicate and keep or remove row. That’s all, the most important stuff is hidden in IPredicate.

Predicate interface is very short:

using Model;

namespace QueryLogic.Predicates
{
    public interface IPredicate
    {
        bool KeepRow(Row row);
    }
}

Now come implementations. We start with abstract class for comparing predicates (e.g., less or equal predicate):

using System;
using Model;
using QueryLogic.Expressions.RowExpressions;

namespace QueryLogic.Predicates.Simple
{
    public abstract class AbstractCompareToPredicate : IPredicate
    {
        protected AbstractCompareToPredicate(IRowExpression left, IRowExpression right)
        {
            Left = left;
            Right = right;
        }

        public IRowExpression Left { get; private set; }
        public IRowExpression Right { get; private set; }

        public bool KeepRow(Row row)
        {
            Cell leftCell = Left.Calculate(row);
            Cell rightCell = Right.Calculate(row);

            if (leftCell == null || rightCell == null)
            {
                return false;
            }

            TypeCode leftTypeCode = leftCell.GetTypeCode();
            TypeCode rightTypeCode = rightCell.GetTypeCode();

            if (leftTypeCode != rightTypeCode)
            {
                return ComparisonFunction(CompareAsStrings(leftCell, rightCell));
            }

            bool result = ComparisonFunction(CompareDependingOnTypeCode(leftCell, rightCell, leftTypeCode));
            return result;
        }

        protected bool Equals(AbstractCompareToPredicate other)
        {
            return Equals(Left, other.Left) && Equals(Right, other.Right);
        }

        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((AbstractCompareToPredicate)obj);
        }

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

        protected abstract bool ComparisonFunction(int compareToResult);

        private int CompareDependingOnTypeCode(Cell leftCell, Cell rightCell, TypeCode typeCode)
        {
            int compareToResult;

            switch (typeCode)
            {
                case TypeCode.Double:
                    compareToResult = CompareAsDoubles(leftCell, rightCell);
                    break;
                case TypeCode.DateTime:
                    compareToResult = CompareAsDates(leftCell, rightCell);
                    break;
                case TypeCode.String:
                    compareToResult = CompareAsStrings(leftCell, rightCell);
                    break;
                default:
                    compareToResult = CompareAsStrings(leftCell, rightCell);
                    break;
            }

            return compareToResult;
        }

        private int CompareAsStrings(Cell leftCell, Cell rightCell)
        {
            string leftValue = leftCell.Value;
            string rightValue = rightCell.Value;

            int compareToResult = String.Compare(leftValue, rightValue, StringComparison.Ordinal);
            return compareToResult;
        }

        private int CompareAsDoubles(Cell leftCell, Cell rightCell)
        {
            double leftValue = Convert.ToDouble(leftCell);
            double rightValue = Convert.ToDouble(rightCell);

            int compareToResult = leftValue.CompareTo(rightValue);
            return compareToResult;
        }

        private int CompareAsDates(Cell leftCell, Cell rightCell)
        {
            DateTime leftValue = Convert.ToDateTime(leftCell);
            DateTime rightValue = Convert.ToDateTime(rightCell);

            int compareToResult = leftValue.CompareTo(rightValue);
            return compareToResult;
        }
    }
}

We can see that predicate works on expressions. What is that? Well, we need to be able to extract value from row or create constant. For this we have these expressions:

using Model;

namespace QueryLogic.Expressions.RowExpressions
{
    public interface IRowExpression
    {
        Cell Calculate(Row row);
    }
}
using Model;
using QueryLogic.Expressions.CellExpressions;

namespace QueryLogic.Expressions.RowExpressions
{
    public class GetCellRowExpression : IRowExpression
    {
        public GetCellRowExpression(ColumnHeader columnHeader, ICellExpression cellExpression = null)
        {
            ColumnHeader = columnHeader;
            CellExpression = cellExpression ?? new GetOriginalCellCellExpression(columnHeader);
        }

        public ICellExpression CellExpression { get; private set; }
        public ColumnHeader ColumnHeader { get; private set; }

        public Cell Calculate(Row row)
        {
            return CellExpression.Calculate(row);
        }

        protected bool Equals(GetCellRowExpression other)
        {
            return Equals(CellExpression, other.CellExpression) && Equals(ColumnHeader, other.ColumnHeader);
        }

        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((GetCellRowExpression)obj);
        }

        public override int GetHashCode()
        {
            unchecked
            {
                return ((CellExpression != null ? CellExpression.GetHashCode() : 0) * 397) ^
                       (ColumnHeader != null ? ColumnHeader.GetHashCode() : 0);
            }
        }
    }
}
using Model;

namespace QueryLogic.Expressions.RowExpressions
{
    public class ConstantRowExpression : IRowExpression
    {
        public ConstantRowExpression(string value)
        {
            ConstCell = new Cell(new ColumnHeader("", ""), value);
        }

        public Cell ConstCell { get; private set; }

        public Cell Calculate(Row row)
        {
            return ConstCell;
        }

        protected bool Equals(ConstantRowExpression other)
        {
            return Equals(ConstCell, other.ConstCell);
        }

        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((ConstantRowExpression)obj);
        }

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

Having this code we can easily implement multiple predicates:

using QueryLogic.Expressions.RowExpressions;

namespace QueryLogic.Predicates.Simple
{
    public class GreaterEqualPredicate : AbstractCompareToPredicate
    {
        public GreaterEqualPredicate(IRowExpression left, IRowExpression right)
            : base(left, right)
        {
        }

        protected bool Equals(GreaterEqualPredicate other)
        {
            return base.Equals(other);
        }

        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((GreaterEqualPredicate)obj);
        }

        public override int GetHashCode()
        {
            return base.GetHashCode();
        }

        protected override bool ComparisonFunction(int compareToResult)
        {
            bool result = (compareToResult >= 0);
            return result;
        }
    }
}
using QueryLogic.Expressions.RowExpressions;

namespace QueryLogic.Predicates.Simple
{
    public class GreaterThanPredicate : AbstractCompareToPredicate
    {
        public GreaterThanPredicate(IRowExpression left, IRowExpression right)
            : base(left, right)
        {
        }

        protected override bool ComparisonFunction(int compareToResult)
        {
            bool result = (compareToResult > 0);
            return result;
        }

        protected bool Equals(GreaterThanPredicate other)
        {
            return base.Equals(other);
        }

        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((GreaterThanPredicate)obj);
        }

        public override int GetHashCode()
        {
            return base.GetHashCode();
        }
    }
}
using QueryLogic.Expressions.RowExpressions;

namespace QueryLogic.Predicates.Simple
{
    public class LessEqualPredicate : AbstractCompareToPredicate
    {
        public LessEqualPredicate(IRowExpression left, IRowExpression right)
            : base(left, right)
        {
        }

        protected override bool ComparisonFunction(int compareToResult)
        {
            bool result = (compareToResult <= 0);
            return result;
        }

        protected bool Equals(LessEqualPredicate other)
        {
            return base.Equals(other);
        }

        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((LessEqualPredicate)obj);
        }

        public override int GetHashCode()
        {
            return base.GetHashCode();
        }
    }
}
using QueryLogic.Expressions.RowExpressions;

namespace QueryLogic.Predicates.Simple
{
    public class LessThanPredicate : AbstractCompareToPredicate
    {
        public LessThanPredicate(IRowExpression left, IRowExpression right)
            : base(left, right)
        {
        }

        protected bool Equals(LessThanPredicate other)
        {
            return base.Equals(other);
        }

        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((LessThanPredicate)obj);
        }

        public override int GetHashCode()
        {
            return base.GetHashCode();
        }

        protected override bool ComparisonFunction(int compareToResult)
        {
            bool result = (compareToResult <  0);
            return result;
        }
    }
}

Of course we can do it other way, e.g., like this:

using Model;
using QueryLogic.Expressions.RowExpressions;

namespace QueryLogic.Predicates.Simple
{
    public class EqualPredicate : IPredicate
    {
        public EqualPredicate(IRowExpression left, IRowExpression right)
        {
            Left = left;
            Right = right;
        }

        public IRowExpression Left { get; private set; }
        public IRowExpression Right { get; private set; }

        public bool KeepRow(Row row)
        {
            Cell leftCell = Left.Calculate(row);
            Cell rightCell = Right.Calculate(row);

            if (leftCell == null || rightCell == null)
            {
                return false;
            }

            string leftValue = leftCell.Value;
            string rightValue = rightCell.Value;

            bool result = leftValue.Equals(rightValue);
            return result;
        }

        protected bool Equals(EqualPredicate other)
        {
            return Equals(Left, other.Left) && Equals(Right, other.Right);
        }

        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((EqualPredicate)obj);
        }

        public override int GetHashCode()
        {
            unchecked
            {
                return ((Left != null ? Left.GetHashCode() : 0) * 397) ^ (Right != null ? Right.GetHashCode() : 0);
            }
        }
    }
}
using Model;
using QueryLogic.Expressions.RowExpressions;
using QueryLogic.Predicates.Complex;

namespace QueryLogic.Predicates.Simple
{
    public class NotEqualPredicate : IPredicate
    {
        public NotEqualPredicate(IRowExpression left, IRowExpression right)
        {
            Left = left;
            Right = right;
            Predicate = new NotPredicate(new EqualPredicate(left, right));
        }

        public IRowExpression Left { get; private set; }
        public IPredicate Predicate { get; private set; }
        public IRowExpression Right { get; private set; }

        public bool KeepRow(Row row)
        {
            Cell leftCell = Left.Calculate(row);
            Cell rightCell = Right.Calculate(row);

            if (leftCell == null || rightCell == null)
            {
                return false;
            }

            bool result = Predicate.KeepRow(row);
            return result;
        }

        protected bool Equals(NotEqualPredicate other)
        {
            return Equals(Left, other.Left) && Equals(Predicate, other.Predicate) &&
                   Equals(Right, other.Right);
        }

        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((NotEqualPredicate)obj);
        }

        public override int GetHashCode()
        {
            unchecked
            {
                int hashCode = (Left != null ? Left.GetHashCode() : 0);
                hashCode = (hashCode * 397) ^ (Predicate != null ? Predicate.GetHashCode() : 0);
                hashCode = (hashCode * 397) ^ (Right != null ? Right.GetHashCode() : 0);
                return hashCode;
            }
        }
    }
}

Last but not least we implement IS NULL predicate:

using Model;
using QueryLogic.Expressions.RowExpressions;

namespace QueryLogic.Predicates.Simple
{
    public class IsNullPredicate : IPredicate
    {
        public IsNullPredicate(IRowExpression rowExpression)
        {
            RowExpression = rowExpression;
        }

        public IRowExpression RowExpression { get; private set; }

        public bool KeepRow(Row row)
        {
            Cell value = RowExpression.Calculate(row);

            bool result = (value == null) || (value.Value == null);
            return result;
        }

        protected bool Equals(IsNullPredicate other)
        {
            return Equals(RowExpression, other.RowExpression);
        }

        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((IsNullPredicate)obj);
        }

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