Repository pattern and filters

Published 10/21/2009 by Henry in C# | LINQ | Patterns
Tags: , ,

In my previous post Multiple search criteria searching using Linq to SQL I talked about a way to implement multiple search criteria queries using LINQ to SQL.
Because I am doing some work using the ASP.NET MVC Framework, I looked into patterns to make a loosely coupled data layer. Ofcourse I checked out Rob Connery’s blog, he created the MVC Storefront (now Kona) the code can be found on MVC Sample Apps on Codeplex. Rob is leveraging the Repository pattern, this pattern provides dependency-free access to data of any type. I saw the screen cast where Ayende Rahien talks about ‘Filters and Pipes’.
Rob implements the filters in the MVC Storefront, I really like this approach, better than the approach in my previous post Multiple search criteria searching using Linq to SQL because it’s much cleaner, it is possible to ‘chain’ multiple criteria, but every criteria has it’s own extension method, thus following the single responsibility principle.
In my previous post in some cases I did too much in one method, I build up an IQueryable<post> for four search criteria, which breaks the Single Responsibility principle for one.

Using filters, which are extension methods that specify a filter on an IQueryable of something, makes it possible to let the calling party build up whatever they need. I know this can sound confusing, but I feel the following code example explains much better.

In my previous post, I called a few methods, and build up a IQueryable that way to satisfy every search criteria in the query.

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

Listing 1

Consuming filters
Would not it be cool if we could use a fluent interface-like way to query the data, so it will be obvious to what we want to query?
Something like the code (also in a Unit test manner like the previous post) in listing 2:

   1:  [TestMethod]
   2:  public void Search_For_mvc_in_Title_With_Paging_Test()
   3:  {
   4:      List<Post> posts = GetPosts().WithTitleLike("mvc")
   5:                                   .WhereTagsContain("")
   6:                                   .WhereBodyContains("")
   7:                                   .WithPaging(0, 5)
   8:                                   .ToList();
   9:   
  10:      Assert.IsNotNull(posts, "DataContext did not return posts when searching for 'mvc' in title");
  11:      Assert.AreEqual(posts.Count, 2, "DataContext did not return 2 posts when searching for 'mvc' in title");
  12:  }

Listing: 2

 

I think it is clear that the code in listing 2 is far more readable, than the code in listing 1. Another advantage is that it is easy to reuse every part of the query whenever it is needed.

Get all()
First I create a method that returns all Posts present in the database as an IQueryable<Post>. By the way, I am not using Dependency Injection, or Inversion of Control in this example, because it does not help in explaining the filters concept. BUT I think that when using this technique in a real world application, it is  a good thing to use IoC (StructureMap, Windsor, Ninject, Unity, whatever…).

   1:  public IQueryable<Post> GetPosts()
   2:  {
   3:      var postsQuery = from p in repository.Posts
   4:      select p;
   5:      return postsQuery;
   6:  }

Listing: 3

Extension methods
Leveraging extension methods, a .NET Framework 3.0 feature in combination with the IQueryable<T> interface, it is possible to create the filters. The class needs to be static and public, the extension methods also need to be static and public.
The first parameter specifies which type the method operates on and needs to be preceded by the ‘this’ modifier.

   1:  public static class PostFilters
   2:  {
   3:      public static IQueryable<Post> WithTitleLike(this IQueryable<Post> postsQuery,
   4:                                             string title)
   5:      {
   6:          if (!string.IsNullOrEmpty(title))
   7:              postsQuery = postsQuery.Where(p => p.Title.Contains(title));
   8:   
   9:          return postsQuery;
  10:      }
  11:   
  12:      public static IQueryable<Post> WhereTagsContain(this IQueryable<Post> postsQuery,
  13:                                                string tags)
  14:      {
  15:          if (string.IsNullOrEmpty(tags))
  16:              return postsQuery;
  17:   
  18:          return postsQuery.Where(p => p.Tags.Contains(tags));
  19:      }
  20:   
  21:      public static IQueryable<Post> WhereBodyContains(this IQueryable<Post> postsQuery,
  22:                                                 string bodyText)
  23:      {
  24:          if (!string.IsNullOrEmpty(bodyText))
  25:              return postsQuery;
  26:   
  27:          return postsQuery.Where(p => p.Body.Contains(bodyText));
  28:      }
  29:   
  30:      public static IQueryable<Post> IsCreatedOn(this IQueryable<Post> postsQuery,
  31:                                           DateTime? createdOn)
  32:      {
  33:          if (!createdOn.HasValue && createdOn.Value == DateTime.MinValue)
  34:              return postsQuery;
  35:   
  36:          return postsQuery.Where(p => p.CreatedOn.Value.Date == createdOn.Value.Date);
  37:      }
  38:   
  39:      public static IQueryable<Post> WithPaging(this IQueryable<Post> postsQuery,
  40:                                          int? startRow,
  41:                                          int? rowCount)
  42:      {
  43:          if ((!startRow.HasValue) && (!rowCount.HasValue || rowCount.Value == 0))
  44:             return  postsQuery;
  45:   
  46:          return postsQuery.Skip((int)startRow).Take((int)rowCount);
  47:      }
  48:  }

