Merge query to upsert data is always inserting the data when run from .NET 6 Web API

Question

I'm encountering an issue with an upsert query in SQL Server within an ASP.NET Core 6 Web API. The goal is to check if a record exists and either update It or insert a new one. However, the query consistently inserts new records even when same records already exist in the database.

The strange part is that the query runs fine(inserts and updates) when run from SSMS. But from the API, it only inserts the records even if the record with the same values exists (the database allows duplicate entries).

Any insights into this is greatly appreciated.

Query

MERGE SG_APPOINTMENT_DATA AS TARGET 
USING (SELECT '@Appt_ID' AS ApptID, '@OPID' AS OPID) AS SOURCE 
      ON TARGET.ApptID = SOURCE.ApptID 
         AND TARGET.OPID = SOURCE.OPID    

WHEN NOT MATCHED THEN    
    INSERT ( //Insert )    

WHEN MATCHED THEN     
    UPDATE //update";

C# code:

 public async Task<int> ExecuteQuery(string query, IDictionary<string, object>? parameters = null)
 {
     int rowsAffected = 0;

     try
     {
         using SqlConnection conn = new SqlConnection(connectionString);
         using SqlCommand command = new(query, conn);

         if (parameters != null)
         {
             foreach (var param in parameters)
             {
                 command.Parameters.Add(new SqlParameter(param.Key, param.Value));
             }
         }

         await conn.OpenAsync();                

         rowsAffected = await command.ExecuteNonQueryAsync();

         await conn.CloseAsync();
    }
    catch (Exception)
    {
        throw;
    }

    return rowsAffected;
}