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

c#-4.0 dynamicquery entity-framework-6

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());
1
0
6/16/2017 12:30:52 PM

Popular Answer

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).

0
9/8/2017 12:31:03 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow