Monday, May 2, 2011

Extended Search using LINQ ....and its easy!

Building an effective search can be a time consuming head ache generating task. But if you have LINQ on your side, this can be quite easy. For this tutorial we'll be building a Windows Form that will query the Adventure Works database and search for products. Using LINQ, we'll build a base query that can be extended based on what the user is searching for.

1. First we'll create a new Windows Form Application. To this we'll add a DataGridView (named dgvProducts), a Button (named btnSearch), and 3 Textboxes (named txtProductName, txtProductNumber, txtProductColor). We'll add a few labels to identify the textboxes.

2. Next double-click the Search button to get the default click event method. For now, we'll leave it empty.

private  void  btnSearch_Click(object  sender, EventArgs  e)

3. Now, we'll create the Entity Framework context. Just right click on the solution and choose Add New Item. Choose Data, and choose ADO.NET Entity Data Model. Create the entity using the Adventure Works database. Just choose all the tables, even though we'll just be using the Products table.

4. Next, we'll create the BaseSearch method. This will be exactly what it is named, a simple search that will pull all the items from the Products table. As you can see, this method returns an IQueryable. Later we'll use this base IQueryable to add additional search parameters.

private  IQueryable <Product > BaseSearch()
AdventureWorksEntities  awe = new  AdventureWorksEntities ();
var  results = from  p in  awe.Products
select  p;
return  results;

5. Now, we'll create the method where the magic happens, called ExtendedSearch. This method will first create a copy of the BaseSearch query which it will use to extend. We have an if statement for each Textbox on the page, and we simply check if the textbox has anything in it. If so, we take the base query and append a Where operator. In the Where, we have a lambda that performs a Contains search on the property we're interested in.

private  IQueryable <Product > ExtendedSearch()
var  extendedSearch = BaseSearch();
if  (!String .IsNullOrEmpty(txtProductName.Text))
extendedSearch = extendedSearch.Where(x => x.Name.Contains(txtProductName.Text.Trim()));
if  (!String .IsNullOrEmpty(txtProductNumber.Text))
extendedSearch = extendedSearch.Where(x => x.ProductNumber.Contains(txtProductNumber.Text.Trim()));
if  (!String .IsNullOrEmpty(txtProductColor.Text))
extendedSearch = extendedSearch.Where(x => x.Color.Contains(txtProductColor.Text.Trim()));
return  extendedSearch;

6. Update the click event method to set the dgvProducts GridView to the result of the ExtendedSearch method.

private  void  btnSearch_Click(object  sender, EventArgs  e)
dgvProducts.DataSource = ExtendedSearch();

7. Run the application, and click the Search button without any criteria. You'll see everything come back. Now, enter some search criteria and you'll get a sub-set of the initial search.

That's it. As you can see, performing extended searches with LINQ is quite easy.


1 comment:

  1. In your post you are searching based on three key fields. I typically accomplish this with a poor-man's-Google type search. This is where I create a partial class on my domain entity with a field named SearchString that is a concatenation of each searchable property. Then I would support searches with a single "Search" textbox that checks to see if SearchString contains the search text. I'm not saying one way is better than the other, but I do like the way your method exposes an IQueryable.