CODEWITHSUNDEEP
c#linqentity-frameworkentity-framework-4
PCasagrande
PCasagrande

Reputation: 5402

Linq to Entity Where built by looping over data

I have a search box and I have broken the data into words. I now need compare each of these words with several fields in the database. Here is the code I have:

List<string> searchValues = searchString.Split(delimiters, StringSplitOptions.RemoveEmptyEntries).ToList();

using (DatabaseEntities context = new DatabaseEntities())
{
    IQueryable<DatabaseType> returnValue =
    context.DatabaseType.Where(y =>
        y.Field1.Contains(searchValues[0]) ||
        y.Field1.Contains(searchValues[0]));

    searchValues.Skip(1).ToList().ForEach(x =>
    {
        returnValue = returnValue.Where(y =>
            y.Field1.Contains(x) ||
            y.Field2.Contains(x));
    });

    return returnValue.ToList();

My goal is to build the full query before actually loading the items into memory. However, when I try to run this I get the following error:

System.NotSupportedException: LINQ to Entities does not recognize the method &#39;System.String get_Item(Int32)&#39; method, and this method cannot be translated into a store expression.

I had thought this type of error meant that the operations I am attempting do not work in the conversion to SQL, but I'm fairly sure I have used Contains as LIKE statements before. Also, all of the information in the database are nvarchars, so those equate to strings in my Entity. Does any one have any idea what the problem is? Is using an IQueryable the best way to do this? I would prefer to lazy load all of these, if possible.

Upvotes: 0

Views: 185

Answers (2)

Plymouth223
Plymouth223

Reputation: 1925

The get_Item(Int32) calls will be the indexing into the list. In your case they're searchValues[0]. Use:

var searchValue = searchValues[0];

IQueryable<DatabaseType> returnValue =
context.DatabaseType.Where(y =>
    y.Field1.Contains(searchValue) ||
    y.Field1.Contains(searchValue));

Upvotes: 2

Eric J.
Eric J.

Reputation: 150108

I use the extension method below to build Contains type expressions for Linq to Entities (reference to the original source is in the comments). I think it will work in this situation as well.

    /// <summary>
    /// Extension method that enables .Contains(obj) like functionality for Linq to Entities.
    /// 
    /// Source: http://www.velocityreviews.com/forums/t645784-linq-where-clause.html
    /// </summary>
    /// <typeparam name="TElement">The element being evaluated by the Where clause</typeparam>
    /// <typeparam name="TValue">The value to match</typeparam>
    /// <param name="valueSelector">Lamda for selecting matching values</param>
    /// <param name="values">IEnumerable of the values</param>
    /// <returns>Expression consumable by Linq to Entities that reflects semantics of .Contains(value)</returns>
    /// <remarks>
    /// Usage:
    /// 
    /// Replace expression like 
    /// 
    /// where ChildrenIDs.Contains(items.CategoryID)
    /// 
    /// with
    /// 
    /// .Where((BuildContainsExpression<Item, int>(item => item.CategoryID, ChildrenIDs))
    /// 
    /// NOTE: If the item collection is large, the SQL query will be as well.
    /// </remarks>
    static public 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();
        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);
    }

Example Usage:

var exprWithContains = 
    LinqToEntitiesUtil.BuildContainsExpression<List<int?>, int?>
        (p => p.ProductYear, productYears);

var result = (from p in products select p).Where(exprWithContains);

Upvotes: 0

Related Questions