Search Using Multiple Criteria In LINQ

• Download Northwind Database - 648.4 KB
• Download source code - 1.3 KB

Introduction

This is a simple article about advanced search of SQL tables using different criteria, either one search criteria at a time, or several.

Background

Have you recently developed SQL based application? Chances are high that you wanted to implement a multiple condition based search. A lot of times when developing SQL based apps, we need to be able to search vast records by deploying different parameters. E.g., If we have a table of sales, we might want to search based on Receipt ID only, or Receipt ID plus Customer ID plus Sales date, etc. This article deals with a scenario like that and shows one easy way we could implement such capability into our application.

This application will use lambda expressions to search one table based on 9 criteria or fields.

Using the Code

First, we should attach and install the Northwind database (attached with the source code) to our SQL server. Then, create a new VB.NET application. Sample is given:

In order to access the database, we need to create a LINQ-To-SQL classes file and populate that file with tables from the Northwind database. In order to do that, we open the solutions explorer > Right click on the project > Click Add > Select Add New Item > Choose Data from the Installed Templates > And Select LINQ-To-SQL classes. Let's name the LINQ-To-SQL file as Test.

Next, we open the LINQ-To-SQL class file and open the server explorer and right click on Data connection > Select Add Connections > Choose Microsoft SQL Server if we are prompted to specify Datasource > choose the SQL Server name from the Server name Combobox as well as the database name from the List of installed databases and click OK.

After that, we open the server explorer and open the Northwind database > open tables >select all tables > Drag and drop all tables on the LINQ-To-SQL file we created.

Ok, next, we create the advanced search form. We will be searching the Orders table in the Northwind database in this example. Now, there are several parameters we can use to search, E.g. Customer ID, Employee ID, Shippers ID, OrderDate, Required Date, etc. In this example, there are 9 search parameters to use.

Ok, now for the fun part. We right click on the form and click View Code. First, we have to create an instance of the LINQ-To-SQL class. Let's call this instance datatest.

''
'' Creating Instance Of LINQ-To-SQL Class
''
Dim datatest as new TestDataContext

Next, we create a sub-procedure that will bind all the customers, shippers and employees to their respective comboboxes.

''Binding Elements To Combobox.
''
Private Sub MainBind()
Try
        ''Binding Customer Combo
        CustomerCombo.DataSource = From l In datatest.Customers Select l.CustomerID, l.CompanyName
        CustomerCombo.ValueMember = "CustomerID"
        CustomerCombo.DisplayMember = "CompanyName"
        CustomerCombo.SelectedValue = -1

        ''Binding Shippers ComboBox
        ShippersCombo.DataSource = From l In datatest.Shippers Select l.ShipperID, l.CompanyName
        ShippersCombo.ValueMember = "ShipperID"
        ShippersCombo.DisplayMember = "CompanyName"
        ShippersCombo.SelectedValue = -1

        ''Binding Employee ComboBox
        EmployeeCombo.DataSource = From l In datatest.Employees Select l.EmployeeID, l.FirstName
        EmployeeCombo.ValueMember = "EmployeeID"
        EmployeeCombo.DisplayMember = "FirstName"
        EmployeeCombo.SelectedValue = -1

      Catch ex as Exception
          MessageBox.Show(ex.Message, "LINQ Advanced Search", _
          MessageBoxButtons.OK, MessageBoxIcon.Information)
End Try

    End Sub

Perfect, next we define the default constructor and call the MainBind procedure inside the constructor. This will ensure that the elements are mapped to the combobox when we run the application.

Sub New()

       ' This call is required by the designer.
       InitializeComponent()

       ' Add any initialization after the InitializeComponent() call.

       ''We Call MainBind Procedure To Bind Data To Respective Combobox's
       MainBind()
   End Sub

Great, after this, we go back to form design and double click the search button to generate click event.
In that event, we copy the following code:

''
''

