Is there a better way to dynamically build an SQL WHERE clause than by using 1=1 at its beginning?

Question

Save the conditions in a list:

List<string> conditions = new List<string>();

if (condition1) conditions.Add("Col1=0");
//...
if (conditions.Any())
    Query += " WHERE " + string.Join(" AND ", conditions.ToArray());

One solution is to simply not write queries manually by appending strings. You could use an ORM, like Entity Framework, and with LINQ to Entities use the features the language and framework offer you:

using (var dbContext = new MyDbContext())
{
    IQueryable<Table1Item> query = dbContext.Table1;

    if (condition1)
    {
        query = query.Where(c => c.Col1 == 0);
    }
    if (condition2)
    {
        query = query.Where(c => c.Col2 == 1);
    }
    if (condition3)
    {
        query = query.Where(c => c.Col3 == 2);
    }   

    PrintResults(query);
}

A slight bit of overkill in this simple case but I've used code similar to this in the past.

Create a function

string AddCondition(string clause, string appender, string condition)
{
    if (clause.Length <= 0)
    {
        return String.Format("WHERE {0}",condition);
    }
    return string.Format("{0} {1} {2}", clause, appender, condition);
}

Use it like this

string query = "SELECT * FROM Table1 {0}";
string whereClause = string.Empty;

if (condition 1)
    whereClause = AddCondition(whereClause, "AND", "Col=1");

if (condition 2)
    whereClause = AddCondition(whereClause, "AND", "Col2=2");

string finalQuery = String.Format(query, whereClause);

This way if no conditions are found you don't even bother loading a where statement in the query and save the sql server a micro-second of processing the junk where clause when it parses the sql statement.

There is another solution, which may also not be elegant, but works and solves the problem:

String query = "SELECT * FROM Table1";
List<string> conditions = new List<string>();
// ... fill the conditions
string joiner = " WHERE ";
foreach (string condition in conditions) {
  query += joiner + condition;
  joiner = " AND "
}

For:

• empty conditions list, the result will be simply SELECT * FROM Table1,
• a single condition it will be SELECT * FROM Table1 WHERE cond1
• each following condition will generate additional AND condN

Just do something like this:

using (var command = connection.CreateCommand())
{
    command.CommandText = "SELECT * FROM Table1";

    var conditions = "";
    if (condition1)
    {    
        conditions += "Col1=@val1 AND ";
        command.AddParameter("val1", 1);
    }
    if (condition2)
    {    
        conditions += "Col2=@val2 AND ";
        command.AddParameter("val2", 1);
    }
    if (condition3)
    {    
        conditions += "Col3=@val3 AND ";
        command.AddParameter("val3", 1);
    }
    if (conditions != "")
        command.CommandText += " WHERE " + conditions.Remove(conditions.Length - 5);
}

It's SQL injection safe and IMHO, it's pretty clean. The Remove() simply removes the last AND;

It works both if no conditions have been set, if one have been set or if multiple have been set.

Just append two lines at back.

string Query="SELECT * FROM Table1 WHERE 1=1 ";
if (condition1) Query+="AND Col1=0 ";
if (condition2) Query+="AND Col2=1 ";
if (condition3) Query+="AND Col3=2 ";
Query.Replace("1=1 AND ", "");
Query.Replace(" WHERE 1=1 ", "");

E.g.

SELECT * FROM Table1 WHERE 1=1 AND Col1=0 AND Col2=1 AND Col3=2 

will become to

SELECT * FROM Table1 WHERE Col1=0 AND Col2=1 AND Col3=2 

While

SELECT * FROM Table1 WHERE 1=1 

will become to

SELECT * FROM Table1

=====================================

Thanks for pointing out a flaw of this solution:

"This could break the query if, for any reason, one of the conditions contains the text "1=1 AND " or " WHERE 1=1 ". This could be the case if the condition contains a subquery or tries to check if some column contains this text, for example. Maybe this isn't a problem in your case but you should keep it in mind… "

In order to get rid of this issue, we need to distinguish the "main" WHERE 1=1 and those from subquery, which is easy:

Simply make the "main" WHERE special: I would append a "$" sign

string Query="SELECT * FROM Table1 WHERE$ 1=1 ";
if (condition1) Query+="AND Col1=0 ";
if (condition2) Query+="AND Col2=1 ";
if (condition3) Query+="AND Col3=2 ";

Then still append two lines:

Query.Replace("WHERE$ 1=1 AND ", "WHERE ");
Query.Replace(" WHERE$ 1=1 ", "");