LINQ to SQL : Windows Forms Dynamic Where Clause

While I was reading the book LINQ in Action, I found this chapter. Below is how we can do in LINQ to SQL. The scenario is if you want to create a LINQ and pass the where clause based on user input from UI, you need to write different queries. Instead, how you can do it in single LINQ. I have used the Pubs from SQL Server 2000 sample from here.

Below are the steps done,

1. Open Visual Studio 2010

2. Create new project from File > New Project > Windows Forms Application, name it LINQ_Custom_Search

3. Add combo box with name comboBox1

4. Choose the property of comboBox1 and add Title Asc, Title Desc, Price Asc, Price Desc

5. Add button with a name btnSort, text Sort

6. Add a datagrid view with a name dataGridView1

7. Select the project from Solution Explorer, right click and Add > New Item.

8. Select LINQ to SQL Classes, name it pubsDB.dbml

9. From Server Explorer drag the table Title from Pubs database and drop it to pubsDB.dbml.

10. Add one utility method

void CustomSort<TKey>(Func<title, TKey> selector, Boolean ascending)

{

    IEnumerable<title> titles = db.titles;

    titles = ascending ? titles.OrderBy(selector) : titles.OrderByDescending(selector);

    dataGridView1.DataSource = titles.ToList();

}

11. Add the below code to btnSort’s click event

private void btnSort_Click(object sender, EventArgs e)

{

    switch (comboBox1.SelectedIndex)

    {

        case 0:

            CustomSort(title => title.title1,true);

            break;

        case 1:

            CustomSort(title => title.title1, false);

            break;

        case 2:

      CustomSort(title => title.price,true);

            break;

        case 3:

            CustomSort(title => title.price, false);

            break;

    }

}

12. And finally the simple code

pubDBDataContext db = new pubDBDataContext();

private void Form1_Load(object sender, EventArgs e)

{

    dataGridView1.DataSource = db.titles.ToList();

}

And you are done. Now people can choose the sort criteria from the application and based on that selection the query will be populated and generated.

Namoskar!!!