Dynamic LINQ solution?

In our app we have a situation where the users can select from numerous criteria and get back a subset of a large number of documents. Also, supported are pagination and sort order. Criteria may include:

zero or more document types (any, company, personal, group, statement, invoice, etc).
filter by author last name (wild card) (i.e. LIKE Smit%)
pagination support (page 2 with page size = 20)
order by filedate desc, author name asc.

So, in SQL I would implement this something like

SELECT * FROM
(  
   SELECT ROW_NUMBER() OVER (ORDER BY FileDate DESC, AuthorName ASC) AS RowNumber, *
     FROM  Document
    WHERE AuthorName LIKE 'Smit%'
) AS Results
WHERE RowNumber BETWEEN 20 AND 40

But how does one build this dynamically in LINQ-to-SQL or LINQ-to-Entities? The number of options are too numerous to build static LINQ queries for all possible combinations.

Thanks.

Craig Johnston, Feb 24, 2010

Reply 1

here is a good tutorial by Scott. Look at the Skip() and Take() methods.

http://weblogs.asp.net/scottgu/archive/2007/06/29/linq-to-sql-part-3-querying-our-database.aspx

here is a sample too (its VB so its probably wrong :))
http://stackoverflow.com/questions/770909/linq-over-datatable-with-skip-and-take-method

Aaron Burton, Feb 26, 2010