Linq - How to combine reflection with where and contains

Question

I want to create a dynamic method for selecting data by using contains in a where clause. I found a lot of information about expressions, but I can't find a combination that works.

I posted my method and I'm stuck at this line by searching a solution with refelection or something like that:

.Where(p => p.Description != null && p.Description.ToLower().Contains(lowerFilter));

What is best practice by solving the problem?

Selection Method:

public List<TEntity> GetItems(string sortBy, string sort, string? filter)
{
    IQueryable<TEntity> items = dbContext.Set<TEntity>();

    if (filter != null && filter.Length > 0)
    {
        string lowerFilter = filter.ToLower(CultureInfo.CurrentCulture);

        items = items
            .Where(p => p.Description != null && p.Description.ToLower().Contains(lowerFilter));
    }

    if (!string.IsNullOrEmpty(sortBy))
    {
        if (!string.IsNullOrEmpty(sort))
        {
            items = items.AsQueryable().OrderBy($"{sortBy} {sort}");
        }
    }

    List<TEntity> itemsList = items.ToList();

    return itemsList;
}

I am sorry but kind of confused here. Your method GetItems is generic method. Are you sure that every TEntity has Description column? Or that is what you are trying address here? — sam, Jan 09 '20 at 15:43
I'm looking for a replacement for the line, because it doesn't work that way because the method is generic. The name of the column can then be specified dynamically as a string. — Henning, Jan 09 '20 at 16:03
I just want to share my thought here. Even though you figure out a way to achieve this via reflection or via EF conventions or some other method, I think, your GetItems method will become messy because, either your code will have multiple if-else statements or where clause becomes heavier. Not sure your use case here but re-visit it once before pursuing in this direction. — sam, Jan 09 '20 at 16:39

score 0 · Answer 1 · answered Jan 09 '20 at 15:09

0

Your linq query should work just fine.

.Where(p => p.Description != null && p.Description.ToLower().Contains(lowerFilter));

In terms of best practice, the only thing I can see for now is the unnecessary nested if statement. Which you can simplify as:

 if (!string.IsNullOrEmpty(sortBy) && !string.IsNullOrEmpty(sort))
    {
        items = items.AsQueryable().OrderBy($"{sortBy} {sort}");
    }

answered Jan 09 '20 at 15:09

Ndubuisi Jr

461
6
13

1

Unfortunately not because I work with a type and Description is not known. I am looking for a solution to replace this line with Reflection or something like that. – Henning Jan 09 '20 at 15:13

score 0 · Accepted Answer · answered Jan 10 '20 at 06:30

0

You can do this with using dynamic Expression Builder:

This generic extension method helps you implement Contains for all IQueryable:

public static IQueryable<T> ContainsByField<T>(this IQueryable<T> q, string field, string value)
{
    var eParam = Expression.Parameter(typeof(T), "e");
    var method = field.GetType().GetMethod("Contains");
    var call = Expression.Call(Expression.Property(eParam, field), method, Expression.Constant(value.ToLower()));
    var lambdaExpression = Expression.Lambda<Func<T, bool>>(
        Expression.AndAlso(
            Expression.NotEqual(Expression.Property(eParam, field), Expression.Constant(null)),
            call
        ),
        eParam
    );

    return q.Where(lambdaExpression);
}

Then you need to call as simple like this:

items = items.ContainsByField("Description", lowerFilter);

answered Jan 10 '20 at 06:30

Selim Yildiz

5,254
6
18
28

That's exactly what I was looking for. I just had to change the line with the `GetMethod` to `typeof(string).GetMethod("Contains", new[] { typeof(string) })` because I got the error "Ambiguous match found" there. I only have a strange behaviour during my tests. It works correctly with the integrationtests with the real MSSQL-DB. When unit testing against an InMemoryDatabase the value "Test1" is not found. – Henning Jan 10 '20 at 07:50
Please accept answer as a accepted if it helped to you. I tested this code it works just for this purpose, i think you had other problem which is not relevant to this problem. Because my code does not have`Test1` etc. It's hard to understand problem from what you said. – Selim Yildiz Jan 10 '20 at 08:09
I have found the reason for the different behaviour. In the expression of you, the `.ToLower()` is missing in `.Description.ToLower().Contains(...)`. So it only works with a case insensitive database. Maybe you can adapt your code? I haven't been able to get it to work. – Henning Jan 13 '20 at 06:16

Linq - How to combine reflection with where and contains

2 Answers2

Linked