使用 Linq to Entity 的“包含()”解决方案？

小开

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

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

小开

From 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));

小开

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

小开

You can fall back on hand coding some e-sql (note the keyword "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:

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

小开

最佳答案

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

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"
);
}
}

小开

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.

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])

小开

I think a Join in LINQ can be a walkaround.

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:

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

小开

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);
}

小开

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).

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.

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

小开

In addition to selected answer.

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