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 a complicated requirement that allows the user to select results based on a number of criteria. I created the dynamic query using Entity Framework SqlQuery. I thus intended to show - in the programme. Total No of Records available by applying the filter - Display only 25 (pagination records using the same filter)

I do not want to transfer all of the data from the database server to the application server. I need to use the same query to return a total of 25 records and all of the filter results.

The following code will get the database's results:

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 done is phone distinct inquiries like

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

to get paging 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 the problem has not been fixed. But after cloning the parameters and clearing the parameter, I was unable.

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

To obtain the entire number of records in addition to the paginated records, you must either create newSqlParameter examples, or (if using the saved procedure).

The article utilises basic parameter values, however you may follow the advice found in here.SqlParameter a second item).

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