Inconsistent Query Results with Dapper and SQL Server When Using Dynamic Parameters
This might be a silly question, but After trying multiple solutions online, I still can't figure this out... I'm experimenting with After trying multiple solutions online, I still can't figure this out. I'm experiencing inconsistent query results when using Dapper to fetch data from SQL Server. My application is built on .NET 6, and I'm dynamically constructing SQL queries based on user input. The scenario arises when I try to filter results based on multiple optional parameters. For example, when both `Name` and `Age` are provided, it returns the expected results. However, if only `Name` is provided, it sometimes returns results that don't match the `Age` filter, and I need to figure out why. Here's a simplified version of my code: ```csharp public async Task<IEnumerable<User>> GetUsersAsync(string name, int? age) { var sql = "SELECT * FROM Users WHERE 1=1"; var parameters = new DynamicParameters(); if (!string.IsNullOrEmpty(name)) { sql += " AND Name = @Name"; parameters.Add("@Name", name); } if (age.HasValue) { sql += " AND Age = @Age"; parameters.Add("@Age", age.Value); } return await _dbConnection.QueryAsync<User>(sql, parameters); } ``` Despite having the correct SQL query being generated, I've noticed that sometimes the results include users that shouldn't match the criteria, particularly when filtering by age. When I debug, the `parameters` object contains the expected values, and I've confirmed that the SQL being executed is as intended. I've tried logging the final SQL query and parameters just before execution, and they appear correct. Additionally, I verified the database for any anomalies that might cause incorrect filtering. I'm using Dapper version 2.0 and SQL Server 2019. Can anyone shed light on why this might be happening? Could it be related to how Dapper handles null values or dynamic parameters under the hood? Any insights would be greatly appreciated. This is part of a larger web app I'm building. Any ideas what could be causing this? For reference, this is a production microservice. I'd be grateful for any help.