none
EF Dynamic column name in where clause RRS feed

  • 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.

    enter image description here

    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


    • Edited by Sudip_inn Friday, February 9, 2018 1:14 PM
    Friday, February 9, 2018 12:37 PM

Answers

  • Hi Sudip_inn,

    We could achieve it via Lambda Expression Tree. and following method for your reference.

    public ActionResult Index(String ColumnName,String SearchText)
    {
    
        private CustomersEntities db = new CustomersEntities();
    
        ParameterExpression param = Expression.Parameter(typeof(Customer), "t");
       MemberExpression member = Expression.Property(param, columnName);
        ConstantExpression constant = Expression.Constant(searchText);
        MethodInfo containsMethod = typeof(string).GetMethod("Contains");
        Expression exp = Expression.Call(member, containsMethod, constant); 
        var deleg = Expression.Lambda<Func<Customer, bool>>(exp, param).Compile();
       var customer = db.Customers.Where(deleg).Select(s => new CustomerDTO {
            CustomerID = s.CustomerID,
                        CompanyName = s.CompanyName,
                        ContactName = s.ContactName,
                        ContactTitle = s.ContactTitle,
                        Address = s.Address
                    });
            return View(customer);
    }

    You could use the following code to achieve it.

    public ActionResult Index(String ColumnName,String SearchText)
    {
    
        private CustomersEntities db = new CustomersEntities();
    IQueryable<Customer> custom = null;
                    switch (ColumnName)
                    {
                        case "CompanyName":
                            custom = db.Customers.Where(t => t.CompanyName.Contains(SearchText));
                            break;
                        case "ContactName":
                            custom = db.Customers.Where(t => t.ContactName.Contains(SearchText));
                            break;
                        case "ContactTitle":
                            custom = db.Customers.Where(t => t.ContactTitle.Contains(SearchText));
                            break;
                            //other field
                    }
              var customer= custom.Select(s => new CustomerDTO
                        {
                        CustomerID = s.CustomerID,
                        CompanyName = s.CompanyName,
                        ContactName = s.ContactName,
                        ContactTitle = s.ContactTitle,
                        Address = s.Address });
    
        
        return View(customer);
    
    }

    Best regards,

    Zhanglong


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Sudip_inn Monday, February 12, 2018 9:11 AM
    Monday, February 12, 2018 5:26 AM
    Moderator