Universal IQueryable Search Usable by Linq-to-SQL and Linq-to-Entities

ever since Linq came out, I have been improving my simple 'google-like' searches; I think I found the holy grail

UPDATE 05/12/2009 - I have done a ton of updates to the class using Reflection, and a new version which is much more usable and simple will be out in the next day or two.

The 'quick search' is a great tool. Google has shown us that searching with one single, universal search field is the way that people prefer to search. Anyone who has worked with Linq for any amount of time knows this is possible, but it requires a lot of hard-coding and a long jumble of 'where' statements. This class will allow you to run a universal 'google-like' search on any IQueryable.

Now what I am going to show you here was a breakthrough to me, and it has made my life a whole lot easier. It may not be the best way to do this, but it is the best/fastest way I could figure out (hint: I am always looking for improvements), and it is totally portable for *any* multi-column IQueryable collection, plus it does multiple relations as well!

dynamic linq

First of all, this uses the System.Linq.Dynamic dll that was released a while back - it writes a dynamic query each search run, so this was necessary in my approach. If you do not have it, you can get it here: http://msdn2.microsoft.com/en-us/vcsharp/bb894665.aspx.

data for the example

For this example, I am going to be working with Linq-to-SQL, but this works just as well with Linq-to-Entities and have used it in both types of projects. Here is the dummy data I am using for the demo:
CREATE TABLE dbo.category (
category_id  INT NOT NULL PRIMARY KEY IDENTITY,
category_name VARCHAR(MAX)
);

INSERT INTO dbo.category VALUES ('ninja');
INSERT INTO dbo.category VALUES ('pirate');
INSERT INTO dbo.category VALUES ('zombie');
INSERT INTO dbo.category VALUES ('weakling');

CREATE TABLE dbo._user (
_user_id INT NOT NULL PRIMARY KEY IDENTITY,
first_name VARCHAR(MAX),
last_name VARCHAR(MAX),
email_address VARCHAR(MAX),
category_id INT REFERENCES category(category_id)
);

INSERT INTO dbo._user VALUES 
  ('stan', 'naspinski', 'stan@mailinator.com', 2);
INSERT INTO dbo._user VALUES 
  ('sara', 'ortbals', 'hotchick@yahoo.com', 1);
INSERT INTO dbo._user VALUES
  ('rj', 'russell', 'punk@ilovedudes.com', 4);
INSERT INTO dbo._user VALUES 
  ('brian', 'pond', 'captweaksauce@ilovedudes.com', 4);
INSERT INTO dbo._user VALUES 
  ('idaho', 'edokpayi', 'idaho@yahoo.com', 1);
INSERT INTO dbo._user VALUES 
  ('phil', 'robles', 'crazyphil@hotmail.com', 3);
INSERT INTO dbo._user VALUES 
  ('jose', 'meza', 
    'iwishiwasasstrongasstan@gmail.com', 2);

Just a table of users with a referenced table of categories so I can demonstrate the multi-level search. Here is what my dbml looks like:
dbml

quick example

Now, say I want to do a universal search in all the fields for the word 'stan', it is as simple as this:
string[] columns_to_search = {"first_name", "last_name", "email_address", "category.category_name"};
string[] search_terms = {"stan"};
var search_results = new dbDataContext()._users.Search(columns_to_search, search_terms); 

All I did was specify which columns to include in the search, and what terms to search for; doesn't get much simpler than that. This is the simplest approach/overload, so you can get much more detailed if you want, and I will get into that later, but here you can see how powerful and simple this is. Here are the results when bound to a GridView:

search for 'stan'
search for 'stan'

This simply runs through the columns you specify and runs a Contains() against each field; if the keyword is found in one of those fields, it is considered to be a valid record. As you can see, the email address of of record 7 has the word 'stan' in it as well as the name and email address of record 1. Now if I narrow the search by adding another search term: 'pirate' you will see it narrows down the search to just one:

search for {'stan', 'pirate'}
search for 'stan', 'pirate'

Now you can not see 'pirate' in this GridView as it is just an auto-generated GridView, but 'pirate' is the value of the 'category.category_name' record 2 which is referenced in the last column (chopped off a bit); so you can see how easy it is to use relations with this tool.

what is happening

It is pretty simple what is happening. For each object that is passed in the 'keywords' the IQueryable is whittled down so only the ones that 'pass' remain:

searching with 'stan' and 'pirate' (psuedocode)
IQueryable results;
results = dbDataContext._users.Where(any_of_the_specified_fields.Contains(first term));
results = results.Where(any_of_the_specified_fields.Contains(second_term));
results = results.Where(any_of_the_specified_fields.Contains(third_term));
...and so on...

Note that this only does a single db call to get the first IQueryable (in the case your IQueryable is from a database), once that one is in memory, all searches are done against it, so it is pretty fast.

