Best way of constructing dynamic sql queries in C#/.NET3.5?

Question

A project I'm working on at the moment involves refactoring a C# Com Object which serves as a database access layer to some Sql 2005 databases.

The author of the existent code has built all the sql queries manually using a string and many if-statements to construct the fairly complex sql statement (~10 joins, >10 sub selects, ~15-25 where conditions and GroupBy's). The base table is always the same one, but the structure of joins, conditions and groupings depend on a set of parameters that are passed into my class/method.

Constructing the sql query like this does work but it obviously isn't a very elegant solution (and rather hard to read/understand and maintain as well)... I could just write a simple "querybuilder" myself but I am pretty sure that I am not the first one with this kind of problem, hence my questions:

• How do you construct your database queries?
• Does C# offer an easy way to dynamically build queries?

Answer 1

I used C# and Linq to do something similar to get log entries filtered on user input (see Conditional Linq Queries):

IQueryable<Log> matches = m_Locator.Logs;

// Users filter
if (usersFilter)
    matches = matches.Where(l => l.UserName == comboBoxUsers.Text);

 // Severity filter
 if (severityFilter)
     matches = matches.Where(l => l.Severity == comboBoxSeverity.Text);

 Logs = (from log in matches
         orderby log.EventTime descending
         select log).ToList();

Edit: The query isn't performed until .ToList() in the last statement.