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

I'm building some SQL query in C#. It will differ depending on some conditions stored as variables in the code.

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 ";

It works, but testing 1=1 doesn't seem elegant. If I didn't use it, I would have to remember and check every time if "where" keyword was already added or not to the query.

Is there a nicer solution?

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.

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

Related

Recent Posts