the search textbox

Now that is all very simple, but rarely are your searches going to be hard-coded, so it would be simple to code in a button handler to search through a specified textbox with something like this:
protected void btnSearch_Click(object sender, EventArgs e)
{
  char[] search_delimiters = { ',', ' ', ';', '+' };
  string[] columns_to_search =  {"first_name", "last_name", "email_address", "category.category_name"};
  
  gvSearch.DataSource = new dbDataContext()._users
    .Search( columns_to_search, txtSearch.Text.Split(search_delimiters));
  gvSearch.DataBind();
}

Pretty basic stuff there, you could also get your columns to search from a user interface like a bunch of CheckBoxes or a ListBox control.

getting more specific

If you look into the code, you can see it gets more and more specific the more you drill down into it. With the above overload, it assumes you are using all string comparable fields and all of your inputs are strings. The class's abilities do not stop there, and can get more and more specific. Say you had a massive table with the following fields: int named 'ID', DateTime named 'birthday', text named 'name' and bool named 'male'. If you simply tried the above overload with that, it would error as the types int, DateTime and bool do not have Contains() methods. But, if you specify name/type, you can use this to great advantage:
Dictionary ColumnNamesAndTypes =  new Dictionary<string, Type>();
ColumnNamesAndTypes.Add("ID", typeof(int));
ColumnNamesAndTypes.Add("first_played_videogames", typeof(DateTime));
ColumnNamesAndTypes.Add("name", typeof(string));
ColumnNamesAndTypes.Add("male", typeof(bool));

object[] search_for = {18, 25, DateTime.Parse("12/08/1981"), true, "a","b","c" };

var search_results = largeTable.Search(ColumnNamesAndTypes.ToArray(), search_for);

Now, since the Search now knows the types, it will only run valid types against the fields; for example, the 'true' object will only be run against the 'male' field, as it is the only 'bool' field, the values '18' and '25' are ints, so they will only be compared to the 'age' field of type 'int'. So, what this search would do is return all the people in the IQueryable 'largeTable' that are ages 18 and 25, who 'first_played_videogames' on 12/08/1981, that have an a,b or c in their names who are male. Now I know you can easily write these queries in Linq yourself, but if somehting changes, you are hardcoding a search change that is easy to screw up, this does it all for you.

parsing varying strings

Now there is a little more difficulty with attempting to pass in data through the magical 'google-like' single search box, but it really isn't that hard, check this out:
protected object[] parseSearchString(string search_string)
{
    int obInt;
    DateTime obDt;
    bool obBool;

    char[] search_delimiters = { ',', ' ', ';', '+' };
    List<object> obs = new List<object>();

    foreach (string s in search_string.Split(search_delimiters))
    {
        if (Int32.TryParse(s, out obInt)) obs.Add(obInt);
        else if (DateTime.TryParse(s, out obDt)) obs.Add(obDt);
        else if (bool.TryParse(s, out obBool)) obs.Add(obBool);
        else obs.Add(s); // else it's a string
    }
    return obs.ToArray();
}

Now I obviously didn't go through all the types there, but you get the idea. This takes in a string from a textbox and tries to parse it out to the more specific types before it defaults to a string. Now you might run into conflicts when you want to use integers as strings and such, but if you are this far in this post and still understanding things, I am sure you can figure out a way to code around that. Going back to the original example, now if I wanted to search the '_users' table for either 'category_id' (which is an integer) or the name of that category, I would just use the following:
protected void btnSearch_Click(object sender, EventArgs e)
{
  Dictionary<string, Type> columns_to_search = new Dictionary<string, Type>();
  columns_to_search.Add("category_id", typeof(Int32));
  columns_to_search.Add("category.category_name", typeof(string));
  gvSearch.DataSource = new dbDataContext()._users
    .Search( columns_to_search, parseSearchString(txtSearch.Text));
  gvSearch.DataBind();
}

And as you can see that uses the above 'parseSearchString' so if I enter in '1', it is searched as in Int32:
search for '1'

And if I type in 'ninja' is is searched with a 'string' (ninja is the string value of category_id 1):
search for 'ninja'

that's it

I am hoping that at least a few of you can decipher my ramblings... hopefully you can see how this can be useful to you, and with any luck, it will save you some time and headaches. If people are interested, I can go into the code and how it works, but for now I will just leave you with the source (I even wrote comments... something I am horrible at). I am hopeing someone knows how to get the fields/types automatically from a generic IQueryable so this can be made even better - if you know how to do that, please share!!!

ScrewTurn Wiki is a Great Open-Source Tool

Easy setup and a breeze to use

