Workaround to take invalid LINQ methods out of the query in generic method

Question

I'm trying to do the following generic method to search entities in a database:

    // 'atributo' is the name of the attribute to search against
    public List<TEntity> buscar<TEntity>(string valor, string atributo) where TEntity : class
    {
        var matches = from m in db.Set<TEntity>()
                      where m.GetType().GetProperty(atributo).GetValue(m, null).ToString().Contains(valor)
                      select m;

        return matches.ToList();
    }

Of course I'm getting the exception:

LINQ to Entities does not recognize the method 'System.String ToString()' method

And I know that GetType(), GetProperty() and GetValue() are invalid LINQ methods too.

I can't figure out how to use the invalid methods before the query.

Any ideas?

TIA

This is a horrible idea. Without your "generic" method L2E could (on the right schema) do an index lookup. With the generic method, you will have to pull the entire table to your method, just to cast *every single value to a string and do a substring match*. This table better have ~10 entries... — Chris Pitman, Jan 30 '13 at 07:49
@ChrisPitman Thanks for the idea but some tables could have thousands of entries. — , Jan 30 '13 at 07:54
@Chris Even in the case of such generic methods you can have an database lookup (which might use an index if an appropriate index is defined on the DB), if the expression tree is structured in a way that can be recognized by L2E. — Jean Hominal, Jan 30 '13 at 08:31
@JeanHominal I understand, I've written many such query generators. However, this method casts everything to strings, which is already never going to match an index. Even if a column was added that had the string form of the searched values, this is using a substring match, requiring a full text index. It's overly complicated for negative value. — Chris Pitman, Jan 30 '13 at 16:13

Jean Hominal · Accepted Answer · 2013-01-30T08:49:00.227

You could write the expression tree yourself (dynamic LINQ is overkill for your need):

using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;
using System.Linq.Expressions;

class YourClass
{
    public static List<TEntity> DoSomething<TEntity>(DbContext db, string valor, string atributo)
        where TEntity : class
    {
        var mParam = Expression.Parameter(typeof(TEntity));

        var matches = db.Set<TEntity>()
                        .Where(Expression.Lambda<Func<TEntity, bool>>(
                            Expression.Call(
                                Expression.Convert(
                                    Expression.Property(mParam, atributo),
                                    typeof(String)
                                    ),
                                "Contains",
                                new Type[0],
                                Expression.Constant(valor)
                                ),
                            mParam
                         ));

        return matches.ToList();
    }
}

Notes:

That will not work if the property is not defined on TEntity (even if TEntity is abstract and all subclasses do define the property);
If the return type of the property is known to be String, you can avoid the Expression.Convert call;
That will not work if Entity Framework can not convert the value of the property to a string (e.g. if the property returns an entity type);

Thanks. I feel that you are very close. Got the following exception: **Incorrect number of parameters supplied for lambda declaration**. I'm trying to make it work. — , Jan 30 '13 at 08:44
@user1537004: Whoops. I forgot to use the `mParam` object for the lambda function definition. — Jean Hominal, Jan 30 '13 at 08:50
Thanks Jean. It's working perfectly now! Thanks for taking your time. :) — , Jan 30 '13 at 08:56

score 0 · Answer 2 · edited May 23 '17 at 12:18

0

linq to entities translate the linq to an expression tree and then tries to translate that expression tree to SQL. It does not evaluate the expressions and it therefor can't translate reflective code to SQL. What you could do is use dynamic linq your code would then be something similar to

public List<TEntity> buscar<TEntity>(string valor, string atributo) where TEntity : class
{
    return db.Set<TEntity>()
           .Where(atributo + =".Contains(\"" + valor + "\")")
           .ToList()
}

edited May 23 '17 at 12:18

Community

1
1

answered Jan 30 '13 at 07:52

Rune FS

21,497
7
62
96

Thanks. Have some errors now but I'll try to fix them. Looks promising. :) – Jan 30 '13 at 08:08

Reza ArabQaeni · Answer 3 · 2013-01-30T08:56:48.367

-1

Update:

SqlFunctions.StringConvert(m.GetType().GetProperty(atributo).GetValue(m, null))

edited Jan 30 '13 at 08:56

answered Jan 30 '13 at 07:23

Reza ArabQaeni

4,848
27
46

Thanks for your answer but isn't working. Got the same exception. – Jan 30 '13 at 07:26
The exception is slightly different. Now it says: **LINQ to Entities does not recognize the method 'System.String ToString(System.Object)' method** – Jan 30 '13 at 07:30
-1: There is no overload of [`SqlFunctions.StringConvert`](http://msdn.microsoft.com/en-us/library/system.data.objects.sqlclient.sqlfunctions.stringconvert.aspx) that takes an `object` parameter, so that code will not compile. – Jean Hominal Jan 30 '13 at 09:05

Workaround to take invalid LINQ methods out of the query in generic method

3 Answers3