'Contains()' 使用 Linq to Entities 的解决方法？

'Contains()' workaround using Linq to Entities?

提问by James Bloomer

I'm trying to create a query which uses a list of ids in the where clause, using the Silverlight ADO.Net Data Services client api (and therefore Linq To Entities). Does anyone know of a workaround to Contains not being supported?

我正在尝试使用 Silverlight ADO.Net 数据服务客户端 api（以及 Linq To Entities）创建一个在 where 子句中使用 id 列表的查询。有没有人知道不支持包含的解决方法？

I want to do something like this:

我想做这样的事情：

List<long?> txnIds = new List<long?>();
// Fill list 

var q = from t in svc.OpenTransaction
        where txnIds.Contains(t.OpenTransactionId)
        select t;

---

---

Tried this:

试过这个：

var q = from t in svc.OpenTransaction
where txnIds.Any<long>(tt => tt == t.OpenTransactionId)
select t;

But got "The method 'Any' is not supported".

但得到“不支持方法'Any'”。

采纳答案by Shimmy Weitzhandler

Update:EF ≥ 4 supports ">Containsdirectly (Checkout ">Any), so you don't need any workaround.

更新：EF ≥ 4">Contains直接支持(Checkout ">Any)，因此您不需要任何解决方法。

public static IQueryable<TEntity> WhereIn<TEntity, TValue>
  (
    this ObjectQuery<TEntity> query,
    Expression<Func<TEntity, TValue>> selector,
    IEnumerable<TValue> collection
  )
{
  if (selector == null) throw new ArgumentNullException("selector");
  if (collection == null) throw new ArgumentNullException("collection");
  if (!collection.Any()) 
    return query.Where(t => false);

  ParameterExpression p = selector.Parameters.Single();

  IEnumerable<Expression> equals = collection.Select(value =>
     (Expression)Expression.Equal(selector.Body,
          Expression.Constant(value, typeof(TValue))));

  Expression body = equals.Aggregate((accumulate, equal) =>
      Expression.Or(accumulate, equal));

  return query.Where(Expression.Lambda<Func<TEntity, bool>>(body, p));
}

//Optional - to allow static collection:
public static IQueryable<TEntity> WhereIn<TEntity, TValue>
  (
    this ObjectQuery<TEntity> query,
    Expression<Func<TEntity, TValue>> selector,
    params TValue[] collection
  )
{
  return WhereIn(query, selector, (IEnumerable<TValue>)collection);
}

USAGE:

用法：

public static void Main()
{
  using (MyObjectContext context = new MyObjectContext())
  {
    //Using method 1 - collection provided as collection
    var contacts1 =
      context.Contacts.WhereIn(c => c.Name, GetContactNames());

    //Using method 2 - collection provided statically
    var contacts2 = context.Contacts.WhereIn(c => c.Name,
      "Contact1",
      "Contact2",
      "Contact3",
      "Contact4"
      );
  }
}

回答by AndreasN

I'm not sure about Silverligth, but in linq to objects i always use any() for these queries.

我不确定 Silverligth，但在 linq to objects 我总是使用 any() 进行这些查询。

var q = from t in svc.OpenTranaction
        where txnIds.Any(t.OpenTransactionId)
        select t;

回答by James Bloomer

From MSDN:

从MSDN：

static Expression<Func<TElement, bool>> BuildContainsExpression<TElement, TValue>(
    Expression<Func<TElement, TValue>> valueSelector, IEnumerable<TValue> values)
{
    if (null == valueSelector) { throw new ArgumentNullException("valueSelector"); }
    if (null == values) { throw new ArgumentNullException("values"); }
    ParameterExpression p = valueSelector.Parameters.Single();

    // p => valueSelector(p) == values[0] || valueSelector(p) == ...
    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);
} 

and the query becomes:

查询变为：

var query2 = context.Entities.Where(BuildContainsExpression<Entity, int>(e => e.ID, ids));

回答by Phani Raj

Here's an example where I demonstrate how to write set-based queries using the DataServiceContext : http://blogs.msdn.com/phaniraj/archive/2008/07/17/set-based-operations-in-ado-net-data-services.aspx

这是我演示如何使用 DataServiceContext 编写基于集合的查询的示例：http: //blogs.msdn.com/phaniraj/archive/2008/07/17/set-based-operations-in-ado-net-data -services.aspx

回答by Rob Fonseca-Ensor

You can fall back on hand coding some e-sql (note the keyword "it"):

您可以重新编写一些 e-sql（注意关键字“it”）：

return CurrentDataSource.Product.Where("it.ID IN {4,5,6}"); 

Here is the code that I used to generate some e-sql from a collection, YMMV:

这是我用来从集合 YMMV 生成一些 e-sql 的代码：

string[] ids = orders.Select(x=>x.ProductID.ToString()).ToArray();
return CurrentDataSource.Products.Where("it.ID IN {" + string.Join(",", ids) + "}");

回答by jrojo

