3

How to build dynamic LINQ to SharePoint expression like this(but store "Bill", "Sam" in array):

// listing 1

// SPEntityModelDataContext generated with SPMetal
using (var db = new SPEntityModelDataContext("http://sharepoint/"))
{
    var res = db.OrgUnitToUser
        .Where(oo => oo.User.Title == "Bill" || oo.User.Title == "Sam")
        .ToList();
}

we try to use PredicateBuilder

This don't work:

// listing 2

using (var db = new SPEntityModelDataContext("http://sharepoint/"))
{
    var names = new String[] {"Bill", "Sam" };

    var inner = PredicateBuilder.False<OrgUnitToUserItem>();
    foreach (var name in names)
    {
        var temp = name;
        inner = inner.Or(pp => pp.User.Title == temp); // use projection
    }

    var res = db.OrgUnitToUser
        .Where(inner)
        .ToList(); // ERROR
}

Error: The query uses unsupported elements, such as references to more than one list, or the projection of a complete entity by using EntityRef/EntitySet.

For example this code work

// listing 3

var inner = PredicateBuilder.False<OrgUnitToUserItem>();
foreach (var name in names)
{
    var temp = name;
    inner = inner.Or(pp => pp.Title == temp); // without projection
}

var res = db.OrgUnitToUser
    .Where(inner)
    .ToList();

It is posible to use Compile():

// listing 4

using (var db = new SPEntityModelDataContext("http://sharepoint/"))
{
    var names = new String[] {"Bill", "Sam" };

    var inner = PredicateBuilder.False<OrgUnitToUserItem>();
    foreach (var name in names)
    {
        var temp = name;
        inner = inner.Or(pp => pp.User.Title == temp); // use projection
    }

    var res = db.OrgUnitToUser
        .Where(inner.Compile())
        .ToList();
}

No error, but listing 4 use Linq To Object - first get all User and iterate them. CAML not the same as in listing 1.

5
  • 2
    Can you please post your solution as answer and mark it as answer? Commented Feb 21, 2012 at 12:35
  • I'am sorry Listing 4 is't a solution (I edit question).
    – Alex
    Commented Feb 21, 2012 at 14:47
  • Hmmm looks like a solution :-) One other solution: Have you thought about the linq specification pattern? Thats how I have implemented it.
    – Smokefoot
    Commented Feb 21, 2012 at 22:04
  • Smokefoot, now another problem. How to add Or expression? For example: .Where(BuildOrExpressionContains<OrgUnitToUserItem>(tt => tt.User.Title, names) || tt.Id == 2)
    – Alex
    Commented Feb 21, 2012 at 22:32
  • Ditto on what @ToniFrankola said. If you move the code into an answer below.. then you can accept it and users can vote (more rep yay)!
    – Kit Menke
    Commented Feb 22, 2012 at 0:59

1 Answer 1

3

Solution

We build two methods. One to build Equal Expressions, and one to Contains Expressions

// Listing 5

private static Expression<Func<TElement, bool>> BuildOrExpressionEqual<TElement, TValue>(Expression<Func<TElement, TValue>> valueSelector, IEnumerable<TValue> values)
{
    if (null == valueSelector)
        throw new ArgumentNullException("valueSelector");
    if (null == values)
        throw new ArgumentNullException("values");

    var p = valueSelector.Parameters.Single();

    if (!values.Any())
        return e => false;

    var equals =
        values.Select(value =>
            (Expression)Expression.Equal(valueSelector.Body, Expression.Constant(value, typeof(TValue))));


    var body = equals.Aggregate<Expression>((accumulate, equal) => Expression.Or(accumulate, equal));

    return Expression.Lambda<Func<TElement, bool>>(body, p);
}


private static Expression<Func<TElement, bool>> BuildOrExpressionContains<TElement>(Expression<Func<TElement, string>> valueSelector, IEnumerable<string> values)
{
    if (null == valueSelector)
        throw new ArgumentNullException("valueSelector");
    if (null == values)
        throw new ArgumentNullException("values");

    var p = valueSelector.Parameters.Single();

    if (!values.Any())
        return e => false;

    MethodInfo method = typeof(string).GetMethod("Contains", new[] { typeof(string) });
    var equals = values.Select(value =>
            (Expression)Expression.Call(valueSelector.Body, method, Expression.Constant(value, typeof(string))));


    var body = equals.Aggregate<Expression>((accumulate, equal) => Expression.Or(accumulate, equal));

    return Expression.Lambda<Func<TElement, bool>>(body, p);
}

Usage:

// Listing 6

var names = new string[] {"Bill", "Sam"};

var res = db.OrgUnitToUser
    .Where(BuildOrExpressionContains<OrgUnitToUserItem>(tt => tt.User.Title, names))
    .ToList();

var res2 = db.OrgUnitToUser
    .Where(BuildOrExpressionEqual<OrgUnitToUserItem, string>(tt => tt.User.Title, names))
    .ToList();

Another question come from this solution - Dynamic LINQ to SharePoint

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.