CODEWITHSUNDEEP
c#linqlinq-to-entities
aleczandru
aleczandru

Reputation: 5449

Getting items from a database between a specific number of values

Hi I recently learned entity framework and linq and I was asked to convert this stored procedure:

CREATE PROCEDURE [dbo].[GetBooks]
@numberOfBooksOnPage int,
@pageNumber int
    AS
    BEGIN

    WITH AllBooks AS
    (
        SELECT ROW_NUMBER() OVER(ORDER BY Id) AS RowId,
        Id , Name , Author , Description , PublicationDate , CategoryId
        FROM Books
    )

    SELECT Id , Name , Author , Description , PublicationDate , CategoryId
    FROM AllBooks
    WHERE RowId BETWEEN ((@pageNumber - 1) * @numberOfBooksOnPage) + 1 AND @pageNumber * @numberOfBooksOnPage
    ORDER BY RowID
    END

So far I only managed to get the total number of books to be displayed per page , but I still need to find a way to get the books via a pageNumber.

This is what I have so far:

var books = bookContext.Books.Take(numberOfBooksOnPage);

So how can I represent what is written in the stored procedure in my linq query?

Upvotes: 0

Views: 90

Answers (5)

Mike Dinescu
Mike Dinescu

Reputation: 55720

Use the Skip() method in conjunction with take

var books = bookContext.Books.OrderBy(b => b.Id).Skip(pageNumber * numberOfBooksOnPage).Take(numberOfBooksOnPage)

Upvotes: 1

NDJ
NDJ

Reputation: 5194

You want Skip -

var books = bookContext.Books.OrderBy(b => b.someField).Skip(pageNumber * numberOfBooksOnPage).Take(numberOfBooksOnPage);

Upvotes: 1

Giorgio Minardi
Giorgio Minardi

Reputation: 2775

As said if you want to page your result it's better to use Take in conjunction with Skip as in the previous answers. Linq to sql doesn't expose a Between method, this can be easily replaced with a Where clause. Alternatively you can write your own extension method:

public static IEnumerable<TSource> Between<TSource, TResult>
(
    this IEnumerable<TSource> source, Func<TSource, TResult> selector,
    TResult lowest, TResult highest
)
    where TResult : IComparable<TResult>
{
    return source.OrderBy(selector).
        SkipWhile(s => selector.Invoke(s).CompareTo(lowest) < 0).
        TakeWhile(s => selector.Invoke(s).CompareTo(highest) <= 0 );
}

And give it a try:

public class Book
{
    public string Name { get; set; }
    public int Pages { get; set; }
}    

[Test]
public void TestBooks()
{
    var listOfNumbers = new List<Book>() {new Book(){Pages = 10}, new Book(){Pages = 44}};
    var result = listOfNumbers.Between(x => x.Pages, 0, 29);
}

Upvotes: 1

M.Babcock
M.Babcock

Reputation: 18965

Paging in LINQ typically involves use of the Skip and Take extensions, so:

var numOfBooksPerPage = 20;
var currPage = 3;

var books = bookContext.Books.Skip(currPage * numOfBooksPerPage).Take(numOfBooksPerPage);

Upvotes: 1

Patryk Ćwiek
Patryk Ćwiek

Reputation: 14318

If you want to do an easy paging, here's a quick solution:

var books = bookContext.Books.OrderBy(x => x.Id).Skip(pageNo*booksPerPage).Take(booksPerPage);

Upvotes: 3

Related Questions