Listing: 3

In listing 3 it is obvious that every method has its own responsibility, it is easily maintainable and very readable. I think this is an elegant solution for the problem I tried to solve in my previous post, I found this better technique and want to share.

Henry Cordes
My thoughts exactly…


 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’

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
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

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: 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;");
   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…


LINQ to Active Directory

Published 1/20/2008 by Henry in C# | LINQ
Tags: ,

Because I needed a way to get users from Active Directory to sync them with another application (CRM). I started out writing Directory Services (LDAP) Queries. I started thinking: "Can't this be done with LINQ?", after all in C# and the .NET Framework 3.0, 3.5  we now have the Language Integrated Query possibilities in our programming toolkit. With LINQ you should be able to query all collections, as long as they impement the IQueryable interface. So why not Active Directory?
When you search: "LINQ to AD" with Google. You''ll find the following link: http://www.codeplex.com/LINQtoAD.

Bart de Smet is a belgian developer, who works for Microsoft. He is the one that made LINQ to AD. Here is a serie blogposts he wrote about this topic.
LINQ to AD is a query provider for LINQ that's capable of talking to Active Directory (and other LDAP data sources potentially) over LDAP.
Ofcourse LINQ To AD is a wrapper around System.DirectoryServices.

He wrote the BdsSoft.DirectoryServices.Linq, the LINQ to AD implementation, if you want to use LINQ To AD you need to add a reference to this assembly and you need to reference the Active DS Type Library (ActiveDs.dll). The 'activeds.dll' is a module that contains functions and object methods, or COM components, for the Active Directory Services Interfaces (ADSI) API.

One of the reasons System.DirectoryServices is so powerfull, is because you can still access native ADSI interfaces by using the NativeObject method. NativeObject will return the IADs interface of the specific type of object.
To use the NativeObject method, you'll need to add a reference to the ActiveDs.dll library. I need to get all users belonging to a particular AD Group and sync these to my application.
All I need to do is: Add a reference the BdsSoft.DirectoryServices.Linq assembly and the Active DS Type Library (ActiveDs.dll).
Next I created a method that must Retrieve all users member of a group in the Active Directory.

The method will have two parameters:

  1. domain, a string that will hold the Active Directory domain name
  2. groupName, a string that will contain the name of the group in AD the users belong to

The method will return a List of type User, User is a custom object that represents a user and is called: "RetrieveADUsersInGroup";

Here a listing of the signature: 

   1:  private List<User> RetrieveADUsersInGroup(string domain, string groupName)
   2:  {
   3:      // Put code here
   4:  }
Listing 1

In the method, an instance of the System.DirectoryServices.DirectoryEntry object needs to be instantiated.
I pass a string to the constructor ('LDAP://'), so the DirectoryEntry instance is bound to the node in Active Directory Domain Services that is located at this specific path.

   1:  DirectoryEntry rootOfDirectory = new DirectoryEntry(string.Format("LDAP://{0}", domain));
Listing 2

Now we take this DirectoryEntry object (rootOfDirectory) and pass it to the constructor of the object DirectorySource<T> (which implements the IQueryable<T> interface), together with the enum System.DirectoryServices.SearchScope (defines the scope for a Directory search).
We take SearchScope.Subtree, which means the whole SubTree, including Base object and all child objects.

Here the signature of the method that is called and lives inside the BdsSoft.DirectoryServices.Linq assembly. 

   1:  public class DirectorySource<T> : IQueryable<T>, IDirectorySource
   2:  {
   3:      // Code here
   4:  }
Listing 3

Now we use the keyword: 'var', var is a way to declare variables in C# 3.0  and up, that uses implicit typing. Keep in mind that var is not the same as 'object', or the JavaScript 'var' datatype as it’s actually strongly typed, but inferred from whatever the value is being assigned.
The proces of creating datatypes on the fly is called: 'projection'. Var is projecting the datatype from the Query.

   1:      var groups = new DirectorySource<Group>(rootOfDirectory, SearchScope.Subtree);
   2:      var listOfAllGroups = from          grp in groups
   3:                            where         grp.Name == groupName
   4:                            select new {  grp.Name, MemberCount = grp.Members.Length, grp.Members };
Listing 4

