26

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

Homam
  • 23,263
  • 32
  • 111
  • 187

3 Answers3

25

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.

Marc Gravell
  • 1,026,079
  • 266
  • 2,566
  • 2,900
  • "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`. – Merlyn Morgan-Graham Mar 27 '11 at 14:45
  • ♦: Thanks. But I wonder why can't I get count from IQueryable ! – Homam Mar 27 '11 at 14:50
  • 2
    @Jack - well, you could; but the Count extension method is defined for `IQueryable` - not `IQueryable` – Marc Gravell Mar 27 '11 at 15:01
  • Hye Marc, Please tell me whats best in case of IQueryable .Any() or .Count(). I get a IQueryable, 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. – Deeps Mar 22 '13 at 13:44
  • 4
    @Deeps if in doubt, why don't you just *measure* which performs better? – Marc Gravell Mar 22 '13 at 14:45
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);
}
jonathanpeppers
  • 26,115
  • 21
  • 99
  • 182
0

@Marc Gravell's answer solves the issue as stated, and is probably the best way to go. However, in the interest of providing options, you could also just repeat the join (since you don't need the anonymous type when counting):

private int GetEmployeeCount(EmployeeInfoDataContext dbEmp)
{
    var query = from emp in dbEmp.Employees
                join dept in dbEmp.Departments on emp.DeptID equals dept.DeptID
                select dept;

    return query.Count();
}
Bryan Watts
  • 44,911
  • 16
  • 83
  • 88