How to do a Count() before a ToList() on records retrieved with a Stored Procedure?

Question

Here's my code:

// grid query
var data = ctx.spRewards(Month, Year, null, MedicalID).Select(p => new
{
    MedicalID = p.MedicalID,
    DateReward = p.DateReward,
    Medical = p.Medical,
    AmountRefunds = p.AmountRefunds,
    AmountActivitiesStart = p.AmountActivitiesStart,
    AmountActivitiesEnd = p.AmountActivitiesEnd,
    AmountActivities = p.AmountActivities,
    AmountTotal = p.AmountTotal,
    Month = p.Month,
    Year = p.Year
});

// some further filters that will be attached in case

// grid order
data = data.OrderBy(p => p.DateReward).ThenBy(p => p.MedicalID);

// grid data
var dataTotal = data.Count();
if (formData.length >= 0)
{
    data = data.Skip(formData.start).Take(formData.length);
}
var dataFiltered = data.ToList();
return Json(new { data = dataFiltered, recordsFiltered = dataTotal, recordsTotal = dataTotal });

But once I try to do var dataFiltered = data.ToList();, I get a The result of a query cannot be enumerated more than once.

My intent is to return just the count of records first (for get just the amount of filtered data, without download all records in memory and than Count, which would take time and resources), than paginate it with Skip/Take.

Usually this works using IQueryable<a> on tables:

var data = ctx.SomeTable.AsNoTracking().Select(p => new
{
    //
})

but it doesn't calling directly a Stored Procedure within the DB. I've tried to convert data from IEnumerable<a> to IQueryable<a> with ctx.spRewards(Month, Year, null, MedicalID).AsQueryable(), but I got the same error.

Somethings that I need to configure?

EDIT: added whole "actual" code suggested by answer, still not working:

var dataQuery = ctx.spRewards(Month, Year, null, MedicalID).AsQueryable().Select(p => new
{
    MedicalID = p.MedicalID,
    DateReward = p.DateReward,
    Medical = p.Medical,
    AmountRefunds = p.AmountRefunds,
    AmountActivitiesStart = p.AmountActivitiesStart,
    AmountActivitiesEnd = p.AmountActivitiesEnd,
    AmountActivities = p.AmountActivities,
    AmountTotal = p.AmountTotal,
    Month = p.Month,
    Year = p.Year
});

// grid - filters
string searchValue = Request.Form.GetValues("search[value]")?.FirstOrDefault()?.ToLower();
if (!string.IsNullOrEmpty(searchValue))
{
    dataQuery = dataQuery.Where(p =>
          p.Medical.ToLower().Contains(searchValue) ||
          p.AmountRefunds.ToString().ToLower().Contains(searchValue) ||
          p.AmountActivitiesStart.ToString().ToLower().Contains(searchValue) ||
          p.AmountActivitiesEnd.ToString().ToLower().Contains(searchValue) ||
          p.AmountTotal.ToString().ToLower().Contains(searchValue)
    );
}

// grid - order
string orderColumnId = Request.Form.GetValues("order[0][column]")?.FirstOrDefault();
string orderColumn = Request.Form.GetValues("columns[" + orderColumnId + "][data]")?.FirstOrDefault();
string orderDir = Request.Form.GetValues("order[0][dir]")?.FirstOrDefault();
if (!string.IsNullOrEmpty(orderColumn))
{
    if (orderDir == "desc")
    {
        dataQuery = dataQuery.OrderByDescending(orderColumn);
    }
    else
    {
        dataQuery = dataQuery.OrderBy(orderColumn);
    }
}
else
{
    dataQuery = dataQuery.OrderBy(p => p.DateReward).ThenBy(p => p.MedicalID);
}

    // grid - result
var dataClone = dataQuery.CloneQuery();
var dataTotal = dataQuery.Count();
if (formData.length >= 0)
{
    dataClone = dataClone.Skip(formData.start).Take(formData.length);
}
var dataFiltered = dataClone.ToList();
return Json(new { data = dataFiltered, recordsFiltered = dataTotal, recordsTotal = dataTotal });

EDIT 2: added spRewards definition:

public virtual ObjectResult<spRewards_Result> spRewards(Nullable<int> month, Nullable<int> year, Nullable<int> clinicID, Nullable<int> medicalID)
{
    var monthParameter = month.HasValue ?
        new ObjectParameter("Month", month) :
        new ObjectParameter("Month", typeof(int));

    var yearParameter = year.HasValue ?
        new ObjectParameter("Year", year) :
        new ObjectParameter("Year", typeof(int));

    var clinicIDParameter = clinicID.HasValue ?
        new ObjectParameter("ClinicID", clinicID) :
        new ObjectParameter("ClinicID", typeof(int));

    var medicalIDParameter = medicalID.HasValue ?
        new ObjectParameter("MedicalID", medicalID) :
        new ObjectParameter("MedicalID", typeof(int));

    return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction<spRewards_Result>("spRewards", monthParameter, yearParameter, clinicIDParameter, medicalIDParameter);
}

Answer 1

I think your issue is that you are using a stored procedure with Entity Framework, which creates a forward only result set.

Using an extension method, you can create a new query with the same conditions:

public static class IQueryableExt 
{
    public static IQueryable<T> CloneQuery<T>(this IQueryable<T> q) => q.Provider.CreateQuery<T>(q.Expression);
}

Then you can use the original query for the total count and the cloned query for the filtered records:

// grid data
var data2 = data.CloneQuery();
var dataTotal = data.Count();

if (formData.length >= 0)
{
    data2 = data2.Skip(formData.start).Take(formData.length);
}

var dataFiltered = data2.ToList();