What’s the Linq to SQL equivalent to CEILING?

Question

How do I do this

SELECT  CEILING(COUNT(*) / 10) NumberOfPages
 FROM  MyTable

in Linq to SQL?

Answer 1

You dont use SQL CEILING, you use .NET ceiling (Math.Ceiling) in your LINQ query.

Answer 2

I don't think this is possible. A possible solution would be to get the total count and then figure it out in .NET code. Like below:

where query is IQueryable

  var itemsPerPage = 10; 
  var currentPage = 0; 
  var totalResults = query.Count(); 
  var myPagedResults = query.Skip(currentPage).Take(itemsPerPage);
  var totalPages = (int)Math.Ceiling((double)totalResults / (double)pageSize);

Answer 3

Many .NET methods are translated to SQL Server functions, such as most of the methods of the Math class and the String class. But there are some caveats.

Also have a look at the SqlMethods class, which exposes additional SQL Server function that doesn't have a .NET equivalent.

But you don't even need any of that in your case:

int numberOfPages;

using (var db = new MyDBDataContext())
{
   numberOfPages = (int)Math.Ceiling(db.Books.Count() / 10.0);
}