Geeks With Blogs

News This is the *old* blog. The new one is at blog.sixeyed.com
Elton Stoneman
This is the *old* blog. The new one is at blog.sixeyed.com

[Source: http://geekswithblogs.net/EltonStoneman]

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.

Posted on Sunday, July 11, 2010 6:07 PM EF , LINQ | Back to top


Comments on this post: WHERE IN()-style Queries in LINQ to Entities

# re: WHERE IN()-style Queries in LINQ to Entities
Requesting Gravatar...
LINQ to Entities does not have a Contains operator in .Net 3.5, however, in .Net 4.0 it does.
Left by Josh on Jul 12, 2010 7:11 AM

# re: WHERE IN()-style Queries in LINQ to Entities
Requesting Gravatar...
Thanks for that Josh - you're right that Contains() is available in 4.0. Full list of supported and unsupported LINQ methods is here: http://msdn.microsoft.com/en-us/library/bb738550.aspx
Left by Elton on Jul 12, 2010 2:27 PM

# re: WHERE IN()-style Queries in LINQ to Entities
Requesting Gravatar...
Nice and impressive post title is very attractive and the content are unique also. Thanks a lot for giving this information.
Left by .net hosting on Aug 06, 2010 7:10 AM

# re: WHERE IN()-style Queries in LINQ to Entities
Requesting Gravatar...
This post is the one of the post that i have came across.Really you have covered a lot of things through your article.Thanks
Left by Videos of Dentistry on Aug 07, 2010 4:44 AM

# re: WHERE IN()-style Queries in LINQ to Entities
Requesting Gravatar...
Thanks to all employees.
Left by desenhane on Sep 13, 2010 3:54 PM

# re: WHERE IN()-style Queries in LINQ to Entities
Requesting Gravatar...
Hey I just got through with the pages, I should say Pretty nice work done there.I really appreciate it.Thanks a lot for sharing.Keep up the good works.
Left by trigger450 on Oct 04, 2010 4:32 AM

# re: WHERE IN()-style Queries in LINQ to Entities
Requesting Gravatar...
This article was exceptional helpful! Thank you so much for sharing!
Left by ACanadian on Sep 24, 2011 12:24 AM

# re: WHERE IN()-style Queries in LINQ to Entities
Requesting Gravatar...
This article is awesome thx!!
Left by Hans Huber on May 29, 2012 11:38 AM

Your comment:
 (will show your gravatar)


Copyright © Elton Stoneman | Powered by: GeeksWithBlogs.net