Records getting duplicated when mapped to object in TemplateContext.Database.SqlQuery function

Question

I am getting strange issue when I am calling a stored procedure like this using TemplateContext

string query = "exec SearchRequest @recct = @TotalCount OUTPUT" + parametersString.ToString();
var requestDtos = ctx.Database.SqlQuery<RequestDto>(query, totalCount).ToList();

This gets sometimes 30 and sometimes 60 objects in requestDtos while the database is actually returning correct number of rows based on PageSize parameter that I am passing to the stored procedure, but strangely records are getting duplicated while getting mapped to objects.

So instead of getting 10 objects if page size is 10 I am getting duplicated 30 or 60 objects.

Any idea of this strange behavior?

Answer 1

You are not passing parameters correctly. I suggest you to use below code :

var outParam = new SqlParameter();
outParam.ParameterName = "TotalCount";
outParam.SqlDbType = SqlDbType.Int;
outParam.ParameterDirection = ParameterDirection.Output;

var requestDtos = dbContext.Database.SqlQuery<MyType>("SearchRequest @recct, @TotalCount OUT", 
               new SqlParameter("recct", parametersString.ToString()), 
               outParam);
var result = requestDtos.ToList();
var totalCount = (int)outParam.Value;