How to map/join two tables and filter by parameters using dapper

I want to join two tables that have a relationship with each other, In the Customer table, there are two columns for deleted and approved. Get the Customer that are approved and not deleted then match the Customer with the Loan table Using CustId (Both Tables has CustId) and Filter by supplied parameters

• Deleted=0
• Approved=1

Passed Parameters are(And they can be optional) User can pass only one or two or more than 2 InterestRate LoanTrfDate LastPaymentDate

        public async Task<IEnumerable<Loan>> ManageGettingAll(string InterestRate, string LoanTrfDate, string LastPaymentDate)
        {
            IEnumerable<Loan> loans = null;

            //var sql = "Select * from MonthlyInterest where LoanAccountNo=@Id AND Deleted=@Deleted AND CAST(TranDate AS DATE) BETWEEN @StartDate AND @EndDate";
            try{

                using (var conn = new SqlConnection(connectionstring))
                {
                    await conn.OpenAsync();

                    var parameters = new 
                    { 
                        InterestRate = InterestRate, 
                        LoanTrfDate = LoanTrfDate, 
                        LastPaymentDate = LastPaymentDate
                    };
                   
                string sql = "Select l.*, c.FirstName_CompanyName from dbo.Loan l left join Customer where and l.Deleted = 0 and and l.Approved = 1 c on  l.CustId=c.CustId Where InterestRate=@InterestRate AND CAST(LoanTrfDate AS DATE)=@LoanTrfDate AND CAST(LastPaymentDate AS DATE)=@LastPaymentDate";
                 
                    loans = await conn.QueryAsync<Loan>(sql, parameters);
                }
            }
            catch (Exception ex)
            {

                throw;
            }


            return loans;
        }

Answer

to filter by a parameter only if it’s not null, you can use the SQL below.

Select l.*, c.FirstName_CompanyName 
from dbo.Loan l 
left join Customer c on l.CustId=c.CustId 
where l.Deleted = 0 and l.Approved = 1
and (@InterestRate is null or InterestRate=@InterestRate) 
AND (@LastPaymentDate is null or (CAST(LoanTrfDate AS DATE)=@LoanTrfDate AND CAST(LastPaymentDate AS DATE)=@LastPaymentDate))

to check that at least one parameter was set, you can add this if statement.

if(string.IsNullOrWhiteSpace(LoanTrfDate) && string.IsNullOrWhiteSpace(LastPaymentDate))
{
    throw new Exception("Some clear message here.");
}