Search Using Multiple Criteria In LINQ
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:
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.