How to query with unknown number of where parameters in ASP.NET and C# with Linq2SQL?

Question

Hi,

I have a GridView using LinqDataSource to populate data from table Tickets. The LinqDataSource has AutoGenerateWhereClause="True" so that the where clause can be constructed dynamically like this:

<asp:LinqDataSource ID="dsGridView" runat="server" AutoGenerateWhereClause="True"
ontextTypeName="TicketsDataContext" TableName="Tickets" OrderBy="ID Descending"
EnableDelete="True" OnSelecting="dsGridView_Selecting">
<WhereParameters>
<asp:SessionParameter Name="AssignedTo" SessionField="user"/>
<asp:Parameter Name="Department" DefaultValue="" />
<asp:Parameter Name="Category" DefaultValue="" />
</WhereParameters>
</asp:LinqDataSource>

The Where parameters are used with the dropdownlist filters on the header of the gridview. They can be null so that dsGridView will return all records.

My gridview has paging enabled.

Table Ticket has a field called TimeSpent. I would like to calculate the total TimeSpent for all the tickets filtered and display it on Footer.

I could use Linq2SQL on gridView_DataBound to query ALL tickets' TimeSpent. However, I am confused how to get the total TimeSpent when the gridview is filtered.

I tried to get tickets from LinqDataSourceSelectEventArgs.Result, but it only returned the total TimeSpent for the current page of gridview, not for the whole table.

The problem is, I do not know how many Where parameters will present in the Selecting event. Department can be null and not shown up in WhereParameters, and so can Category.

Something like this:

TicketsDataContext db = new TicketsDataContext();
var query = from ticket in db.Tickets select ticket;
foreach (var param in dsGridView.WhereParameters
{
if (!string.IsNullOrEmpty(param.Value))
query.query.Where(...)
}

Does not work of course. Is there any idea how I could tackle this issue? Thanks in advance!

Updated: I ended up reusing the data returned from dsGridView in OnSelected event as below:

protected void dsGridView_Selected(Object sender, LinqDataSourceStatusEventArgs e)  
{  
    var totalTime = (e.Result as List<Ticket>).Sum(t => t.TimeSpent);  
    grvTickets.Columns[7].FooterText = "Sum: " + totalTime.ToString();  
}

Answer 1

Start with http://www.albahari.com/nutshell/predicatebuilder.aspx. They go into PredicateBuilder which is good for most of the scenarios you'll run into. Ultimately you may need to delve into expression trees, but that's a good deal more advanced.

From your comment below, it sounds like Dynamic Linq will be a better fit for you: http://weblogs.asp.net/scottgu/archive/2008/01/07/dynamic-linq-part-1-using-the-linq-dynamic-query-library.aspx

If that doesn't suffice, you'll have to build the expression trees yourself. Don't worry, what you want to do is definitely possible. Might get a bit tricky if you have to do it yourself though.

Answer 2

You could build up the query bit by bit. It works, albeit tedious and ugly to write.

TicketsDataContext db = new TicketsDataContext();
var query = from ticket in db.Tickets select ticket;

If (param.Name == "Department"){
    if (!string.IsNullOrEmpty(param.Value)){
        query = query.Where(c => c.Department == param.Value);
     }
}

If (param.Name == "Category"){
    if (!string.IsNullOrEmpty(param.Value)){
        query = query.Where(c => c.Category == param.Value);
    }
}

I'm not sure if the param.Name is valid and my C# my be slightly wrong but hopefully that'll get you started.

Answer 3

If you have a limited number of parameters in your where clause you can do the following:

TicketsDataContext db = new TicketsDataContext();
var query = from ticket in db.Tickets 
            where ticket.Department == (!string.IsNullOrEmpty(DepartmentParam.Value) ? DepartmentParam.Value : ticket.Department) &&
                  ticker.Category == (!string.IsNullOrEmpty(CategoryParam.Value) ? CategoryParam.Value : ticket.Category)
            select ticket;

That way if the parameter is blank then it simply compares the field with itself.