[Source: http://geekswithblogs.net/EltonStoneman]
Normal
0
false
false
false
EN-US
X-NONE
X-NONE
/* Style Definitions */
table.MsoNormalTable
{mso-style-name:”Table Normal”;
mso-tstyle-rowband-size:0;
mso-tstyle-colband-size:0;
mso-style-noshow:yes;
mso-style-priority:99;
mso-style-parent:””;
mso-padding-alt:0cm 5.4pt 0cm 5.4pt;
mso-para-margin-top:0cm;
mso-para-margin-right:0cm;
mso-para-margin-bottom:10.0pt;
mso-para-margin-left:0cm;
mso-pagination:widow-orphan;
font-size:11.0pt;
mso-bidi-font-size:10.0pt;
font-family:”Calibri”,”sans-serif”;
mso-ascii-font-family:Calibri;
mso-ascii-theme-font:minor-latin;
mso-hansi-font-family:Calibri;
mso-hansi-theme-font:minor-latin;
mso-bidi-font-family:”Times New Roman”;
mso-bidi-theme-font:minor-bidi;}
LINQ to Entities doesn’t contain a definition for Contains(), so you have a couple of options where you want to write a LINQ query which operates like a WHERE IN() clause in SQL.
The first option is an extension to ObjectQuery which lets you specify a collection to use as selection criteria, something like the WhereIn extension suggested on StackOverflow. This lets you write code like this:
var ids = new string[] { “3012472”, “3012473”, “3012474”, “3012475” };
var query = context.Employee
.WhereIn(e => e.EmployeeId, ids)
.OrderBy(e => e.EmployeeId);
There are a couple of issues with the extension code suggested though – the first is that if the collection is empty, the WhereIn doesn’t add any criteria, so the original query is run in full. In SQL if you have an empty WHERE IN() clause, you’ll get no results so I’d prefer WhereIn to behave in the same way.
More importantly, there’s a limit to how big a collection WhereIn can cope with. Pass it anything over about 3,000 items and it’ll bomb out with a nasty StackOverflowException, which will likely take out your AppDomain and not give you any notification.
An amended WhereIn addresses the first issue and prevents the second:
/// <summary>
/// Adds an equivalent SQL WHERE IN() clause to the query, restricting results to a given range
/// </summary>
/// <typeparam name=”TEntity”>Type of entity to query</typeparam>
/// <typeparam name=”TValue”>Type of value to query against</typeparam>
/// <param name=”query”>Existing query</param>
/// <param name=”selector”>Expression to retrieve query field</param>
/// <param name=”collection”>Collection of values to limit query</param>
/// <returns>Query with added WHERE IN() clause</returns>
public static IQueryable<TEntity> WhereIn<TEntity, TValue>
(
this ObjectQuery<TEntity> query,
Expression<Func<TEntity, TValue>> selector,
IEnumerable<TValue> collection
)
{
ParameterExpression p = selector.Parameters.Single();
//if there are no elements to the WHERE clause,
//we want no matches:
if (!collection.Any()) return query.Where(x=>false);
if (collection.Count() > 3000) //could move this value to config
throw new ArgumentException(“Collection too large – execution will cause stack overflow”, “collection”);
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));
}
This is fine for cases where your criteria are known at design-time, or are predictably small – e.g. limiting to a set of statuses. For scenarios where the criteria are large or unknown, the better option is to use a LINQ subquery. The syntax seems backwards but is straightforward when you get used to it.
This query looks for any customers who have placed an order in a given date range, and then returns all their orders – irrespective of the order date:
var query = from o in context.Orders
let recent = context.Orders.Where(o => o.OrderDate > selectionDate)
where recent.Any(x => x.CustomerId == o.CustomerId)
select o;
The let defines the subquery to gets a list of orders restricted by date, and the Any() matches the outer query to the subquery on customer ID. This will produce a single SQL statement which will be as efficient as your schema allows, but it will cope with any quantity of restriction criteria.