Yitzhak or50 Yitzhak or50 - 1 month ago 5
C# Question

select columns where one from columns contains value is it possible ? in linq

What is the best way to query where one of the columns contains a value?

This is my code:

public JsonResult FindCustomer(string value)
{
var result= new SelectList(db.Customers.Where(t => (t.PersonalMail.Contains(value) ||
t.BusinessMail.Contains(value)
|| t.ContactName.Contains(value) ||
t.BusinessName.Contains(value) || t.Phone.Contains(value) ||
t.DirectPhone.Contains(value)), "ID", "PersonalMail");
return Json(jj);
}


Maybe it is possible to do something like this:

var result= new SelectList(db.Customers.Where(t => (Each column).Contains(value)), "ID", "PersonalMail");

Answer
public JsonResult FindCustomer(string value)
{
    var Customers = db.Customers.ToList();
    var result = new SelectList(db.Customers.Where(t =>
        t?.GetType().GetProperties().Any(x => //for all columns of a row where...
            (x.PropertyType==typeof(string)) && // it does have the same type
            x.GetValue(t, null) != null && // the value should not be null
             x.GetValue(t, null).ToString().Contains(value)) // the value should be equal
        ?? false), // if t is null, than skip it
        "ID", "PersonalMail");

    return Json(jj);
}

Would do the trick. If you do want to exclude some columns, add them to the ANY clause:

.Any(x =>  x.GetValue(t, null) != null && !x.Name.Equals("BusinessMail") && (x.PropertyType==typeof(string)) && x.GetValue(t, null).ToString().Contains(value))

NOTE:

This will not work when applying on the Entity Framework! As @Zev Spitz has already mentioned:

What you are passing into Where is not compiled into runnable code, but rather into an expression tree. See here.

I was not sure what the OP actually uses, so this could have been an answer. For future readers - an example use case of this would be having a list of Foo instances (containing string properties) and you want to get only those with one value being "Cat".


For selecting the value of the first property containing the value, add the following after "PersonalMail"):

.Select(t=>t.GetType().GetProperties().First(x=> // get the first porperty
    x.GetValue(t, null).Equals(value) // where the value equals the searched one
 ).GetValue(t, null)); // And select the value of that property.

If you do want to get the property instead of the value, than remove .GetValue(t, null). You could also use Where instead of Any and will return a list of all properties containing that value. (Note, that it will return a collection filled with PropertyInfo!)