2

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?

Rahul Nikate
  • 6,192
  • 5
  • 42
  • 54
Pawan Nogariya
  • 8,330
  • 12
  • 52
  • 105
  • I think you need to do `.Take(10)` after `.Distinct()` like `.Distinct().Take(10).ToList();` – Rahul Nikate Jun 15 '16 at 09:15
  • I updated my question, actually I tried calling distinct because of that duplicate object behavior, it was not in the original code. The issue is it shouldn't actually duplicate the objects. – Pawan Nogariya Jun 15 '16 at 09:17
  • Then you can try like this `(query, totalCount).Take(10).ToList();` – Rahul Nikate Jun 15 '16 at 09:18
  • But why should I do that workaround? The issue is why it is making records 30 or 60 when database is returning only 10 records. It should work directly with this `ctx.Database.SqlQuery(query, totalCount).ToList();` only – Pawan Nogariya Jun 15 '16 at 09:20
  • I mentioned in the question that when I run the same query in the database that is getting passed to the code it returns correct no. of rows. So the same query returns 10 records when I run it through sql server management studio and in code after mapping records are getting duplicated. – Pawan Nogariya Jun 15 '16 at 09:23
  • We can't check if your `SearchRequest` procedure is any good. It's probably got a join that sometimes duplicates data. – Gert Arnold Jun 15 '16 at 13:18
  • I am checking the same query that is coming in `query` variable on the same time. It is returning proper result. – Pawan Nogariya Jun 15 '16 at 13:43

1 Answers1

0

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;
Rahul Nikate
  • 6,192
  • 5
  • 42
  • 54
  • I am passing the variables in the same way. I posted only two lines of my code, I am doing the same thing in my code. If I would not do that it would throw an exception. It is running that means I am passing everything properly. – Pawan Nogariya Jun 15 '16 at 09:41