linq query dynamically add where condition ?

Question

I want to filter records dynamically on user choice. The user may choose to show the debit amount greater than credit or credit amount greater than debit. Accordingly I want to put a condition similar to either totDebit>totCredit orTotCredit>totDebit`

Dim query = _
        From product In Bills.AsEnumerable() _
        Group product By accode = product.Field(Of String)("ACCODE"), _
        BILLNO = product.Field(Of String)("BILLNO") Into g = Group _
        Let totDebit = g.Sum(Function(proudct) proudct.Field(Of Decimal)("DEBIT")) _
        Let totCredit = g.Sum(Function(proudct) proudct.Field(Of Decimal)("CREDIT")) _
        Select New With _
         { _
            .ACCODE = accode, _
            .billno = BILLNO, _
            .TOTALDEBIT = totDebit, _
            .TOTALCREDIT = totCredit, _
            .BALNACE = totDebit - totCredit _
          }

How can this be done?

Answer 1

I think you're looking for Dynamic Query Library. Not included in Linq but availble for download from Scott Guthrie's blog:

http://weblogs.asp.net/scottgu/archive/2008/01/07/dynamic-linq-part-1-using-the-linq-dynamic-query-library.aspx

Both the VB and C# DynamicQuery samples include a source implementation of a helper library that allows you to express LINQ queries using extension methods that take string arguments instead of type-safe language operators.

You can apply strings to filter the exection:

myData.DynamicWhere(string.Format("{0}.Contains(\"{1}\")", filter.field, filter.data.value));

Answer 2

In the code you h ave shownm you've merely built a query; you haven't executed it yet. And, at any point before you excute it, you may continue to build it:

 Dim query = _ 
    From product In Bills.AsEnumerable() _ 
    Group product By accode = product.Field(Of String)("ACCODE"), _
   ' etc as above....

   If onlyCredits Then
       query = query.Where(Function(proudct) product.TOTALCREDIT > product.TOTALDEBIT)
   Elseif onlyDebits Then
       query = query.Where(Function(proudct) product.TOTALCREDIT < product.TOTALDEBIT)
   Endif

Note, I'm a C# guy, so please forgive minor syntax errors....