Also take a look at: >> Part 2

Multiple search-criteria searching is a often encountered requirement that is not always straight forward to implement. These days we have NHibernate, Entity Framework, LBLGen, LINQ to SQL and many more.
I find that the choice for an ORM is hard, simply because there is so much to choose from.
I will not go in the choice for one or the other, but will use LINQ to SQL in this example, in my opinion if you only need a one to one mapping of your database tables to domain objects and your database does not contain a lot of tables LINQ to SQL is the way to go. LINQ to SQL is intuitive, the querying of the DataContext works as you would expect it to work and the performance is better than a lot of stored procs and T-SQL I have seen running in production at some of my clients :-).
So in this post I am going to show how to implement a search using multiple search criteria with LINQ to SQL.

The table I use in my example is a simple table called Post, it only has a few fields (see picture 1).

Pic 1: The SQL Server design of the Post table
Pic 1: SQL server design of Table ‘Post’

So how do we search
with T-SQL, if we want the query to return all records of the ‘Post’ table where the Title field contains the text “mvc”.
So in T-SQL, that would be:

   1:  SELECT PostId, Title, … FROM Post WHERE Title LIKE ‘%mvc%’

Listing 1: T-SQL example LIKE query

Right? In T-SQL we have to place wildcards (the ‘%’) around the search criterion we use with the LIKE operator to get the behavior that records where the Title field is “ mvc framework” will be returned also, not only the records where the title field has the exact value: “mvc”.

When we execute the sql directly in the SQL Server Manager the result shows 2 rows (see picture 2).

Pic 2: Execute T-SQL in SQL Server directly
Pic 2: Execute T-SQL in SQL Server directly

LINQ to SQL model
I use LINQ to SQL to create a model (or a DataContext). The model shows the ‘entity’ you see in picture 3. As I mentioned earlier it is a one to one mapping with the SQL table from picture 1.

Pic 3: The LINQ to SQL designer shows the Post entity
Pic 3: Post entity in LINQ to SQL model

To easily proof how many rows will be returned
I create a unittest, that instanciates the MvcBlogDataContext and does a search in the Post table. The LINQ query also looks if the Title field of the Post table contains the text “mvc”. In the test the ‘Contains’ operator is used. ‘Contains’ in LINQ to SQL works exactly the same as the LIKE with the ‘%’ wildcards in listing 1. 

   1:  [TestMethod]
   2:  public void Search_For_mvc_in_Title_Test()
   3:  {
   4:    MvcBlogDataContext repository = new MvcBlogDataContext("Data Source=.;Initial Catalog=MvcBlog;Persist Security Info=True;");
   5:    List<Post> posts = repository.Posts.Where(p => p.Title.Contains("mvc")).ToList();
   7:    Assert.IsNotNull(posts, "DataContext did not return posts when searching for 'mvc' in title");
   8:    Assert.AreEqual(posts.Count, 2, "DataContext did not return 2 posts when searching for 'mvc' in title");
   9:  }

Listing 2: Unittest that proofs 2 records are in database

The test does an Assert on the posts object not being Null and on the number of posts in the posts object (List<Post>) being two. When we execute the test, will the test pass, or fail?

Pic 4: The unittest to proof we have 2 rows containing 'mvc' in the Title field of the Post table passed succesfully
Pic 4: Unittest to proof we have 2 rows containing 'mvc' in the Title field has passed

The test passed, the result returns two rows, so we now know two rows in the Post table contain a Title field that contains "mvc".

Make a search with multiple search criteria
Now when we have one search criterion, the LINQ to SQL query is straight forward. But in real life the requirements are in a lot of cases that you have a lot of search criteria, which can be used alone, or in combinations. So how do we go about in making a search with multiple search criteria?

   1:  [TestMethod]
   2:  public void Search_For_mvc_in_Title_And_Use_Paging_Test()
   3:  {
   4:    MvcBlogDataContext repository = new MvcBlogDataContext("Data Source=.;Initial Catalog=MvcBlog;Persist Security Info=True;");
   6:    var postsQuery = from p in repository.Posts
   7:                     select p;
   9:    postsQuery = GetPostsQuery(postsQuery, "mvc", "", "", null);
  10:    postsQuery = GetPostsPagingQuery(postsQuery, 0, 5);
  12:    List<Post> posts = postsQuery.ToList();
  14:    Assert.IsNotNull(posts, "DataContext did not return posts when searching for 'mvc' in title");
  15:    Assert.AreEqual(posts.Count, 2, "DataContext did not return 2 posts when searching for 'mvc' in title");
  16:  }

