matching string to item ids in LINQ

Question

I have a string:

strCheckedCategories = "2;"

an EntityList representing a SharePoint list, with item IDs from 1 to 21:

EntityList<VendorSearchesItem> vendorSearches = 
    dataContext.GetList<VendorSearchesItem>("Vendor Searches");

a LINQ query returning fields from two SharePoint lists that are joined to the "Vendor Searches" list:

var vendorSearchesQuery = (from s in vendorSearches
                           orderby s.VendorID.Title
                           select new
                           {
                               Vendor = s.VendorID.Title,
                               Website = s.VendorID.VendorWebsite,
                               VendorID = s.VendorID.Id,
                               SearchType = s.SearchTypeID.Title,
                               SearchTypeId = s.SearchTypeID.Id
                           });

and another LINQ query returning only the items where the item ID is in the list:

var q2 = from m2 in vendorSearchesQuery 
         where strCheckedCategories.Contains(m2.SearchTypeId.ToString())
         select m2

The problem is that, in addition to returning the item with ID 2 (desired result) the query also returns items with ID 12, 20, and 21. How can I fix that?

Answer 1

So, fundamentally, what you want to do here is have an IN clause in which you specify a bunch of values for a field and you want rows who's value for that column is in that set.

CAML does actually have an IN clause which you could use, but sadly LINQ to Sharepoint doesn't provide any means of generating an IN clause; it's simply not supported by the query provider.

You're trying to use a bit of a hack to get around that problem by trying to do a string comparison rather than using the proper operators, and you're running into the pitfals of stringifying all of your operations. It's simply not well suited to the task.

Since, as I said, you cannot get LINQ to SharePoint to use an IN, one option would simply be to not use LINQ, build the CAML manually, and execute it using the standard server object model. But that's no fun.

What we can do is have a series of OR checks. We'll see if that column value is the first value, or the second, or the third, etc. for all values in your set. This is effectively identical to an IN clause, it's just a lot more verbose.

Now this brings us to the problem of how to OR together an unknown number of comparisons. If it were ANDs it'd be easy, we'd just call Where inside of a loop and it would AND those N clauses.

Instead we'll need to use expressions. We can manually build the expression tree ourselves of a dynamic number of OR clauses and then the query provider will be able to parse it just fine.

Our new method, WhereIn, which will filter the query to all items where a given property value is in a set of values, will need to accept a query, a property selector of what property we're using, and a set of values of the same type to compare it to. After we have that it's a simple matter of creating the comparison expression of the property access along with each key value and then ORing all of those expressions.

public static IQueryable<TSource> WhereIn<TSource, TKey>(
    this IQueryable<TSource> query,
    Expression<Func<TSource, TKey>> propertySelector,
    IEnumerable<TKey> values)
{
    var t = Expression.Parameter(typeof(TSource));
    Expression body = Expression.Constant(false);
    var propertyName = ((MemberExpression)propertySelector.Body).Member.Name;

    foreach (var value in values)
    {
        body = Expression.OrElse(body,
            Expression.Equal(Expression.Property(t, propertyName),
                Expression.Constant(value)));
    }

    return query.Where(Expression.Lambda<Func<TSource, bool>>(body, t));
}

Now to call it we just need the query, the property we're filtering on, and the collection of values:

var q2 = vendorSearchesQuery.WhereIn(vendor => vendor.SearchTypeId
    , strCheckedCategories.Split(';'));

And voila.

While I'd expect that to work as is, you may need to call the WhereIn before the Select. It may not work quite right with the already mapped SearchTypeId.

Answer 2

You should probably use a Regex, but if you want a simpler solution then I would avoid string searching and split those strings to an array:

string strCheckedCategories = "2;4;5;7;12;16;17;19;20;21;";
string[] split = strCheckedCategories.Split(';');

It will create an empty entry in the array for the trailing semicolon delimiter. I would check for that and remove it if this is a problem:

strCheckedCategories.TrimEnd(';');

Finally now you can change your where clause:

where split.Contains(m2.SearchTypeId.ToString())

If you have a very large list it is probably worth comparing integers instead of strings by parsing strCheckedCategories into a list of integers instead:

int[] split = strCheckedCategories.Split(';').Select(x => Convert.ToInt32(x)).ToArray();

Then you can do a quicker equality expression:

where split.Contains(m2.SearchTypeId)

Answer 3

try:

strCheckedCategories.Split(new []{';'}).Any(x => x == m2.SearchTypeId.ToString())

Contains will do a substring match. And "20" has a substring "2".

Answer 4

var q2 = from m2 in vendorSearchesQuery 
         where strCheckedCategories.Split(';').Contains(m2.SearchTypeId.ToString())
         select m2

Answer 5

var q2 = from m2 in vendorSearchesQuery 
         where strCheckedCategories.Contains(";" + m2.SearchTypeId + ";")
         select m2

And your strCheckedCategories should always end with ; and start with ;, for example ;2;, ;2;3;, ...

NOTE: This trick works only when your SearchTypeId should always not contain ;. I think you should use another kind of separator like \n or simply store your checked categories in a list or some array. That's the more standard way to do.