How to get the count from IQueryable

Question

I am implementing paging in my GridView. From this article, I need two methods:

public IQueryable BindEmployees(int startRowIndex, int maximumRows)
{
    EmployeeInfoDataContext dbEmp = new EmployeeInfoDataContext();
    var query = from emp in dbEmp.Employees
                join dept in dbEmp.Departments
                    on emp.DeptID equals dept.DeptID
                select new
                {
                    EmpID = emp.EmpID,
                    EmpName = emp.EmpName,
                    Age = emp.Age,
                    Address = emp.Address,
                    DeptName = dept.DepartmentName
                };

    return query.Skip(startRowIndex).Take(maximumRows);
} 

And

public int GetEmployeeCount()
{
    // How can I not repeat the logic above to get the count?
}

How can I get the value of the second method GetEmployeeCount from the first method BindEmployees ? I mean without repeating the logic (the query)?

Solution 1

One option would be:

public IQueryable BindEmployees(int startRowIndex, int maximumRows, out int count)
{
    EmployeeInfoDataContext dbEmp = new EmployeeInfoDataContext();
    var query = from emp in dbEmp.Employees
                join dept in dbEmp.Departments
                    on emp.DeptID equals dept.DeptID
                select new
                {
                    EmpID = emp.EmpID,
                    EmpName = emp.EmpName,
                    Age = emp.Age,
                    Address = emp.Address,
                    DeptName = dept.DepartmentName
                };

    count = query.Count();
    return query.Skip(startRowIndex).Take(maximumRows);
}

The other option is to pass the query into the paging function.

Solution 2

Make a function to use in both places:

//Can be private or public, your choice
private IQueryable<Employee> GetQuery()
{
    EmployeeInfoDataContext dbEmp = new EmployeeInfoDataContext();
    return from emp in dbEmp.Employees
                join dept in dbEmp.Departments
                    on emp.DeptID equals dept.DeptID
                select emp;
}

Then use it in both of your other functions:

public int GetEmployeeCount()
{
    return GetQuery().Count();
}

public IQueryable BindEmployees(int startRowIndex, int maximumRows)
{
    var query = from e in GetQuery()
                select new { /*Do your anonymous type here*/ };
    return query.Skip(startRowIndex).Take(maximumRows);
} 

Comments

[Homam]

Thanks, but It's an anonymous type.

[jonathanpeppers]

I'm suggesting to promote it to a true type, that makes it reusable. Your can switch to using an anonymous type when calling this function if desired.

[Marc Gravell]

honestly, the "c" in Marc isn't a typo ;p

[Merlyn Morgan-Graham]

"The other option is to pass the query into the paging function" - you may need to clarify this slightly. The paging function would have to be a generic function for this to work, since Skip/Take also operate only on IQueryable<T>.

[Homam]

♦: Thanks. But I wonder why can't I get count from IQueryable !

[Bryan Watts]

@Marc Gravell: D'oh! I've been following you forever and I knew that. Obviously I just woke up :-)

[Marc Gravell]

@Jack - well, you could; but the Count extension method is defined for IQueryable<T> - not IQueryable

[Sunil Raj]

@jonathanpeppers: In this case, the datacontext has to be shared between the methods. Otherwise you will get a runtime error.

[Deeps]

Hye Marc, Please tell me whats best in case of IQueryable<T> .Any() or .Count(). I get a IQueryable<T>, and both .Any() or .Count() takes time. I just have to check if it has any row. So I should use .Any(). Please tell which performs better.

[Marc Gravell]

@Deeps if in doubt, why don't you just measure which performs better?