Thanks very much. WhereIn extension method was enough for me. I profiled it and generated the same SQL command to the DataBase as e-sql.

非常感谢。WhereIn 扩展方法对我来说已经足够了。我对其进行了分析，并为数据库生成了与 e-sql 相同的 SQL 命令。

public Estado[] GetSomeOtherMore(int[] values)
{
    var result = _context.Estados.WhereIn(args => args.Id, values) ;
    return result.ToArray();
}

Generated this:

生成了这个：

SELECT 
[Extent1].[intIdFRLEstado] AS [intIdFRLEstado], 
[Extent1].[varDescripcion] AS [varDescripcion]
FROM [dbo].[PVN_FRLEstados] AS [Extent1]
WHERE (2 = [Extent1].[intIdFRLEstado]) OR (4 = [Extent1].[intIdFRLEstado]) OR (8 = [Extent1].[intIdFRLEstado])

回答by Gabriel Chung

I think a Join in LINQ can be a walkaround.

我认为在 LINQ 中加入可以是一种解决方法。

I haven't tested the code though. Hope it helps. Cheers. :-)

不过我还没有测试代码。希望能帮助到你。干杯。:-)

List<long?> txnIds = new List<long?>();
// Fill list 

var q = from t in svc.OpenTransaction
        join tID in txtIds on t equals tID
        select t;

Join in LINQ:

加入LINQ：

http://weblogs.asp.net/salimfayad/archive/2008/07/09/linq-to-entities-join-queries.aspx

http://weblogs.asp.net/salimfayad/archive/2008/07/09/linq-to-entities-join-queries.aspx

回答by James Bloomer

To complete the record, here's the code I finally used (error checking omitted for clarity)...

为了完成记录，这是我最终使用的代码（为清楚起见省略了错误检查）...

// How the function is called
var q = (from t in svc.OpenTransaction.Expand("Currency,LineItem")
         select t)
         .Where(BuildContainsExpression<OpenTransaction, long>(tt => tt.OpenTransactionId, txnIds));



 // The function to build the contains expression
   static System.Linq.Expressions.Expression<Func<TElement, bool>> BuildContainsExpression<TElement, TValue>(
                System.Linq.Expressions.Expression<Func<TElement, TValue>> valueSelector, 
                IEnumerable<TValue> values)
        {
            if (null == valueSelector) { throw new ArgumentNullException("valueSelector"); }
            if (null == values) { throw new ArgumentNullException("values"); }
            System.Linq.Expressions.ParameterExpression p = valueSelector.Parameters.Single();

            // p => valueSelector(p) == values[0] || valueSelector(p) == ...
            if (!values.Any())
            {
                return e => false;
            }

            var equals = values.Select(value => (System.Linq.Expressions.Expression)System.Linq.Expressions.Expression.Equal(valueSelector.Body, System.Linq.Expressions.Expression.Constant(value, typeof(TValue))));
            var body = equals.Aggregate<System.Linq.Expressions.Expression>((accumulate, equal) => System.Linq.Expressions.Expression.Or(accumulate, equal));
            return System.Linq.Expressions.Expression.Lambda<Func<TElement, bool>>(body, p);
        }

回答by Shannon

Sorry new user, I would have commented on the actual answer, but it seems I can't do that yet?

抱歉，新用户，我会对实际答案发表评论，但似乎我还不能这样做？

Anyway, in regards to the answer with sample code for BuildContainsExpression(), be aware that if you use that method on database Entities (i.e. not in-memory objects) and you are using IQueryable, that it actually has to go off to the database since it basically does a lot of SQL "or" conditions to check the "where in" clause (run it with SQL Profiler to see).

无论如何，关于 BuildContainsExpression() 示例代码的答案，请注意，如果您在数据库实体（即不是内存对象）上使用该方法并且您使用的是 IQueryable，则它实际上必须转到数据库因为它基本上做了很多SQL“或”条件来检查“where in”子句（用SQL Profiler运行它来查看）。

This can mean, if you are refining an IQueryable with multiple BuildContainsExpression(), it won't turn it in to one SQL statement that gets run at the end as you expect.

这可能意味着，如果您正在使用多个 BuildContainsExpression() 优化 IQueryable，它不会将其转换为一个 SQL 语句，如您预期的那样在最后运行。

The workaround for us was to use multiple LINQ joins to keep it to one SQL call.

我们的解决方法是使用多个 LINQ 连接来保持一个 SQL 调用。

回答by smg

In addition to selected answer.

除了选择答案。

Replace Expression.Orwith Expression.OrElseto use with Nhibernate and fix Unable to cast object of type 'NHibernate.Hql.Ast.HqlBitwiseOr' to type 'NHibernate.Hql.Ast.HqlBooleanExpression'exception.

更换Expression.Or与Expression.OrElse与NHibernate和修复使用Unable to cast object of type 'NHibernate.Hql.Ast.HqlBitwiseOr' to type 'NHibernate.Hql.Ast.HqlBooleanExpression'异常。