LINQ query to test the predicate in all columns of a table

Question

I have a table with 30 columns and it contains 1000 rows. I need one LINQ query that checks for a specific value in all columns and converts the result to a list.

For instance:

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

How to accomplish the above using a single LINQ query. Any help is greatly appreciated.

Answer 1

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

The Queriable.Where method receives the predicate Expression<Func<T, bool>> as a parameter. Therefore, you need to build the predicate o.p1 == val || o.p2 == val || o.p3 = val ... o.p1 == val || o.p2 == val || o.p3 = val ... o.p1 == val || o.p2 == val || o.p3 = val ... like Expression . Here o is the parameter 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 to construct the predicate dynamically for all TObj properties of type TVal .

To simplify the code, we construct an equal expression false || o.p1 == val || ... || o.pN == val false || o.p1 == val || ... || o.pN == val 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); } 

Answer 2

I got an answer But not a perfect answer But it works well

  public class GenericList<T> { void Add(T input) { } public List<T> SerachFun(List<T> input, string search) { List<T> output = new System.Collections.Generic.List<T>(); foreach (var aa in input) { var columns = aa.GetType().GetProperties().ToList(); foreach (var bb in columns) { var cccc = bb.GetValue(aa); bool result = cccc.ToString().Contains(search); if (result) { output.Add(aa); continue; } } } return output; } } 

Generic Class Object Created

  public GenericList<table1> g = new GenericList<table1>(); 

A generic class method that is called:

  var tabledetails=db.table1.ToList(); var resultcommonsearch = g.SerachFun(tabledetails, "Dhoni"); 

Answer 3

using code

 public class GenericList<T> { public List<T> SerachFun(List<T> input, string search) { List<T> output = new System.Collections.Generic.List<T>(); foreach (var aa in input) { var columns = aa.GetType().GetProperties().ToList(); foreach (var bb in columns) { var cccc = bb.GetValue(aa); if(cccc!=null) { bool result = cccc.ToString().Contains(search); if (result) { output.Add(aa); continue; } } } } return output; } } 

Try the call method

  public GenericList<table1> g = new GenericList<table1>(); var tabledetails=db.table1.ToList(); var resultcommonsearch = g.SerachFun(tabledetails, "Dhoni");