Use inputbox to filter in asp.net core razor with dapper

I have a query that is already getting data from the DB, Now I want to filter the data using a checkbox from the razor page. Below here is the checkbox

 <!--check box-->
                    <div id="selection" class="input-group col-md-3 flex-column" style="display: inline-flex;">

                        <div class="form-check">
                            <input type="text" class="form-check-input" value="InterestRate" name="loan" id="intRate">
                            <label class="form-check-label" for="exampleCheck1">Interest Rate</label>
                        </div>
                        <div class="form-check">
                            <input type="text" class="form-check-input" value="LoanAmount" name="loan" id="intRate">
                            <label class="form-check-label" for="exampleCheck1">Interest Rate</label>
                        </div>
                        <div class="form-check">
                            <input type="date" class="form-check-input" value="LoanTrfDate" name="loan" id="transDate">
                            <label class="form-check-label" for="exampleCheck1">Transfer Date</label>
                        </div>
                        <button type="submit" class="btn btn-primary" id="filter">Submit</button>
                    </div>
                    <!--check box-->

I am already getting the values of the checkboxes via string[] Requestloans But the problem is how to filer db with the RequestLoans array using dapper. If this is possible for two then I can also apply it to more than two

public async Task<IEnumerable<Loan>> ManageGettingAll(string[] Requestloans, bool includeDeleted, bool showUnapprovedOnly)
{
  IEnumerable<Loan> loans = null;
            
  try
  {
    using (var conn = new SqlConnection(connectionstring))
    {
      await con.OpenAsync();
      string sql = "Select l.*, c.FirstName from dbo.Loan l left join Customer c on  l.CustId=c.CustId";
      if (!includeDeleted)
      {
        sql += " and l.Deleted = 0";
      }
      if (showUnapprovedOnly)
      {
        sql += " and l.Approved = 0";
      }
      loans = await conn.QueryAsync<Loan>(sql);
    }
  }
  catch (Exception)
  {
    throw;
  }
return loans;
}

Answer

If you manage to get your input variables inside a Dictionary<string, object> before the call, either through data binding the variables or otherwise getting them when you need them. The following method can filter it for you.

This assumes you make the keys the column names. And that in this case they’re all from the same table alias (though you can edit this as needed. It’s just a way to keep building a prepared statement adding where conditions on the fly).

public async Task<IEnumerable<Loan>> ManageGettingAll(Dictionary<string, object> Params)
{
  IEnumerable<Loan> loans = null;

  try
  {
    using (var conn = new SqlConnection(connectionstring))
    {
    await conn.OpenAsync();
    string basesql = "Select l.*, c.FirstName from dbo.Loan l left join Customer c on  l.CustId=c.CustId";
    DynamicParameters queryParams = new DynamicParameters();

    if (Params.Count == 0)
    {
      //No params, so no where condition needed.
    }
    else
    {
      bool firstVar = true;                        
      basesql += " WHERE ";
              
      foreach (var paramName in Params.Keys)
      {
        if (!firstVar)
        {
          basesql += " AND ";
        }
        else
        {
          firstVar = false;
        }
        basesql += ("l." + paramName + " = @" + paramName);
        if (Params.TryGetValue(paramName, out object ParamValue))
        {
          queryParams.Add("@" + paramName, ParamValue);
        }
        else
        {
          //Key not found, should be impossible though.
        }
      }                        
    }

    loans = await conn.QueryAsync<Loan>(basesql, queryParams);
    }
  }
  catch (Exception)
  {
    throw;
  }
return loans;
}

If you have a dictionary build up as such:

Dictionary<string, object> parameters = new Dictionary<string, object>();

parameters.Add("interestrate", 10);
parameters.Add("transferdate", DateTime.Now);
parameters.Add("loanName", "Mortgage");

ManageGettingAll(parameters);

The parameterised query becomes:

SELECT l.*, c.FirstName from dbo.Loan l left join Customer c on l.CustId=c.CustId
WHERE l.interestrate = @interestrate AND l.transferdate = @transferdate AND l.loanName = @loanname

Upon execute you supply the parameter values, and Dapper is pretty good at the type matching based on your values. This also works around some dangers of SQLinjection.

There are other ways as well to build up the query dynamically, or insert parameters. Getting the values into the dictionary i’ll leave to you.

Dapper explanation