EF: How to pass column name dynamically to where clause

Question

i am not before dev pc. i just started working with EF. so curious to know can we pass column name dynamically for where clause.

see a screen shot for searching grid.

i just compose a sample query. please tell me does it work?

public ActionResult Index(String ColumnName,String SearchText)
{

    private CustomersEntities db = new CustomersEntities();

    var customer = (from s in db.Customers
                    select new CustomerDTO
                    {
                    CustomerID = s.CustomerID,
                    CompanyName = s.CompanyName,
                    ContactName = s.ContactName,
                    ContactTitle = s.ContactTitle,
                    Address = s.Address
                    })
    .Where(s => s.Field<string>(ColumnName).ToUpper().Contains(SearchText.ToUpper());

    return View(customer);

}

thanks

ok, what is the problem with your code? doesn't compile? doesn't work as expected? don't you like? — Roman Marusyk, Feb 09 '18 at 13:06

score 2 · Answer 1 · answered Feb 09 '18 at 12:52

2

You can create something like this in repository (if you use it)

public IQueryable<T> FindBy(Expression<Func<T, bool>> predicate)
{
    return _context.Set<CustomersEntities>().Where(predicate);
}

and then

var result = _repository.FindBy(y => y.CompanyName.IndexOf(SearchText, StringComparison.OrdinalIgnoreCase) >= 0);

answered Feb 09 '18 at 12:52

Roman Marusyk

23,328
24
73
116

thanks for reply but here you use column name `y => y.CompanyName.IndexOf` but i want to pass customer name as string. – Monojit Sarkar Feb 09 '18 at 12:58
Why do you want to pass customer name as string? It will be more safest to use really field with known type and intellisense – Roman Marusyk Feb 09 '18 at 13:00
i have update my post code. please have a look for better understand. – Monojit Sarkar Feb 09 '18 at 13:02

score 2 · Accepted Answer · answered Feb 12 '18 at 05:50

public ActionResult Index(string ColumnName, string SearchText)
{
    var arg = Expression.Parameter(typeof(Customer), "x");

    var strType = typeof(string);
    var ToUpperMeth = strType.GetMethods().Where(x => x.Name == nameof(string.ToUpper) 
                                          && x.GetParameters().Count() == 0).Single();
    var ContainsMeth = strType.GetMethods().Where(x => x.Name == nameof(string.Contains) 
                                          && x.GetParameters().Count() == 1).Single();

    var exprVal = Expression.Constant(SearchText);
    var toUpExprVal = Expression.Call(exprVal, ToUpperMeth);

    var exprProp = Expression.Property(arg, ColumnName);
    var toUpExpr = Expression.Call(exprProp, ToUpperMeth);
    var contExpr = Expression.Call(toUpExpr, ContainsMeth, toUpExprVal);

    var predicate = Expression.Lambda<Func<Customer, bool>>(contExpr, arg);

    var customer = (from s in db.Customers
                    select new CustomerDTO
                    {
                        CustomerID = s.CustomerID,
                        CompanyName = s.CompanyName,
                        ContactName = s.ContactName,
                        ContactTitle = s.ContactTitle,
                        Address = s.Address
                    }).Where(predicate).ToList();

    return View(customer);
}

thanks for answer. i have one request that can u please see this post and tell me why some one use AsEnumerable() when working with EF. see the code they use AsEnumerable() before select but why? i many time use EF select but never use AsEnumerable() before it. so tell me the reason for using there AsEnumerable(). https://stackoverflow.com/q/48723564/6188148 — Monojit Sarkar, Feb 12 '18 at 08:44
@Slava Thank you so so much! I don't even completely understand how this works but it works beautifully. Couln't ever come to a solution my self. I would pay you a coffe sir. Just send me a Binance ETH wallet adress. — Ricardo Araújo, Mar 25 '22 at 18:54

score -1 · Answer 3 · answered Feb 09 '18 at 14:36

The basic pattern is to build up the query at runtime, selectively adding Where expressions before running the query and projecting the results into the DTO.

Like this:

        public IList<CustomerDTO> FindCustomers(String ColumnName, String SearchText)
        {

            var query = from s in db.Customers select s;

            if (ColumnName == "CompanyName")
            {
                query = query.Where(c => c.CompanyName == SearchText);
            }
            else if (ColumnName == "ContactName")
            {
                query = query.Where(c => c.ContactName == SearchText);
            }
            //. . .
            else
            {
                throw new InvalidOperationException($"Column {ColumnName} not found or not supported for searching.");
            }

            var results = from c in query
                        select new CustomerDTO()
                        {
                            CustomerID = c.CustomerID,
                            CompanyName = c.CompanyName,
                            ContactName = c.ContactName,
                            ContactTitle = c.ContactTitle,
                            Address = c.Address
                        };

            return results;

        }

You can do this dynamically with reflection and expressions, but what's the point? Your Entities don't just change at runtime. — David Browne - Microsoft, Feb 09 '18 at 14:43
see my picture posted here. if my dropdown has 100 fields then i have to too many if..else. that may looks odd — Monojit Sarkar, Feb 09 '18 at 15:16

EF: How to pass column name dynamically to where clause

3 Answers3

Linked