using variables in a dynamic linq query

Question

I am using Linq to Entities and have added the using stmt of using System.Linq.Dynamic; My goal is to pass in the whereClause variable into the emailList query (see screen shot).

Any thoughts?

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
After using @Michael suggestion I got it to work with the following:

HTML (notice I placed the field value in 'Value' attr.):

<asp:CheckBoxList ID="_checkboxGroups" runat="Server">
                            <asp:ListItem Text="Sid Dickens Lovers" Value="SidDickens_TF" Selected="False" />
                            <asp:ListItem Text="Rosamond Lover" Value="Rosamond_TF" Selected="false" />
                            <asp:ListItem Text="Wine and Cheese Lovers" Value="WineAndCheese_TF" Selected="false" />
                            <asp:ListItem Text="Good Clients" Value="IntDesign_TF" Selected="false" />
                            <asp:ListItem Text="Vendors" Value="Vendor_TF" Selected="false" />
                        </asp:CheckBoxList>

Code behind:

// determine # of items in asp:CheckBoxList
        var groupCount = _checkboxGroups.Items.Count;

        var conditions = new List<string>();

        for (int i = 0; i < groupCount; i++)
        {
            if (_checkboxGroups.Items[i].Selected)
            {
                conditions.Add(_checkboxGroups.Items[i].Value.ToString() + " == true");
            }
        }

        string whereClause = string.Join(" OR ", conditions.ToArray());


        ElanEntities3 db = new ElanEntities3();

        var emailList = (from c in db.vEmailListViewforSendings
                         orderby c.Email
                         select c).AsQueryable();

        emailList = emailList.Where(whereClause);

       _listViewClients.DataSource = emailList;

Solution 1

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

var email = from c in db.MailListViewForSendings
            order by c.ID
            select c;

// then you chain the Linq;
email = email.Where("CategoryID=3");

To use parameters:

var email = from c in db.MailListViewForSendings
            order by c.ID
            select c;

// then you chain the Linq;
email = email.Where("CategoryID=@0", 3);

UPDATE

Don't use StringBuilder, use List<string> instead, then concatenate it by string.Join:

using System;

using System.Collections.Generic;

public class Test
{
        public static void Main()
        {
             var conditions = new List<string>();
             conditions.Add("Lastname = 'Lennon'");
             conditions.Add("Firstname = 'John'");
             conditions.Add("Age = 40");

             Console.WriteLine(string.Join(" OR ", conditions.ToArray() ));
        }
}

Output:

Lastname = 'Lennon' OR Firstname = 'John' OR Age = 40

Live test: http://ideone.com/EFhnA

Solution 2

You need to pass an object that matches the parameter to IQueryable.Where( predicate )

So whereClause must be an object of this type:

Expression<Func<TSource, bool>>

---

Because you are ORing your where clauses not ANDing them, you will have to build one big where clause.

Assuming your data object is of type OBJ and it has bool properties P0 and P1:

bool filterP0 = _checkboxGroups[0].Selected;
bool filterP1 = _checkboxGroups[1].Selected;

Expression<Func<OBJ, bool>> predicate = o =>
(
    ( !filterP0 && !filterP1 )
    ||
    ( filterP0 && o.P0 )
    ||
    ( filterP1 && o.P1 )
);

var emailList =
    db.vEmailListViewForSendings
        .Where( predicate )
        .OrderBy( o => o.ID );

That's the gist anyway.

---

Or, if you really must build the predicate dynamically, you could use Joe Albahari's Predicate Builder.

Solution 3

You should be able to do it using Linq Dynamic Query:

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

This is what the library was designed for.

Comments

[Slavo]

In the code sample shown above - you are right. However, string arguments can be passed to versions of the Where() extension method on IQueryable that use dynamic LINQ. See Wiktor Zychla's answer.

[Nick Butler]

@Slavo Unless there is absolutely no other way, I would avoid using magic strings. If there was no other way I still wouldn't do it. Magic strings are opaque to the compiler and cause run-time exceptions.

[Slavo]

I agree, but that's another topic. The question only asks how.

[blub]

I have added the System.Linq.Dynamic using statement already, my issue is using the 'dynamic library' correctly. Is my formatting wrong?

[blub]

given that I don't know many '_checkBoxGroup' items will be selected, I'm not clear on how to chain 'OR' conditions in a dynamic way?

[blub]

any suggestions on how I might cast my whereClause variable to the type you mention? Thanks...

[Wiktor Zychla]

Try to switch from Linq-style syntax to extension method syntax: db.vEmailList.Where( whereClause.ToString() ).OrderBy( c => c.ID ). This should help.

[blub]

Thanks for your help... see my comments in my edits to the original post.