Doesn't work with NULLs in SQL

[NibblyPig]

It translates NULLs into = null as if it were any other value, which doesn't return anything from your query because SQL doesn't properly understand = null.

I fixed this by putting in a check, if the value is null and the operator is = then append IS NULL, and don't add it as a parameter. The same for != too.

Unoptimised/unrefactored code:

if (!string.IsNullOrEmpty(item.LinkingOperator) && i > 0)
{
    if (item.PropertyValue == null && item.QueryOperator == "=")
    {
        builder.Append(string.Format("{0} {1} IS NULL", item.LinkingOperator, item.PropertyName));
    }
    else if (item.PropertyValue == null && item.QueryOperator == "!=")
    {
        builder.Append(string.Format("{0} {1} IS NOT NULL", item.LinkingOperator, item.PropertyName));
    }
    else
    {
        builder.Append(string.Format("{0} {1} {2} @{1} ", item.LinkingOperator, item.PropertyName,
                                     item.QueryOperator));
    }
}
else
{
    if (item.PropertyValue == null && item.QueryOperator == "=")
    {
        builder.Append(string.Format("{0} IS NULL", item.PropertyName));
    }
    else if (item.PropertyValue == null && item.QueryOperator == "!=")
    {
        builder.Append(string.Format("{0} IS NOT NULL", item.PropertyName));
    }
    else
    {
        builder.Append(string.Format("{0} {1} @{0} ", item.PropertyName, item.QueryOperator));
    }
}

Output if you do x.Where(c => c.Someprop == 5 && c.AnotherProp != null) is then SELECT * FROM SOMETABLE WHERE Someprop = 5 && AnotherProp IS NOT NULL