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:
So, in SQL I would implement this something like
- 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.
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.
SELECT * FROM
SELECT ROW_NUMBER() OVER (ORDER BY FileDate DESC, AuthorName ASC) AS RowNumber, *
WHERE AuthorName LIKE 'Smit%'
) AS Results
WHERE RowNumber BETWEEN 20 AND 40