Dynamic LINQ Multiple Where Clause

Question

Hello all.

Still really struggling with this and appear to be going round in circles.

I have the following code that is driving me nuts. It should populate a list of items to be used in an autocomplete text box:

public string[] GetAutoComplete(string prefixText, int count)
    {
            string memberid = HttpContext.Current.Session["MemberID"].ToString(); 
            string locationid = HttpContext.Current.Session["LocationID"].ToString();
            string inhouse = HttpContext.Current.Session["Inhouse"].ToString();
            string supplier = HttpContext.Current.Session["Supplier"].ToString();
            string groupw = HttpContext.Current.Session["Group"].ToString();
            string external = HttpContext.Current.Session["External"].ToString();

            MyEnts autocomplete = new MyEnts();

            var r = from p in autocomplete.tblAutoCompletes
                        where p.MemberId == memberid && p.LocationId == locationid && p.ACItem.Contains(prefixText)
                        select p.ACItem;

            if (inhouse == "Inhouse")
                r = r.Where(p => p == inhouse);

            if (supplier == "Supplier")
                r = r.Where(p => p == supplier);

            if (groupw == "Group")
                r = r.Where(p => p == groupw);

            if (external == "External")
                r = r.Where(p => p == external);

            r.OrderBy(p => p);

            return r.ToArray();

What I am trying to retrieve with the dynamic where clause along the lines of the following.

Should inhouse = "Inhouse", then the list of items should include the word "Inhouse". If inhouse != "Inhouse", the word "Inhouse" should be excluded from the list.

This same logic should then be applied across the different where clauses i.e. Supplier, Group, External.

I genuinely have tried lots of different methods but I cannot for the life of me get the thing to work and it's frustrating me somewhat.

If anyone can suggest a way of doing this, you will either get a big kiss or a big frosty beer should our paths ever cross.

Answer 1

Ricardo,

not sure if it'll help in any way at all, but perhaps you could try Contains(), rather than == i.e.:

if (groupw == "Group")
    r = r.Where(p => p.Contains(groupw));

sorry to not offer a more fully featured answer - this was 'top of the head' stuff :)

jim

[edit] - just realised, this won't help with the 'not in' list issue. i'll have a think...

Answer 2

Wouldn't each of your Where clauses just need to contain a Contains criteria and some Not Contains?

if (inhouse == "Inhouse")
     r = r.Where(p => p.Contains(inhouse) && !p.Contains("Supplier") && !p.Contains("Group") && !p.Contains("External"));

Answer 3

Not exactly sure about your problem here but if you want to exclude then shouldn't the code be something like

 if (inhouse == "Inhouse")
                r = r.Where(p => p == inhouse);
 else
                r = r.Where(p => p != inhouse);

Oh! if you want just exclusion then the code should be something like

if (inhouse != "Inhouse")
                    r = r.Where(p => p != inhouse);

Answer 4

If the set of values to include/exclude is known at compile-time (as appears to be the case in your example), I think this can be managed with one query:

string memberid = HttpContext.Current.Session["MemberID"].ToString(); 
string inhouse = HttpContext.Current.Session["Inhouse"].ToString();
string supplier = HttpContext.Current.Session["Supplier"].ToString();

bool includeInHouse = (inhouse == "Inhouse");
bool includeSupplier = (supplier == "Supplier");

MyEnts autocomplete = new MyEnts();

var r = from p in autocomplete.tblAutoCompletes
            where (p.MemberId == memberid && p.LocationId == locationid && p.ACItem.Contains(prefixText))
            && (includeInHouse || (p.ACItem != "InHouse"))
            && (includeSupplier || (p.ACItem != "Supplier"))
            select p.ACItem;

r.OrderBy(p => p.ACItem);

return r.ToArray();

I've eliminated a couple cases for brevity.

Answer 5

Sorted.

var r = from p in autocomplete.tblAutoCompletes where p.MemberId == memberid && p.LocationId == locationid && p.ACItem.Contains(prefixText) select p.ACItem;

        if (inhouse != "Inhouse")
            r = r.Where(p => p != "Inhouse");

        if (supplier != "Supplier")
            r = r.Where(p => p != "Supplier");

        if (groupw != "Group")
            r = r.Where(p => p != "Group");

        if (external != "External")
            r = r.Where(p => p != "External");

        r = r.OrderBy(p => p);

        return r.ToArray();

I had to set the exception in quotation marks as the session vlaue was inappropriate and wouldn't have picked out anything from the list.

Thanks to all those contributing and helping me out.