c# - 2 parameterized where clauses in a single query (not 'where _ and _')

Question

Can parameterizing 2 different where clauses (non contiguous) in a single query be done in dapper?

Sample SQL query as C# property

private string GetSalesRepToCustomer => @"
  WITH SALES_REP_FILTERED AS (
    SELECT
      SALES_REP_ID
    FROM
      SALES_REP
    /**where**/     /* 1st where clause. */
  )
  SELECT
    A.SALES_REP_ID,
    B.CUSTOMER_ID
  FROM
    SALES_REP_FILTERED A LEFT JOIN
    CUSTOMER           B   ON A.SALES_REP_ID = B.SALES_REP_ID
  /**where**/     /* 2nd where clause */
";

C# parameterization not working, illustration purposes.

SqlTemplate queryTemplate = new SqlBuilder()
  .where("SALES_REP_ID = :SALES_REP_ID")

  // this 2nd 'where' is illustrative.
  // In actual an 'and' clause is created.
  // My goal is to have a secondary/non contiguous 'where' clause in the same query.
  .where("CUSTOMER_ID  IN :CUSTOMER_ID")
  .AddTemplate(GetSalesRepToCustomer);

var conn = new OracleConnection();
var queryResults = conn.Query(
  queryTemplate.RawSql,
  new { SALES_REP_ID = 2021, CUSTOMER_ID = new int[] {11, 22, 33}}
);

Context and reasoning of what I want to achieve:

1. Read online (Cant recall where. Probably on stackoverflow) the incentive(s) for dapper query parameterization and would like to stick/follow to this recommended practice.
2. I could split the query up into 2 and make separate round trips to the DB. But there are performance and IO payoffs/benefits when executed as a single query.
3. My last option is to in line the where clauses in SQL. But this probably would negate the benefits of dapper parameterization? I.e.

private string GetSalesRepToCustomer => @"
  WITH SALES_REP_FILTERED AS (
    SELECT
      SALES_REP_ID
    FROM
      SALES_REP
    where SALES_REP_ID = :SALES_REP_ID
  )
  SELECT
    A.SALES_REP_ID,
    B.CUSTOMER_ID
  FROM
    SALES_REP_FILTERED A LEFT JOIN
    CUSTOMER           B   ON A.SALES_REP_ID = B.SALES_REP_ID
  WHERE CUSTOMER_ID IN :CUSTOMER_ID
";

And the where parameterization is taken out.

SqlTemplate queryTemplate = new SqlBuilder()
    // No parameterized where clauses. So also no dapper performance improvements?
    .AddTemplate(GetSalesRepToCustomer);

var conn = new OracleConnection();
var queryResults = conn.Query(
  queryTemplate.RawSql,
  new { SALES_REP_ID = 2021, CUSTOMER_ID = new int[] {11, 22, 33}}
);

Answer 1

As I understand it, this should be fine:

var conn = new OracleConnection();
var sql = @"
  WITH SALES_REP_FILTERED AS (
    SELECT
      SALES_REP_ID
    FROM
      SALES_REP
    where SALES_REP_ID = :SALES_REP_ID
  )
  SELECT
    A.SALES_REP_ID,
    B.CUSTOMER_ID
  FROM
    SALES_REP_FILTERED A LEFT JOIN
    CUSTOMER           B   ON A.SALES_REP_ID = B.SALES_REP_ID
  WHERE CUSTOMER_ID IN :CUSTOMER_ID
";
var queryResults = conn.Query<SOME_TYPE>(
  sql,
  new { SALES_REP_ID = 2021, CUSTOMER_ID = new int[] {11, 22, 33}}
);

But your SQL could also be written as:

  SELECT
    A.SALES_REP_ID,
    B.CUSTOMER_ID
  FROM
    SALES_REP SR
    LEFT JOIN CUSTOMER C   ON SR.SALES_REP_ID = C.SALES_REP_ID
  WHERE 
    C.CUSTOMER_ID IN :CUSTOMER_ID AND
    SR.SALES_REP_ID = :SALES_REP_ID

If you pass 3 customer IDs, then dapper will rewrite your WHERE clause to be:

WHERE C.CUSTOMER_ID IN (:CUSTOMER_ID1, :CUSTOMER_ID2, :CUSTOMER_ID3) AND

and it will populate the command's parameters collection with the three parameters populated with the values supplied