Using query properties - adamfoneil/Dapper.QX GitHub Wiki

Make Query<TResult> classes useful by adding properties to define a mix of required and optional parameters. (Hereafter shortened to Query.) In a typical application, you may have one result class that is queried many different ways. This is what Dapper.QX is meant for.

To define required parameters in a query, include the parameter name somewhere in the SQL. For example:

public class MyQuery : Query<CustomerResult>
{
    public MyQuery() : base("SELECT * FROM [Customer] WHERE [Id]=@customerId") { }

    public int CustomerId { get; set; }
}

Because @customerId appears in the SQL, it's a required parameter. It has a corresponding property CustomerId that is not nullable, indicating that it always has a value. Accordingly, we might infer in this case that this query always returns a single row. This is probably not useful in practice, so let's address with optional parameters.

To define optional parameters in a query, don't include them in the SQL, and instead attach a [Where] or [Case] attribute to parameter's corresponding property. Also important, optional parameters should have nullable properties. Make sure you include a {where} or {andWhere} token somewhere in your SQL. For example:

public class MyQuery : Query<CustomerResult>
{
    public MyQuery() : base("SELECT * FROM [Customer] {where}") { }

    [Where("[Id]=@customerId")]
    public int? CustomerId { get; set; }
}

Now, we can call MyQuery with or without a CustomerId. In examples below, I'm assuming there's a connection variable in scope named cn. Note also haven't included the definition of CustomerResult referenced above to keep these examples simple. Ordinarily, I would use Postulate.Zinger to create result classes.

var results = await new MyQuery() { CustomerId = 2294 }.ExecuteAsync(cn);

Now without a CustomerId:

var results = await new MyQuery().ExecuteAsync(cn);

In practice, you would usually have several or many different criteria that applied to a single query. Let's add a few more properties to our query to make it more realistic looking:

public class MyQuery : Query<CustomerResult>
{
    public MyQuery() : base("SELECT * FROM [Customer] [c] {where}") { }

    [Where("[Id]=@customerId")]
    public int? CustomerId { get; set; }

    [Where("[Balance]>=@minBalance")]
    public decimal? MinBalance { get; set; }

    [Where("[Balance]<=@maxBalance")]
    public decimal? MaxBalance { get; set; }

    [Where("[Name] LIKE '%'+@nameLike+'%'")]
    public string NameLike { get; set; }

    [Case(false, "NOT EXISTS(SELECT 1 FROM [Orders] WHERE [CustomerId]=[c].[Id] AND [TotalAmount]>100000)")]
    [Case(true, "EXISTS(SELECT 1 FROM [Orders] WHERE [CustomerId]=[c].[Id] AND [TotalAmount]>100000)")]
    public bool? HasLargeOrder { get; set; }
}

Now we have more things we can query:

var results = await new MyQuery()
{
    HasLargeOrder = true,
    NameLike = 'Sm'
}.ExecuteAsync(cn);

var results = await new MyQuery()
{
   MinBalance = 1500
}.ExecuteAsync(cn);

A Query class may have a mix of required and optional parameters. Let's take our customer query example and imagine that it's part of a multi-tenant solution that always requires a tenant Id when querying. Such a query might look like this:

public class MultiTenantQuery : Query<CustomerResult>
{
    public MultiTenantQuery() : base("SELECT * FROM [Customer] WHERE [TenantId]=@tenantId {andWhere}") { }

    public int TenantId { get; set; }

    [Where("[Balance]>=@minBalance")]
    public decimal? MinBalance { get; set; }

    [Where("[Balance]<=@maxBalance")]
    public decimal? MaxBalance { get; set; }

    [Where("[Name] LIKE '%'+@nameLike+'%'")]
    public string NameLike { get; set; }
}

This query has a required @tenantId parameter because it's in the SQL. Note that because we have some embedded criteria, we need to use {andWhere} as the dynamic criteria token so that AND is properly concatenated into the generated SQL. When executing this query, we have to provide a tenant Id to get any results:

var results = await new MultiTenantQuery()
{
    TenantId = 2245
}.ExecuteAsync(cn);

Fortunately, if we forget to set the TenantId a zero is passed. So, the worst that happens is that we get no results. In a real application, multi-tenant criteria would be set in a standardized, low-level way to prevent cross-tenant leakage, but would leverage the same query approach shown here.