QuerySingleOrDefaultAsync: Procedure or function has too many arguments specified
Nobody has claimed this yet.
- Dominant language
- C#
- Stars
- 18.4k
- Forks
- 3.7k
- Avg merge
- 5h 8m
- Merged PRs (30d)
- 1
Description
We have developed an API project in .Net Core 2.2 and Dapper 2.0.30 with Microsoft SQL Server 2017 database.
Sometimes it happens after a new build in release, that we receive the "Procedure or function has too many arguments specified" error on the production server. We cannot reproduce this error in debug, even using the same database.
Often, recompiling and republishing the project in production, even without making any changes, the problem disappears. The problem is that it then reappears randomly in a subsequent build.
I found this discussion where there was already talk of a similar case: https://stackoverflow.com/questions/25069578/dapper-procedure-or-function-has-too-many-arguments-specified
In fact, the first time we had a Stored Procedure that could return multiple datasets and that we ran with the QuerySingleOrDefaultAsync command. But now it is also happening with a stored procedure that always returns a single dataset.
By the way, this method is generic and we use it to perform all the data reading of a single record for all entities. But the error, at least at the moment, occurs only with a certain stored procedure.
Looking at what happens through SQL Profiler, this is the correct call I see in debug:
exec dbo.s_ExampleModelFind @emId=74658
This is the wrong one I see in production:
exec dbo.s_ExampleModelFind @ParameterNames1=N'emId', @RemoveUnused=1
This is the Stored Procedure:
CREATE PROCEDURE [dbo].[s_ExampleModelFind]
@emId int
AS
BEGIN
SET NOCOUNT ON;
SELECT emId,
emName
emOrder,
_usrIdC,
_grpIdC,
_tsC,
_usrIdM,
_grpIdM,
_tsM,
_deleted
FROM dbo.ExampleModel
WHERE emId = @emId;
END
This is the model class:
public partial class ExampleModel
{
[Key]
[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
public int EmId { get; set; }
public string EmName { get; set; }
public int EmOrder { get; set; }
public int UsrIdC { get; set; }
public int GrpIdC { get; set; }
public DateTimeOffset TsC { get; set; }
public int? UsrIdM { get; set; }
public int? GrpIdM { get; set; }
public DateTimeOffset? TsM { get; set; }
public bool Deleted { get; set; }
}
This is the generic method:
async Task<T> ICRUDRepository<T>.GetByID(params object[] ids)
{
var keyProperties = typeof(T).GetKeysProperties(); // returns only the properties with Key attribute
var p = new DynamicParameters();
for (int i = 0; i < keyProperties.Count; i++)
{
p.Add(keyProperties[i].Name, ids[i], keyProperties[i].PropertyType.ToDbType());
}
string spName = $"{_storedProcedurePrefix}{_modelName}Find";
using (IDbConnection conn = Connection) // Connection is a SqlConnection variable
{
conn.Open();
var record = await conn.QuerySingleOrDefaultAsync<T>(spName, p, commandType: CommandType.StoredProcedure);
return record;
}
}
Are there any problems building a project in a certain way that uses dapper? I can't explain why this issue never occurs in debugging.
Contributor guide
No contributing guide indexed for this repository
First steps
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- Open a pull request that references the issue number.
Research direction
Start with the generic ICRUDRepository.GetByID method and its QuerySingleOrDefaultAsync call, then compare the debug and production SQL Profiler calls shown in the report. Determine why the stored procedure receives @ParameterNames1 and @RemoveUnused instead of @emId; done means identifying a reproducible cause and documenting or testing the correction.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- csharp, sql
- Domain
- backend, databases
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Stale
- Clarity
- Needs clarification
- Newbie friendliness
- 25/100