Creating Custom SQL Queries

If your query requires JOINs or OR statements, you'll have to manually write the SQL statement. The syntax of your SQL query will need to value valid for your current database.

Caution

ALWAYS use parameters for any variables that are coming from outside the current function. Concatenating the variables directly into the query leaves your code open to SQL Injection

public class PersonRepository : DbRepository<Person> {

    public PersonRepository(UnitOfWork uow) : base(uow.DbAdapter) { }

    // Get all entities that are inactive or the display name is NULL
    public List<Person> GetInactiveOrWithoutDisplayName() {
        QueryBuilder query = GetQueryBuilder();
        query.SqlQuery +=
            $"WHERE t.IsActive = 0 " + // Hard-coding the value 0 is safe here
            $"OR DisplayName IS NULL ";
        return GetEntities(query);
    }

    // Get all entities based on a join to another table. This does not SELECT any data from the joined table.
    public List<Person> GetWithTransactionsAfterDate(DateTime datetime) {
        QueryBuilder query = GetQueryBuilder();
        query.SqlQuery +=
            $"INNER JOIN transaction t ON {TableName}.ID = t.PersonID " +
            $"WHERE t.DateTime > @DateTime ";
        query.AddParameter("@DateTime", datetime); // Add the datetime value as an parameter
        return GetEntities(query);
    }
}