Dynamic LINQ solution?

 
128   96.7
Feb 24, 2010
 
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.
 



254   99.5
Feb 26, 2010
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