Equivalent to SQL IN clause

Question

I have an object called new_trexmail with a string attribute called new_contextline.

I am trying to get a list of objects where new_contextlineis is in a specific list.

The following code fails with an error: NotSupportedException: Invalid 'where' condition. An entity member is invoking an invalid property or method. 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 occurs, but I wonder if it is possible to make equialvalent for an IN clause using XRM.

If possible, how do I get XRM to execute SELECT * FROM new_trexmail WHERE new_contextline in ('aaa', 'hhh') ?

Thanks,

David

Answer 1

Check the (longer than necessary) LINQ list of restrictions , in particular the restriction on the where section:

The left side of the sentence should be the attribute name and the right side of the sentence should be the value. You cannot set the left side to constant. Both sides of a sentence cannot be constants. function supports String Contains, StartsWith, EndsWith and Equals.

Since test not a CRM attribute, you cannot call it Contains . However, one way to use this method is to use Dynamic Linq ", developed by ScottGu and 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;