On line 1 'var groups' the complete root directory is inferred as BdsSoft.DirectoryServices.Linq.DirectorySource<T>, where 'T' is of type 'ActiveDirectory.Group', my own class that represents an ActiveDirectory group. 
From line 2 on the LINQ syntax is used to get all groups from the Collection where the name is equal to the value in methods string parameter 'groupName'. From these groups or group the Name, MemberCount and Members are taken and returned by the query and assigned to the listOfAllGroups variable.

I wanted to map the result from this LINQ Query directly to my User type, instead of needing to loop through every result and map each property one by one. What provides an opportunity to show that you can map your own types to the Query's result, after all you can predict the projection result. 
First the code that retrieves the users: 

   1:  var UserFromAD = new DirectorySource<Centric.PublieksDiensten.KC.ActiveDirectory.Entities.User>(rootOfMemberDirectory, SearchScope.Subtree);
   2:   
   3:  IEnumerable<User> usersFromQuery = from adUser in UserFromAD
   4:                                     select new User
   5:                                     {
   6:                                          Firstname = adUser.FirstName,
   7:                                          Lastname = adUser.LastName,
   8:                                          AccountName = adUser.AccountName,
   9:                                          Email = adUser.Email,
  10:                                          PhoneNumber = adUser.TelephoneNumber,
  11:                                          Fax = adUser.Fax,
  12:                                          AddressStreet = adUser.AddressStreet,
  13:                                          City = adUser.City,
  14:                                          State = adUser.State,
  15:                                          Country = adUser.Country
  16:                                      };
Listing 5

On line 3 the variable 'usersFromQuery' is of type 'IEnumerable of type User' (IEnumerable<User>). IEnumerable needs to be implemented if we do not want to use 'var'. On line 4 the part 'select new' is extended to 'select new User' in this way we project our own User type. In the body of the Query you see every field is mapped to a property of the User type through field name/value pairs. Under the covers var infers the field names, field values, and field data types and creates an anonymous type with the fields we project.

Summary:

  • IEnumerable of type ...
  • select new ...
  • map through field name/value pairs

Here a listing with the complete code from the Method:

   1:  private static List<User> RetrieveADUsersInGroup(string domain, string groupName)
   2:  {
   3:      DirectoryEntry rootOfDirectory = new DirectoryEntry(string.Format("LDAP://{0}", domain));
   4:   
   5:      var groups = new DirectorySource<Group>(rootOfDirectory, SearchScope.Subtree);
   6:      var listOfAllGroups = from          grp in groups
   7:                            where         grp.Name == groupName
   8:                            select new {  grp.Name, MemberCount = grp.Members.Length, grp.Members };
   9:   
  10:      List<User> userList = new List<User>();
  11:   
  12:      foreach (var currentGroup in listOfAllGroups)
  13:      {
  14:          foreach (var member in currentGroup.Members)
  15:          {
  16:              DirectoryEntry rootOfMemberDirectory = new DirectoryEntry(GetLDAPPath(domain) + "/" + member);
  17:              var UserFromAD = new DirectorySource<Centric.PublieksDiensten.KC.ActiveDirectory.Entities.User>(rootOfMemberDirectory, SearchScope.Subtree);
  18:   
  19:              IEnumerable<User> usersFromQuery = from adUser in UserFromAD
  20:                                          select new User
  21:                                          {
  22:                                              Firstname = adUser.FirstName,
  23:                                              Lastname = adUser.LastName,
  24:                                              AccountName = adUser.AccountName,
  25:                                              Email = adUser.Email,
  26:                                              PhoneNumber = adUser.TelephoneNumber,
  27:                                              Fax = adUser.Fax,
  28:                                              AddressStreet = adUser.AddressStreet,
  29:                                              City = adUser.City,
  30:                                              State = adUser.State,
  31:                                              Country = adUser.Country
  32:                                          };
  33:   
  34:              userList.AddRange(usersFromQuery);
  35:          }
  36:      }
  37:   
  38:      return userList;
  39:  }
Listing 6

LINQ is a layer of abstraction over the way we query data. It is in the case of LINQ to AD a nice beginning to get rid of the LDAP Querying. LINQ overall provides a way to query Databases, Collections, Active Directory, XML and every Datasource for that matter in the same way from now on. In the LINQ to AD case the fact that had to query all groups and then loop through these groups to get the members of the groups is a big shortcoming. I had hoped to have to write one query that did all the work and returned a list of users. Still because the datasource is DirectoryServices and Active DS I can understand that their limits are in the way of making this possible. Still I hope in the future this will be possible.
I do like to work with LINQ and will try to learn more about it's inner workings. I definitely see a shift in paradigm, no more static language only in C# and I think change like this is exiting!

Henry Cordes
My thoughts exactly...