If you've been stuggling to create a dynamic LINQ query you're not alone. Thanks to some research, hard work and a smart co-worker I was able to implement some cool dynamic LINQ code in our help desk application.
In our helpdesk we have filter screens that look like this:
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.
Have any cool LINQ tips of your own to share?