Listing 3: Unittest search with multiple search criteria and paging

Listing 3 shows a unittest that uses the MvcBlogDataContext, the by LINQ to SQL generated datacontext. Om line 6 the postsQuery object inferes it’s type (IQueryable<Post>) from the LINQ Query that selects all posts in the datacontext.
Line 9 in listing 3 calls a method GetPostsQuery that expects an IQueryable<Post> object and some other parameters (our search criteria). We pass the postQuery that selects all posts from the datacontext.

Line 10 in listing 3 does almost the same trick, but than with other parameters, the GetPostsPagingQuery extends the query with Paging criteria and returns the extended query again as an IQueryable<Post> object.

Line 12 in listing 3 executes the query by calling ToList().

Line 14 in listing 3 does an Assert on the posts object not being Null and the next line asserts the number of posts in the posts object (List<Post>) being two.

Multi search criteria LINQ to SQL query
The following listing (4) shows the method GetPostsQuery, the method that does the actual work of building the LINQ Query according the values of the search criteria. The method’s first parameter is of type IQueryable<Post>, it returns an IQueryable<Post> also. When we use IQueryable<T> it is possible to pass LINQ queries around and extend it. 
The method in listing 4 does check for every criteria (parameter) if it has a valid value. If it doesn’t, nothing happens and we move on. If a parameter (search criterion) does contain a valid value the method will extend the query by adding the criterion to the query. For nvarchar fields the Contains operator is used. Again ‘'Contains’ works exactly the same as the LIKE with the ‘%’ wildcards from listing 1.

   1:  /// <summary>
   2:  /// Gets a LINQ to SQL Query according the provided parameters
   3:  /// </summary>
   4:  /// <param name="postsQuery"></param>
   5:  /// <param name="title"></param>
   6:  /// <param name="tags"></param>
   7:  /// <param name="createdOn"></param>
   8:  /// <param name="bodyText"></param>
   9:  /// <returns></returns>
  10:  private IQueryable<Post> GetPostsQuery(IQueryable<Post> postsQuery, 
  11:                                         string title, 
  12:                                         string tags, 
  13:                                         string bodyText,
  14:                                         DateTime? createdOn)
  15:  {
  16:      if (!string.IsNullOrEmpty(title))
  17:          postsQuery = postsQuery.Where(p => p.Title.Contains(title));
  19:      if (!string.IsNullOrEmpty(tags))
  20:          postsQuery = postsQuery.Where(p => p.Tags.Contains(tags));
  22:      if (!string.IsNullOrEmpty(bodyText))
  23:          postsQuery = postsQuery.Where(p => p.Body.Contains(bodyText));
  25:      if (createdOn.HasValue && createdOn.Value > DateTime.MinValue)
  26:          postsQuery = postsQuery.Where(p => p.CreatedOn.Value.Date == createdOn.Value.Date);
  28:      return postsQuery;
  29:  }

Listing 4: IQueryable<Post> GetPostsQuery, extends and returns the IQueryable<Post> query

Listing 5 contains the method GetpostsPagingQuery. This method also extends and returns the query. LINQ to SQL provides paging with the Skip (skip all rows untill) and Take (take n number of rows) methods. And this method uses these methods to extends the query with paging capabilities.

   1:  private IQueryable<Post> GetPostsPagingQuery(IQueryable<Post> postsQuery,
   2:                                         int? startRow,
   3:                                         int? rowCount)
   4:  {
   5:      if ((startRow.HasValue) && (rowCount.HasValue && rowCount.Value > 0))
   6:          postsQuery = postsQuery.Skip((int)startRow).Take((int)rowCount);
   8:      return postsQuery;
   9:  }

Listing 5: Adds paging to query, using Skip and Take methods

We have everything wired up, all we have to do is execute the TestMethod in listing 3. In this test we only check for the title field to contain the text “mvc”. I think the idea is quite obvious, but in this post i do not really test it. So when I run the test, it passes, the test returned the same two rows like before:

Pic 5: The unittest that executed the programatically created LINQ Query according the search criteria passed succesfull

With LINQ to SQL it is possible to code queries that have to search using one or more search criteria in a structured and type safe way. It is not necessary to use string concatenation using wildcards. With LINQ to SQL we can solve this problem in a more elegant way. The fact that the SQL generated by LINQ to SQL performs really well is the icing on the cake. Or are you the type of developer that likes to write stored procs that 90 % of the time are the same but only the field and tablenames vary…

Henry Cordes
My thoughts exactly…

Comments (24) -

10/2/2009 4:20:58 AM #