''Filtering  Orders table based on user criteria
''

 ''Select the entire Order table and put result in an Anonymous Type variable called SrchResult
        Dim SrchResult = From k In datatest.Orders Select k

        Dim flag As Boolean = False

        Try

            ''Filter All Orders Which Have The Selected Customer ID

            If CustomerCombo.SelectedValue <> Nothing Then
                flag = True
                SrchResult = SrchResult.Where(Function(p) p.CustomerID = _
                                            CStr(CustomerCombo.SelectedValue))
           End If

            ''Filter All Orders Which Have The Selected Shipper ID

            If ShippersCombo.SelectedValue <> Nothing Then
                flag = True
                SrchResult = SrchResult.Where(Function(p) p.ShipVia = _
                                            CInt(ShippersCombo.SelectedValue))
            End If

            ''Filter All Orders Which Have The Selected Employee ID

            If EmployeeCombo.SelectedValue <> Nothing Then
                flag = True
                SrchResult = SrchResult.Where(Function(p) p.EmployeeID = _
                                            CInt(EmployeeCombo.SelectedValue))
            End If

            ''Filter All Orders Which Have The Selected Order Date

            If OrderDatetxt.Text <> Nothing Then
                flag = True
                SrchResult = SrchResult.Where(Function(p) p.OrderDate = CDate(OrderDatetxt.Text))
            End If

            ''Filter All Orders Which Have The Selected Required Date

            If RequiredDateTxt.Text <> Nothing Then
                flag = True
                SrchResult = SrchResult.Where(Function(p) p.RequiredDate = CDate(RequiredDateTxt.Text))
            End If

            ''Filter All Orders Which Have The Selected Shipped Date

            If ShippedDateTxt.Text <> Nothing Then
                flag = True
                SrchResult = SrchResult.Where(Function(p) p.ShippedDate = CDate(ShippedDateTxt.Text))
            End If

            ''Filter All Orders Which Have The Selected Shipped Address

            If ShippedAddressTxt.Text <> "" Then
                flag = True
                SrchResult = SrchResult.Where(Function(p) p.ShipAddress = CStr(ShippedAddressTxt.Text))
            End If

            ''Filter All Orders Which Have The Selected Shipped City

            If ShippedCityTxt.Text <> "" Then
                flag = True
                SrchResult = SrchResult.Where(Function(p) p.ShipCity = CStr(ShippedCityTxt.Text))
            End If

            ''Filter All Orders Which Have The Selected Shipped Country

            If ShippedCountryTxt.Text <> "" Then
                flag = True
                SrchResult = SrchResult.Where(Function(p) p.ShipCountry = CStr(ShippedCountryTxt.Text))

           End If

          If flag = False Then
                MessageBox.Show("Please Select A Criteria For Search", _
                "LINQ Advanced Search", MessageBoxButtons.OK, MessageBoxIcon.Information)
                Exit Sub
            End If

            If SrchResult.Any Then
                '' Sort using Customer ID
                SrchResult = SrchResult.OrderBy(Function(k) k.CustomerID)

                    ''Bind result to the gridview
                SearchResultGrid.DataSource = SrchResult

            Else
                MessageBox.Show("There Is No Record That Matchs Your Criteria.", _
                "LINQ Advanced Search", MessageBoxButtons.OK, MessageBoxIcon.Information)

            End If

        Catch Ex As Exception
            MessageBox.Show(Ex.Message, "LINQ Advanced Search", _
            MessageBoxButtons.OK, MessageBoxIcon.Information)
        End Try

That is it. But before we test the application, let's go through the code. What the code is doing is simple: First, we simply copy the entire table to an anonymous type known as SrchResult, then we check whether the control (be it combobox or textbox) have any value selected or written on them, and if that is the case, the code will use Lambda functions to filter only the records inside the table that have a value that matches with the selected value. And it will repeat this task with all the 9 search conditions. Simple, right?

Now let's run the application in order to test. From the customer combobox, I have chosen the first element to perform the search. The result is shown below:

Let's filter further by selecting the one shipper from the shipper's combobox. The result is shown:

Or by choosing employee from the employee combobox. The result is shown:

Or by writing order date at the order date text box in the following format (YY/MM/DD). The result is shown:

Needless to say, we can use more than 2 search parameters at one time. For example, we can add shipped country parameter to the last example:

Conclusion

This example shows one of LINQ's main advantages: performing queries in a very easy and quick manner. These queries using Lambda Function could also be expanded to include multiple tables thereby creating much more powerful queries and scalability.