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: SQL server design of Table ‘Post’
T-SQL
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 “asp.net 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
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: Post entity in LINQ to SQL model
Unittest
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();
6:
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: 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;");
5:
6: var postsQuery = from p in repository.Posts
7: select p;
8:
9: postsQuery = GetPostsQuery(postsQuery, "mvc", "", "", null);
10: postsQuery = GetPostsPagingQuery(postsQuery, 0, 5);
11:
12: List<Post> posts = postsQuery.ToList();
13:
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));
18:
19: if (!string.IsNullOrEmpty(tags))
20: postsQuery = postsQuery.Where(p => p.Tags.Contains(tags));
21:
22: if (!string.IsNullOrEmpty(bodyText))
23: postsQuery = postsQuery.Where(p => p.Body.Contains(bodyText));
24:
25: if (createdOn.HasValue && createdOn.Value > DateTime.MinValue)
26: postsQuery = postsQuery.Where(p => p.CreatedOn.Value.Date == createdOn.Value.Date);
27:
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);
7:
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…