Click here to Skip to main content
15,120,078 members
Articles / Programming Languages / Visual Basic
Article
Posted 21 Nov 2016

Tagged as

Stats

13.5K views
290 downloads
4 bookmarked

Search Using Multiple Criteria In LINQ

Rate me:
Please Sign up or sign in to vote.
3.24/5 (9 votes)
21 Nov 2016CPOL4 min read
A simple article that describes how to search SQL Database using multiple criteria

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:

Image 1

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.

Image 2

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.

Image 3

Image 4

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.

Image 5

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.

Image 6

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.

VB.NET
''
'' 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.

VB.NET
''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.

VB.NET
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:

VB.NET
''
''

''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:

Image 7

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

Image 8

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

Image 9

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

Image 10

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:

Image 11

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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Zerayacob
Eritrea Eritrea
No Biography provided

Comments and Discussions

 
QuestionThis is Exactly what I already do (Vote of 5) Pin
DanW5223-Nov-16 5:36
MemberDanW5223-Nov-16 5:36 
QuestionLoop Pin
Member 1277327123-Nov-16 1:12
MemberMember 1277327123-Nov-16 1:12 
AnswerRe: Loop Pin
Zerayacob 23-Nov-16 3:57
MemberZerayacob 23-Nov-16 3:57 
QuestionThanks Pin
Jim_Snyder22-Nov-16 10:35
professionalJim_Snyder22-Nov-16 10:35 
PraiseRe: Thanks Pin
Zerayacob 23-Nov-16 4:15
MemberZerayacob 23-Nov-16 4:15 
GeneralRe: Thanks Pin
Jim_Snyder23-Nov-16 7:33
professionalJim_Snyder23-Nov-16 7:33 
PraiseShare Pin
sosplombierorleans21-Nov-16 7:16
Membersosplombierorleans21-Nov-16 7:16 
Currently working on a project and that's what I needed ty

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.