This was one wonderful piece of work ... I have searched all over the internet to get to you and this is the model I was looking for ... great way to divide a huge parameters into several private methods which builds up the search criteria. Also, let me thank you about demonstrating the paging features within LINQ

Dragon | Reply

11/14/2009 8:28:03 PM #

Your Short cut icon looks so funny, and your solution is so great !! Smile
Look forward to seeing more than better articles Smile

11/22/2009 11:36:44 PM #

Excelent post, i don`t found a good example to work with dynamic queries with LTS, until i read this post. Good work.

11/23/2009 4:50:01 AM #


Glad you like it!
Take a look at following post, I think you may find it useful...

Imperialx | Reply

12/6/2009 4:53:54 AM #

This is great! Thanks for sharing...I got a question though, the result of your query is an 'AND' operator isn't? What then is the equivalent 'OR' operator result?

or should I say, how do you use the operator in your code concatenation on a lambda expression?

12/13/2009 6:13:30 PM #

Thanks, this really helped. BUT.. I do have a concern which is that if my IQueryable result returns lets say 500,000 data entries. I could see this approach causing   performance issues. What do you think? Should I only use this approach when I am dealing with small data results?

john | Reply

4/3/2010 7:01:23 AM #

I haven't test your code yet. From list #5, what is the result if the data contain for example:
row#2:title=mouse, tag=mouse, body=mouse
row#3:title=bla, tag=mvc, body=log

will your code return row#1 & row#3?

4/4/2010 6:52:44 AM #

@ John:
Sorry, I am not sure what youre question is.
Maybe if you take a look at part 2: matters will become clearer.

Abhijit khedkar | Reply

9/11/2010 2:15:36 AM #

@henry: thanks again
finally done it...

9/11/2010 2:50:06 AM #

Hi Abhijit,
Is it an idea to use
if (!string.IsNullOrEmpty(title))
  17:          postsQuery = postsQuery.Where(p => p.Title.ToLower().Contains(title.ToLower()));

Abhijit khedkar | Reply

9/11/2010 3:10:23 AM #

hey thanks,this post help me lot for serach criteria...but it will not give expected result when we put "Mvc" instead of "mvc"....i think "contains" is used for case sensitive test..if i want case insensitive seach criteria,
please help me...

9/16/2010 10:54:50 PM #


santzu | Reply

9/29/2010 10:42:17 PM #

Wouldn't this be terribly inefficient?  I think this would be a little better for performance (especially when there is a large amount of data in the table):

postsQuery = postsQuery.Where(p => (p.Title.Contains(title) || string.IsNullOrEmpty(title))
  && (p.Tags.Contains(tags) || string.IsNullOrEmpty(tags))
  && (p.BodyText.Contains(bodyText) || string.IsNullOrEmpty(bodyText))
  && (!createdOn.HasValue || createdOn.Value == DateTime.MinValue || p.CreatedOn > createdOn.Value.Date)

Just my two cents.  In the example provided, you will be loading the entire table into memory, and then widdling down the results a little at a time...A table with 1M+ records will choke L2Sql, EF, and pretty much every ORM out there.

9/30/2010 2:05:19 AM #

Thx for your 2 cents Wink
The post just tries to explain the multiple search criteria issue, ofcourse it is good to think about performance and how many data you load into memory for your situation.
I am glad you commented and found the post helpfull!

Kyle | Reply

10/31/2010 1:47:09 AM #

santzu is wrong. IQueryable<T> provides a means to construct the query before finally executing it via a call such as ToList(). This code would not load the entire table into memory but instead only the rows that are required.

Henry your solution is great and helped me a lot.

12/26/2010 10:29:45 PM #

Great work keep it coming

Yogesh India | Reply

4/7/2011 9:58:35 AM #

Hi Henry, I was just scratching my head to get this logic in L2S and i just went through you article,  made life easier .. Thanks much.

Raj United States | Reply

4/28/2011 9:56:57 PM # are the man...dude, I was looking to get a solution for a similar scenario and it is.

Great Post!!!


Irfan | Reply

11/2/2011 7:24:15 AM #

Thanks for nice post.

bipin United States | Reply

11/24/2011 11:25:42 AM #

nice post and plz keep repeating the post

12/3/2011 12:09:15 PM #

super piece of code!

12/5/2011 6:35:24 AM #

Thanks its a great help!

thiago da silva andrade soares Brazil | Reply

5/15/2012 7:39:39 AM #



Bundit Thailand | Reply

6/15/2012 2:58:36 AM #


Pingbacks and trackbacks (2)+

Add comment

  Country flag
  • Comment
  • Preview