Recently, the nearly complete lack of documentation for my open-source ticket system Slick-Ticket was brought to my attention, so I decided to document it. In the sprit of open-source, I figured why not do it in a wiki format and let my userbase contribute if they wanted to.

So I did some searching and came across ScrewTurn Wiki which is a fantastic open-source Asp.Net wiki, I highly recommend it. I thought about doing a tutorial, but the setup was so stinkin' easy, I figured there was really no point - just change one variable in the web.config and you are running; big props to the guys over there for making it so easy.

So, here is my documentation for Slick-Ticket if anyone wants to see how ScrewTurn looks.

Slick-Ticket hits 1000 downloads!

My open source trouble ticket system has hit 1000 downloads in less than 4 months

I suppose this is more of a bragging point than anything, but I am pretty proud.  I released Slick-Ticket on Jan 18 and it currently has over 1000 downloads on CodePlex and is going quite strong.  I have had a lot of great input and the mini-community has really helped me track down bugs and improve the software. 

 

I have a few reviews/ratings and they are all good so far, so that is encouraging.  Here is one that made my day:

 

We are actually up and running for 2 weeks live supporting over 700 users and currently over 220 users enrolled with profiles.  Over 300 tickets in just two weeks and our users love it.  Works almost flawlessly but I do have a few quick questions.

 

Well, hopefully I can make that *almost flawlessy* into a *flawlessly* (I fixed the bugs he reported).  Hooray for open-source!

new advertising affiliate 'the Lounge'

a great advertising group that focuses on .Net technologies specifically

I was recently lucky enough to be able to join the '.NET Open Source Room' on The Lounge Advertising Network with my open source project Slick-Ticket, and they seem to have a pretty awesome setup. I stumbled upon the lounge while look at Matt Berseth's page as he is one of the member's of their 'Web Publishers Room' which includes a lot of great minds in the .Net community.
The Lounge is an exclusive advertising network of trusted and respected publishers focused on Microsoft technologies

Reach our passionate audience with quality ad placements that deliver results and enhance your brand.

If you are interested in advertising or publishing, check them out; they were extremely fast and friendly in response!

C-Sharpener.com - Another Project

Learn to Program Asp.Net/C# is Just Days - Guaranteed!

If you have been here before you have likely noticed the ad banner over to the right of the page. That is one of my newer projects that you can check out at C-Sharpener.com.

It is a video series provided with code and throrough explanations of how to get started in the web application and web programming world. The videos cover everything from setting up your environment (with all free tools) and arrays to things such as AJAX and Linq-to-SQL. If it doesn't work, I give you your money back - it's that simple.



Slick-Ticket Trouble Ticketing/Help Desk System

My first Open-Source Project!

This is a simple, to-the-point system. It was born out of loathing of the system that I was forced to use. It was tested in a live environment with hundreds of users and ultimately de-throned our expensive and bloated ticketing software. I really enjoyed developing this and my users and I really enjoy using it, I hope some other people out there like it.
  • Full Integrated with Active Directory means not another layer of permissions to add
  • Intuitive interface allows users to jump right in
  • Integrated help/faq system for administrators to inform users

Details

  • Utilizes .Net 3.5 (C#)
  • Asp.Net architecture built with Linq-to-SQL
  • Utilizes Asp.Net AJAX and the AJAX Control Toolkit
  • Completely customizable colors/themes
  • Installation program included, just load it on your machine and follow the directions

Requirements

  • SQL 2005 Database (SQLExpress works fine)
  • .Net 3.5
  • Active Directory

It is available for download at CodePlex. I also have a site up at http://slick-ticket.com with a demo site soon on the horizon.

Screenshots (Click for full-size images)

Settings interface for the administrator, including the theme customizer Adding a new ticket User profile, most data is pulled directly frorm AD (though you can change it)
*Also notice the different style with the sidebar on the opposite side
Integrated FAQ/Help section for information sharing Easy interface to view the tickets you are interested in View/comment a ticket in progress
*This is Licensed under the GNU General Public License version 2 (GPLv2)

Shout it kick it on DotNetKicks.com

DailyAtheistQuote.com

There is all sorts of daily bible quote sites out there, so I decided to make an atheist counterpart

DailyAtheistQuote.com is just that, a site that rotates great and insightful atheist quotes daily.

There are also widgets that you can put on your social networking websites such as MySpace and Facebook to show your atheist pride!

It also has the options to submit, view all and view random quotes.


AuctionConstructor.com

A free utility for making professional auction ads for use on sites such as eBay - Very simple to use and great looking html and css final product

Decided to make this a while back because I sell a sorts of stuff on eBay and I got tired of writing html for each and every ad. Once I came up with the concept, I figured why not allow other people to use it for free?

It has gone through a couple iterations; changed from ColdFusion to asp.net, and is constanty being updated and improved.

Pease check it out and give it a try for yourself!