Dapper query on arbitrary properties

Question

I'm working on a test Dapper project, and I've come across a use case I can't quite figure out. For a user repository, I have a GetById method that's working fine:

public static IEnumerable<User> GetById(IDbConnection connection, string identity)
    {
        string query = "SELECT * FROM USERS.USERINFO WHERE Id = @Id";
        return connection.Query<User>(query, new User { Id = identity });
    }

But I'd also like to be able to query my users by an arbitrary property or set of properties. Is this possible? I've thought of simply dynamically building the SQL query string, but wondered if Dapper provides something like this?

Another approach I've taken is to use the IEnumerable Where method to query, but I'm guessing this is quite inefficient:

public static IEnumerable<User> Search(IDbConnection connection, Func<User, bool> predicate)
    {
        string query = "SELECT * FROM USERS.USER_INFO";
        return connection.Query<User>(query).Where(predicate);
    }

Any thoughts on how an arbitrary filter/search method might be implemented?

Thanks!

Answer 1

You can use SqlBuilder and dynamically add more Where clauses:

 const string sqlTemplate = "SELECT * FROM USERS.USERINFO /**where**/";

 var sqlBuilder = new SqlBuilder();
 var template = sqlBuilder.AddTemplate(sqlTemplate);

 sqlBuilder.Select("*").Where("Id = @Id", new { Id = id });

 return connection.Query<User>(query);