linq - Equivalent to SQL IN clause

Question

I've got an entity called new_trexmail with a string attribute called new_contextline.

I'm trying to get a list of entities where new_contextlineis in a defined list.

The following code fails with the error : NotSupportedException: Invalid 'where' condition. An entity member is invoking an invalid property or method.

string[] test = new[]{"aaa", "hhh"};

var query = from n in New_trexmailSet
            where test.Contains(n.New_contextline)
            select n;

I understand why this error is being thrown but I'm wondering if it's possible to do the equiavalent of an IN clause using XRM.

If it is possible then how do I go about getting XRM to execute SELECT * FROM new_trexmail WHERE new_contextline in ('aaa', 'hhh')?

Thanks,

David

Answer 1

Check out the (longer than desired) list of LINQ limitations, particularly the limitation on the where clause:

The left side of the clause must be an attribute name and the right side of the clause must be a value. You cannot set the left side to a constant. Both the sides of the clause cannot be constants. Supports the String functions Contains, StartsWith, EndsWith, and Equals.

So since test isn't a CRM attribute, you can't call Contains on it. However, one way around this is to use "Dynamic Linq" as developed by ScottGu and as demonstrated below:

//must include the below using statements
//using System.Linq;
//using System.Linq.Dynamic;

var trexmailSet = New_trexmailSet;

string[] test = new[] { "aaa", "hhh" };

string whereClause = "";

foreach (string name in test)
{
    whereClause += string.Format("new_contextline = "{0}" OR ", name);
}

trexmailSet = trexmailSet.Where(whereClause.Substring(0, whereClause.Length - 4));

var query = from n in trexmailSet
            select n;