LINQ Query to check for a predicate in all columns in a table

Question

I have a table with 30 columns, and it contains 1000 rows. I want a single LINQ query, which checks for a particular value in all columns and converts the result into a list.

For example:

table.where(allcolumnvalue.contains(searchvalue)).Tolist()

How to accomplish the above using one LINQ query. Any help is much appreciated.

Answer 1

For your request all of fields should have same type, at least in the static typed C#.

The method Queriable.Where gets the Expression<Func<T, bool>> predicate as parameter. So you need build the predicate o.p1 == val || o.p2 == val || o.p3 = val ... as Expression value. Here o is a parameter of Expression<Func<T, bool>>:

public Expression BuildExpression<TObj, TVal>(TObj obj, TVal val)
{
    Expression<Func<TObj, bool>> predicate = (o) => o.p1 == val || ... || o.pN == val;
    return predicate;
}

but we need build predicate dynamically for all properties of TObj that have type TVal.

To simplify the code we will build equal expression false || o.p1 == val || ... || o.pN == val.

public Expression<Func<TObj, bool>> BuildExpression<TObj, TVal>(TVal val)
{
    var parameter = Expression.Parameter(typeof(TObj), "o");
    var valExpression = Expression.Constant(val, typeof(TVal));
    var body = Expression.Constant(false, typeof(bool));

    var properties = typeof(TObj).GetProperties()
                                 .Where(p => p.PropertyType == typeof(TVal));
    foreach (var property in properties)
    {
        var propertyExpression = Expression.Property(parameter, property);
        var equalExpression = Expression.Equal(propertyExpression, valExpression);
        body = Expression.Or(body, equalExpression);
    }

    return Expression.Lambda<Func<TObj, bool>>(body, parameter);
}

. . .

using (var dbContext = new DbContext())
{
    var whereExpression = BuildExpression<User, string>("foo");
    var contaningsFoo = dbContext.Users.Where(whereExpression);
}