How to create a Dynamic LINQ Query Programmatically

 

Source :- http://blog.bvsoftware.com/post/2008/02/27/How-to-create-a-Dynamic-LINQ-Query-Programmatically.aspx

Creating a LINQ query in challenging because we don't know ahead of time which fields the user will complete. They are all optional and without anything selected we want to pull back all tickets. Normally, you'd write a query like this:

var ticket = (from t in db.cerberus_Tickets

where t.id == id

select t).Single();

In the example above we know that the t.id parameter will always be given. So how do you create a query in code when you don't know what fields to include in the WHERE clause ahead of time?

The first key is understanding the LINQ queries are not executed until they are used to enumerate through a collection. This part is key because it means we can create a query and change it in code as long as we don't try to look at the results first.

What we're going to do is create an IQueryable collection that contains all of our Ticket objects and we'll dynamically add our WHERE clause information.  Then we'll create a normal LINQ query that selects all of the matches from our IQueryable collection and handles paging. Because we don't actually enumerate the IQueryable collection that contains all our tickets, it won't actually pull back all of the tickets (which would take forever!). Instead, it will be "merged" with our normally LINQ query at run time when we enumerate over it.

1) Create our LINQ to SQL context objects

List<cerberus_Ticket> result = new List<cerberus_Ticket>();

cerberusDataContext db = new cerberusDataContext(connectionString);

2) Create an empty IQueryable collection containing all tickets. Note that this query doesn't actually select everything from the database yet. If it did this would take forever and effectively be filtering the database table in memory. That would not be a good design!

IQueryable<cerberus_Ticket> matches = db.cerberus_Tickets;

3) Add our WHERE clause information with custom logic to decide if the clauses should be added or not

 if (this.AgentIdField.Text.Trim().Length > 0)

{

     matches = matches.Where(a => a.AgentId == criteria.AgentId);

}

if (this.TicketIdField.Text.Trim().Length > 0)

{

     matches = matches.Where(a => a.TicketId.Contains(criteria.TicketId));

}

4) Create a second LINQ query that selects from the first one to sort and page the results.

// calculate start row based on page parameters passed in

int startRow = (pageNumber - 1) * pageSize;

var output = (from p in matches

orderby p.DateCreated descending

select p).Skip(startRow).Take(pageSize).ToList();

Again, I can't emphasize enough how cool it is that LINQ doesn't query the database until we call the ToList() at the end of the second statement. This delay in execution is the magic that lets us create dynamic queries on the fly.