How to implement a LIKE operation in a search form in C#?

Question

Hello,

I'm using the code below for my search logic, basically, it evaluates a field when there's an input on the corresponding textbox or dropdown, my problem is that the code is only for exact matches, what's the best way to implement also a .Contains() search, or a search which implement an SQL LIKE search?

private void btnSearch_Click(object sender, EventArgs e)
    {            
        bool ok_username = !txtUsername.IsBlank();
        bool ok_firstname = !txtFirstname.IsBlank();
        bool ok_lastname = !txtLastName.IsBlank();
        bool ok_userlevels = cboUserLevels.IsItemInList();

        _query = from _v
                 in Classes.Data.getdb().vUsers
                 where
                   _v.username ==(ok_username ? txtUsername.Text : _v.username) &&
                   _v.firstname == (ok_firstname ? txtFirstname.Text : _v.firstname) &&
                   _v.lastname == (ok_lastname ? txtLastName.Text : _v.lastname) &&
                   _v.userlevel == (ok_userlevels ? cboUserLevels.Text : _v.userlevel)
                 select _v;
        gv.DataSource = _query ;


    }

When i try to enclose the eval expression in a .Contains() function, it says that only expressions which can be evaluated on the client can be used as an argument in a .Contains function. Thanks! Appreciate any advise!

---

Ok,

Thanks to Alex for giving me an idea about the SQLMethods function. But for now i'll go with Sander's suggestion of chaining expression trees. If anyone can make this code shorter i'll really appreciate it, because in this solution i have two sets of queries one is for the flexible, another is for the exact search. Thanks again to Alex and Sander!

private void btnSearch_Click(object sender, EventArgs e)
    {            

        bool ok_username = !txtUsername.IsBlank();
        bool ok_firstname = !txtFirstname.IsBlank();
        bool ok_lastname = !txtLastName.IsBlank();
        bool ok_userlevels = cboUserLevels.IsItemInList();

        if (optMode.CheckedIndex == 0) //flexible search, the else part is the exact search 
        {
            _query = (from _v
                      in Classes.Data.getdb().vUsers
                      where
                      _v.userlevel == (ok_userlevels ? cboUserLevels.Text : _v.userlevel)
                      select _v);

            if (ok_username)
                _query = _query.Where(x => x.username.Contains(txtUsername.Text));

            if (ok_firstname)
                _query = _query.Where(x => x.firstname.Contains(txtFirstname.Text));

            if (ok_lastname)
                _query = _query.Where(x => x.lastname.Contains(txtLastName.Text));
        }
        else
        {
            _query = (from _v
                      in Classes.Data.getdb().vUsers
                      where
                       _v.username == (ok_username ? txtUsername.Text : _v.username) &&
                       _v.firstname == (ok_firstname ? txtFirstname.Text : _v.firstname) &&
                       _v.lastname == (ok_lastname ? txtLastName.Text : _v.lastname) &&
                       _v.userlevel == (ok_userlevels ? cboUserLevels.Text : _v.userlevel)
                      select _v);
        }

        gv.DataSource = _query.ToList();



    }

Answer 1

You can make the query more specific step by step like this:

var _query = from _v in Classes.Data.getdb().vUsers
             select v;

if(!txtUsername.IsBlank())
    _query = _query.Where(x => x.username.Contains(txtUsername.Text));

if(!txtFirstName.IsBlank())
    _query = _query.Where(x => x.firstname.Contains(txtFirstName.Text));

// etc.

gv.DataSource = _query;

Answer 2

You have to call Contains from the String inside the database:

_query = 
   from _v in Classes.Data.getdb().vUsers
   where
     (txtUsername.IsBlank() || _v.username.Contains(txtUsername.Text)) &&
     (txtFirstname.IsBlank() || _v.firstname.Contains(txtFirstname.Text)) &&
     (txtLastName.IsBlank() || _v.lastname.Contains(txtLastName.Text)) &&
     (!cboUserLevels.IsItemInList() || _v.userlevel.Contains(cboUserLevels.Text))
   select _v;

If you want to use the SQL Like operator, you can use System.Linq.Data.SqlClient.SqlMethods:

_query = 
   from _v in Classes.Data.getdb().vUsers
   where
     SqlMethods.Like(_v.username, "%" + txtUsername.Text + "%") &&
     SqlMethods.Like(_v.firstname, "%" + txtFirstname.Text + "%") &&
     SqlMethods.Like(_v.lastname, "%" + txtLastName.Text + "%") &&
     SqlMethods.Like(_v.userlevel, "%" + cboUserLevels.Text + "%")
   select _v;