Dynamic LINQ to SharePoint and Projection

Question

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.

Answer 1

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