How to get total count and list of records using the Entity Framework 6 SqlQuery

Question

I have some complex requirement where user can filter result based on several parameters. I have used Entity Framework SqlQuery to prepare the dynamic query. So in the application i wanted to display - Total No of Records available by applying the filter - Display only 25 (pagination records using the same filter)

I do not want to get all the records from the database server to app server. i have to get only 25 records and total no of filter result using the same query.

following are the code to get the result from database:

using (Entities dbEntities = new Entities())
                {
                    var resultQuery = dbEntities.AllResources.SqlQuery(searchQuery, parameters.ToArray());
                    int count = resultQuery.Count();
//Here i am getting an error "The SqlParameter is already contained by another SqlParameterCollection."
                    var searchResult = resultQuery.Skip(startRecord).Take(pageSize).ToList();
                }

What i have tried: i have called to separate query like

//For getting total count
int totalRecords = 0;
                using (Entities dbEntities = new Entities())
                {
                    var countQuery = dbEntities.AllResources.SqlQuery(searchQuery, parameters.ToArray());
                    totalRecords = countQuery.Count();
                }

For getting paged records (25 rec)

using (Entities dbEntities = new Entities())
                {
                    var resultQuery = dbEntities.AllResources.SqlQuery(searchQuery, parameters.ToArray());
                    var searchResult = resultQuery.Skip(startRecord).Take(pageSize).ToList();
                }

But still error is not resolved. However i have cloned the parameters and then clear that param but no luck.

SqlParameter[] parametersCount = new SqlParameter[parameters.Count];
parameters.CopyTo(parametersCount); 
var countQuery = dbEntities.AllResources.SqlQuery(searchQuery, parametersCount.ToArray());

I think the parameters are your less problem. If you hover the `Count`, `Skip` and `Take` methods you'll see that they are mapped to `Enumerable` methods, not `Queryable`, which means they will be executed *after* materializing the query result in memory. — Ivan Stoev, Jun 16 '17 at 12:55

score 0 · Answer 1 · answered Sep 08 '17 at 12:31

You need to either re-create SqlParameter instances, or (if you are using the stored procedure) you can just define one of the parameters you are passing to SP as an output one to retrieve the total records count in addition to the paginated records.

You will find some tips here (the post uses the simple parameter values, but you can use SqlParameter object as well).

How to get total count and list of records using the Entity Framework 6 SqlQuery

1 Answers1