Dynamic Linq To Sql With ComboBox and Column.Contains

Question

I have a text box, combo box, button and DataGridView on a form that is used to search and return customer information from a MSSQL view (vCustomer). It works great, but I know my code can be more efficient. The four items in the combobox represent columns to search.

Is there a simple way of converting the following to dynamic LINQ to SQL? I am new to C#. I checked out some other posts, but I cannot seem to get it working.

public partial class MainForm : Form
{
 public MainForm()
 {
  InitializeComponent();
 }

 private void MainForm_Load(object sender, EventArgs e)
 {
  // columns to filter for
  string[] list = new string[4];
  list[0] = "Name";
  list[1] = "CustomerAccountNo";
  list[2] = "Telephone";
  list[3] = "Postal";

  // bind to combobox
  cboColumn.DataSource = list;
  cboColumn.SelectedIndex = 0;
 }

 private void btnSearch_Click(object sender, EventArgs e)
 {

  try
  {
   Cursor.Current = Cursors.WaitCursor; 
   CustomerSearchDataContext db = new CustomerSearchDataContext();
   IEnumerable<vCustomer> customerQuery = null;
   switch (cboColumn.SelectedIndex)
   {
    case 0:
     customerQuery = from c in db.vCustomers
         where c.Name.Contains(txtSearch.Text)
         orderby c.CustomerAccountNo descending
         select c;
     break;
    case 1:
     customerQuery = from c in db.vCustomers
         where c.Name.Contains(txtSearch.Text)
         orderby c.CustomerAccountNo descending
         select c;
     break;
    case 2:
     customerQuery = from c in db.vCustomers
         where c.Telephone.Contains(txtSearch.Text)
         orderby c.CustomerAccountNo descending
         select c;
     break;
    case 3:
     customerQuery = from c in db.vCustomers
         where c.Postal.Contains(txtSearch.Text)
         orderby c.CustomerAccountNo descending
         select c;
     break;
   }
   customerBindingSource.DataSource = customerQuery;
   dataGridView1.DataSource = customerBindingSource;
   dataGridView1.Columns["CustomerId"].Visible = false;
  }
  catch (System.Data.SqlClient.SqlException ex)
  {
   MessageBox.Show("An Error Occured - " + ex.Message,"Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
  }
  finally
  {
   Cursor.Current = Cursors.Default; 
  }
 }
}

Answer 1

Use [System.Linq.Dynamic][1].

Get the condition from a method and use it in a single query.

    switch (choice)
    {
        case case1:
            condition = string.Format("{0}.Contains({1})", "Column", "Value"
            break;

Answer 2

Hey Rony. I tried your suggestion and re factored my code (see below). However, I receive an error: No property or field 'smith' exists in type 'vCustomer' . By the way, the MessageBox.Show(condition); line returns Name.Contains(smith) which looks correct.

What am I doing wrong? Sorry for being a noob and thanks for your help.

Figured it out... needed to wrap search string with double quotes! Code has been edited.

public partial class MainForm : Form
{
 public MainForm()
 {
  InitializeComponent();
 }
 private void MainForm_Load(object sender, EventArgs e)
 {
  // data column to filter against
  string[] list = new string[4];
  list[0] = "Name";
  list[1] = "CustomerAccountNo";
  list[2] = "Telephone";
  list[3] = "Postal";
  cboColumn.DataSource = list;
  cboColumn.SelectedIndex = 0;

  // left, right or middle search
  string[] list2 = new string[3];
  list2[0] = "Contains";
  list2[1] = "StartsWith";
  list2[2] = "EndsWith";
  cboFilterAtt.DataSource = list2;
  cboFilterAtt.SelectedIndex = 0;
 }

 private void btnSearch_Click(object sender, EventArgs e)
 {
  try
  {
   Cursor.Current = Cursors.WaitCursor; 
   CustomerSearchDataContext db = new CustomerSearchDataContext();
   //string condition = string.Format("{0}.{1}({2})", cboColumn.SelectedValue, cboFilterAtt.SelectedValue, txtSearch.Text);
   string condition = string.Format("{0}.{1}({2})", cboColumn.SelectedValue, cboFilterAtt.SelectedValue, "\"" + txtSearch.Text + "\"");
   MessageBox.Show(condition);
   var customerQuery = db.vCustomers.Where(condition).OrderBy("CustomerAccountNo");
   customerBindingSource.DataSource = customerQuery;
   dataGridView1.DataSource = customerBindingSource;
   dataGridView1.Columns["CustomerId"].Visible = false;
  }
  catch (System.Data.SqlClient.SqlException ex)
  {
   MessageBox.Show("An Error Occured - " + ex.Message,"Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
  }
  finally
  {
   Cursor.Current = Cursors.Default; 
  }
 }
}