Take the 2-minute tour ×
SharePoint Stack Exchange is a question and answer site for SharePoint enthusiasts. It's 100% free, no registration required.

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.

share|improve this question
2  
Can you please post your solution as answer and mark it as answer? –  Toni Frankola Feb 21 '12 at 12:35
    
I'am sorry Listing 4 is't a solution (I edit question). –  Alex Feb 21 '12 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 Feb 21 '12 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 Feb 21 '12 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 Feb 22 '12 at 0:59
add comment

1 Answer

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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