等效于SQL IN子句 [英] Equivalent to SQL IN clause

问题描述

我有一个名为new_trexmail的实体，其字符串属性为new_contextline.

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

我正在尝试获取已定义列表中new_contextline位于的实体的列表.

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

以下代码因错误而失败:NotSupportedException: Invalid 'where' condition. An entity member is invoking an invalid property or method.

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;

我理解为什么会引发此错误，但是我想知道是否有可能使用XRM等效于IN子句.

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.

如果可能的话，我该如何使XRM执行SELECT * FROM new_trexmail WHERE new_contextline in ('aaa', 'hhh')?

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

谢谢

大卫

推荐答案

签出(比期望的更长) LINQ限制列表，尤其是对where子句的限制:

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

子句的左侧必须是属性名称，右侧是 子句的一侧必须是一个值.您不能将左侧设置为 持续的.子句的两面都不能为常数.技术支持 字符串函数包含，StartsWith，EndsWith和Equals.

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.

因此，由于test不是CRM属性，因此无法在其上调用Contains.但是，解决此问题的一种方法是使用"动态Linq "由ScottGu开发，如下所示:

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;

这篇关于等效于SQL IN子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！