mardi 28 avril 2015

How to Perform Contains Query On All Properties of an Entity Framework Model

I am trying to write something that will do a "Contains" query on all properties of an entity framework model.

I am able to do the following for example with no issues:

var students = db.Students.AsQueryable();
var test = students.Where(x => x.ID.ToString().ToLower().Contains("1"));

I found a code sample online that does what I want, but it's old and I am getting an error that I can't seem to resolve.

LINQ to Entities does not recognize the method 'System.String ToString()' method, and this method cannot be translated into a store expression.

I have read up on this error, but as you see above ToString is perfectly valid even when using an IQueryable (which is required in my case because I don't want to post filter the data).

I have the following method that runs without errors but then when you convert ToList() it fails:

 private Expression<Func<T, bool>> getSearchExpression()
        {
            string search = _request.Search.Value;

            var filterableColumnIds = new HashSet<string>(_request.Columns.Where(x => x.Searchable).Select(x => x.Data));
            var filterProperties = _properties.Where(x => filterableColumnIds.Contains(x.Name)).ToList();

            //  When no filterProperties or search term(s), return a true expression
            if (string.IsNullOrEmpty(search) || !filterProperties.Any())
            {
                return x => true;
            }

            var paramExpression = Expression.Parameter(typeof(T), "val");
            Expression compoundOrExpression = Expression.Call(Expression.Property(paramExpression, filterProperties[0]),"ToString", null);
            Expression compoundAndExpression = Expression.Call(Expression.Property(paramExpression, filterProperties[0]), "ToString", null);
            MethodInfo convertToString = typeof(Convert).GetMethod("ToString", Type.EmptyTypes);

            //  Split search expression to handle multiple words
            var searchTerms = search.Split(' ');

            for (int i = 0; i < searchTerms.Length; i++)
            {
                var searchExpression = Expression.Constant(searchTerms[i].ToLower());

                //  For each property, create a contains expression
                //  column => column.ToLower().Contains(searchTerm)     
                var propertyQuery = (from property in filterProperties
                                     let toStringMethod = Expression.Call(
                                                         Expression.Call(Expression.Property(paramExpression, property), convertToString, null),
                                                             typeof(string).GetMethod("ToLower", new Type[0]))
                                     select Expression.Call(toStringMethod, typeof(string).GetMethod("Contains"), searchExpression)).ToArray();

                for (int j = 0; j < propertyQuery.Length; j++)
                {
                    //  Nothing to "or" to yet
                    if (j == 0)
                    {
                        compoundOrExpression = propertyQuery[0];
                    }

                    compoundOrExpression = Expression.Or(compoundOrExpression, propertyQuery[j]);
                }

                //  First time around there is no And, only first set of or's
                if (i == 0)
                {
                    compoundAndExpression = compoundOrExpression;
                }
                else
                {
                    compoundAndExpression = Expression.And(compoundAndExpression, compoundOrExpression);
                }
            }

            //  Create expression
            return Expression.Lambda<Func<T, bool>>(compoundAndExpression, paramExpression);
        }

So to clarify, I basically want the user to be able to type a single string in a textbox, and then show any rows that have a field that contains that string. My handler is generic so it needs to use reflection to get to the properties.





Aucun commentaire:

Enregistrer un commentaire