Generic IQueryable 'OR' search for multiple search terms using expression trees

Continuing my latest theme of search extension methods, my new method allows users to search a property against multiple search terms.
The code has been added to my existing search extensions project which can be found here on github

    public static class QueryableExtensions
    {
        public static IQueryable<T> Search<T>(this IQueryable<T> source, Expression<Func<T, string>> stringProperty, params string[] searchTerms)
        {
            if (!searchTerms.Any())
            {
                return source;
            }

            Expression orExpression = null;
            foreach (var searchTerm in searchTerms)
            {
                //Create expression to represent x.[property].Contains(searchTerm)
                var searchTermExpression = Expression.Constant(searchTerm);
                var containsExpression = BuildContainsExpression(stringProperty, searchTermExpression);

                orExpression = BuildOrExpression(orExpression, containsExpression);
            }

            var completeExpression = Expression.Lambda<Func<T, bool>>(orExpression, stringProperty.Parameters);
            return source.Where(completeExpression);
        }

        private static Expression BuildOrExpression(Expression existingExpression, Expression expressionToAdd)
        {
            if (existingExpression == null)
            {
                return expressionToAdd;
            }

            //Build 'OR' expression for each property
            return Expression.OrElse(existingExpression, expressionToAdd);
        }
    }

This allows the following linq statement:

    var users = context.Users.Search(u => u.UserName, "john", "bob", "fred");

... which, if used via entity framework against a SQL database converts to the following SQL

    SELECT [Extent1].[Id] AS [Id],   
           [Extent1].[UserName] AS [UserName],   
           [Extent1].[FirstName] AS [FirstName],   
           [Extent1].[LastName] AS [LastName],   
           [Extent1].[Email] AS [Email],   
    FROM   [dbo].[Users] AS [Extent1]  
    WHERE ([Extent1].[UserName] LIKE N'%john%')   
       OR ([Extent1].[UserName] LIKE N'%bob%')   
       OR ([Extent1].[UserName] LIKE N'%fred%')

Generic IQueryable 'OR' search on multiple properties using expression trees

Following on from my previous post on creating a generic search extension method for IQueryable, I decided to take the concept a step further and create an additional method that allows you to search multiple properties for a particular search term. The syntax I wanted to use for this new method was as follows.

    
    //Search users where...
    var ninjaUsers = dataContext.Users.Search("ninja", x => x.UserName,  // ...UserName contains "ninja"
                                                       x => x.FirstName, // OR FirstName contains "ninja"
                                                       x => x.LastName); // OR LastName contains "ninja"

After a visit to stackoverflow and some expert guidance from @MarcGravell, this is the resulting code:

        
        public static IQueryable<T> Search<T>(this IQueryable<T> source, string searchTerm, params Expression<Func<T, string>>[] stringProperties)
        {
            if (String.IsNullOrEmpty(searchTerm))
            {
                return source;
            }

            var searchTermExpression = Expression.Constant(searchTerm);

            //Variable to hold merged 'OR' expression
            Expression orExpression = null;
            //Retrieve first parameter to use accross all expressions
            var singleParameter = stringProperties[0].Parameters.Single();

            //Build a contains expression for each property
            foreach (var stringProperty in stringProperties)
            {
                //Syncronise single parameter accross each property
                var swappedParamExpression = SwapExpressionVisitor.Swap(stringProperty, stringProperty.Parameters.Single(), singleParameter);

                //Build expression to represent x.[propertyX].Contains(searchTerm)
                var containsExpression = BuildContainsExpression(swappedParamExpression, searchTermExpression);

                orExpression = BuildOrExpression(orExpression, containsExpression);
            }

            var completeExpression = Expression.Lambda<Func<T, bool>>(orExpression, singleParameter);
            return source.Where(completeExpression);
        }

        private static Expression BuildOrExpression(Expression existingExpression, Expression expressionToAdd)
        {
            if (existingExpression == null)
            {
                return expressionToAdd;
            }

            //Build 'OR' expression for each property
            return Expression.OrElse(existingExpression, expressionToAdd);
        }

        private static MethodCallExpression BuildContainsExpression<T>(Expression<Func<T, string>> stringProperty, ConstantExpression searchTermExpression)
        {
            return Expression.Call(stringProperty.Body, typeof(string).GetMethod("Contains"), searchTermExpression);
        }

    //Create SwapVisitor to merge the parameters from each property expression into one
    public class SwapVisitor : ExpressionVisitor
    {
        private readonly Expression from, to;
        public SwapVisitor(Expression from, Expression to)
        {
            this.from = from;
            this.to = to;
        }
        public override Expression Visit(Expression node)
        {
            return node == from ? to : base.Visit(node);
        }
        public static Expression Swap(Expression body, Expression from, Expression to)
        {
            return new SwapVisitor(from, to).Visit(body);
        }
    }

Performing the following code against a DBContext (connected to a sql db):

    //Search users where...
    dataContext.Users.Search("ninja", x => x.UserName,  
                                              x => x.FirstName, 
                                              x => x.LastName).ToList();

Produces the following SQL:

    SELECT [Extent1].[Id] AS [Id], 
           [Extent1].[UserName] AS [UserName], 
           [Extent1].[FirstName] AS [FirstName], 
           [Extent1].[LastName] AS [LastName], 
           [Extent1].[Email] AS [Email], 
    FROM   [dbo].[Users] AS [Extent1]
    WHERE ([Extent1].[UserName] LIKE N'%ninja%') 
       OR ([Extent1].[FirstName] LIKE N'%ninja%') 
       OR ([Extent1].[LastName] LIKE N'%ninja%')

Because I can see more extension methods being added to this code I have created a SearchExtensions project on github. Please feel free fork this and make your own additions. Many thanks to @MarcGravell for helping me to see this task through.

C# Generic search extension method for IQueryable

Following on from my previous post on creating a generic repository method, I decided to take it a step further and create an generic search extension method to perform the same task.
Here is the code:

    
    public static class QueryableExtensions
    {
        public static IQueryable<T> Search<T>(this IQueryable<T> source, Expression<Func<T, string>> stringProperty, string searchTerm)
        {
            if (String.IsNullOrEmpty(searchTerm))
            {
                return source;
            }

            // The below represents the following lamda:
            // source.Where(x => x.[property] != null
            //                && x.[property].Contains(searchTerm))

            //Create expression to represent x.[property] != null
            var isNotNullExpression = Expression.NotEqual(stringProperty.Body, Expression.Constant(null));

            //Create expression to represent x.[property].Contains(searchTerm)
            var searchTermExpression = Expression.Constant(searchTerm);
            var checkContainsExpression = Expression.Call(stringProperty.Body, typeof(string).GetMethod("Contains"), searchTermExpression);

            //Join not null and contains expressions
            var notNullAndContainsExpression = Expression.AndAlso(isNotNullExpression, checkContainsExpression);

            var methodCallExpression = Expression.Call(typeof(Queryable),
                                                       "Where",
                                                       new Type[] { source.ElementType },
                                                       source.Expression,
                                                       Expression.Lambda<Func<T, bool>>(notNullAndContainsExpression, stringProperty.Parameters));

            return source.Provider.CreateQuery<T>(methodCallExpression);
        }
    }

Performing the following code against a DBContext (connected to a sql db):

    string searchTerm = "test";
    var results = context.Clubs.Search(club => club.Name, searchTerm).ToList();

Which produces the following SQL:

    SELECT [Extent1].[Id] AS [Id], 
           [Extent1].[Name] AS [Name] 
    FROM   [dbo].[Clubs] AS [Extent1]
    WHERE  ([Extent1].[Name] IS NOT NULL) 
      AND  ([Extent1].[Name] LIKE N'%test%')

Next goal is to create an extension method that allows the user to pass multiple properties. The results will then match any of the supplied properties. Stay tuned...

Update:

Check out a new post on a new search extension method. Also, this project is now on github

Generic Repository Search function with Expression Trees

Expression trees have been a bit of a magic box for me for a while so I decided to do something about it and learn a bit about them.

I work with entity framework quite a bit and in most cases I use a base repository to perform the common logic such as retrieving records by id, or retrieving all records. I decided to try and implement a generic string search method on my base repository.

I decided that I wanted the following syntax when calling my search functionality:

    this.repository.Search(x => x.Name, searchTerm);

To break this down, the first parameter (x => x.Name) is a lamda expression that represents the string property I want to search within. The second parameter is there search text I want to match on.

After much trial and error, trawling stack overflow and swatting up on the msdn documentation, I finally came up with the following

    public class Repository<T> : IRepository<T> 
        where T : class, IEntity
    {
        /// <summary>
        /// Performs a search on the supplied string property
        /// </summary>
        /// <param name="stringProperty">Property to search upon</param>
        /// <param name="searchTerm">Search term</param>
        public virtual IQueryable<T> Search(Expression<Func<T, string>> stringProperty, string searchTerm)
        {
            var source = this.RetrieveAll();

            if (String.IsNullOrEmpty(searchTerm))
            {
                return source;
            }

            //The following is the query we are trying to reproduce
            //source.Where(x => T.[property] != null 
            //               && T.[property].Contains(searchTerm)

            //Create expression to represent T.[property] != null
            var isNotNullExpression = Expression.NotEqual(stringProperty.Body, Expression.Constant(null));

            //Create expression to represent T.[property].Contains(searchTerm)
            var searchTermExpression = Expression.Constant(searchTerm);
            var checkContainsExpression = Expression.Call(stringProperty.Body, typeof(string).GetMethod("Contains"), searchTermExpression);

            //Join not null and contains expressions
            var notNullAndContainsExpression = Expression.AndAlso(isNotNullExpression, checkContainsExpression);

            //Build final expression
            var methodCallExpression = Expression.Call(typeof (Queryable), 
                                                       "Where", 
                                                       new Type[] {source.ElementType}, 
                                                       source.Expression, 
                                                       Expression.Lambda<Func<Club, bool>>(notNullAndContainsExpression, stringProperty.Parameters));

            return source.Provider.CreateQuery<T>(methodCallExpression);
        }

        public IDataContext DataContext { get; private set; }

        public Repository(IDataContext dataContext)
        {
            this.DataContext = dataContext;
        }

        /// <summary>
        /// Retrieve all records from context for a given type
        /// </summary>
        /// <returns></returns>
        public virtual IQueryable<T> RetrieveAll()
        {
            return this.DataContext.Set<T>();
        }
    }

Thanks in particular to the following articles for helping me learn a bit about the magic box.

• How to: Use Expression Trees to Build Dynamic Queries
• How do I dynamically create an Expression<Func<MyClass, bool>> predicate?

This task is not complete, I am still yet to look at the sql it produces when hooked up to my sql db. I am also yet to look at it's performance but that will be the subject of a later post where I will hopefully make some tweaks and enhancements

Any questions or pointers, please add a comment and I'll do my best to get back to you.

Creating a dropdown list from an Enum in MVC3 C#

Recently I have been displaying enums as drop down lists on some MVC 3 projects and noticed there are a few people out there looking for a solution

Here is an enum helper I use that turns an enum into a select list. Note: If the enum has a description (using the DescriptionAttribute) it will use that as its display text

public static class EnumHelper
{
    //Creates a SelectList for a nullable enum value
    public static SelectList SelectListFor<T>(T? selected)
        where T : struct
    {
        return selected == null ? SelectListFor<T>()
                                : SelectListFor(selected.Value);
    }

    //Creates a SelectList for an enum type
    public static SelectList SelectListFor<T>() where T : struct
    {
        Type t = typeof (T);
        if (t.IsEnum)
        {
            var values = Enum.GetValues(typeof(T)).Cast<enum>()
                             .Select(e => new { Id = Convert.ToInt32(e), Name = e.GetDescription() });

            return new SelectList(values, "Id", "Name");
        }
        return null;
    }

    //Creates a SelectList for an enum value
    public static SelectList SelectListFor<T>(T selected) where T : struct 
    {
        Type t = typeof(T);
        if (t.IsEnum)
        {
            var values = Enum.GetValues(t).Cast<Enum>()
                             .Select(e => new { Id = Convert.ToInt32(e), Name = e.GetDescription() });

            return new SelectList(values, "Id", "Name", Convert.ToInt32(selected));
        }
        return null;
    } 

    // Get the value of the description attribute if the 
    // enum has one, otherwise use the value.
    public static string GetDescription<TEnum>(this TEnum value)
    {
        FieldInfo fi = value.GetType().GetField(value.ToString());

        if (fi != null)
        {
            DescriptionAttribute[] attributes =
             (DescriptionAttribute[])fi.GetCustomAttributes(
    typeof(DescriptionAttribute),
    false);

            if (attributes.Length > 0)
            {
                 return attributes[0].Description;
            }
        }

        return value.ToString();
    }
}

Once you have this helper class in place you can do the following.

In your controller:

    //If you don't have an enum value use the type
    ViewBag.DropDownList = EnumHelper.SelectListFor<MyEnum>();

    //If you do have an enum value use the value (the value will be marked as selected)
    ViewBag.DropDownList = EnumHelper.SelectListFor(myEnumValue);

In your View:

@Html.DropDownList("DropDownList")

Hey presto you have a drop down list for your enums which binds back to your view model on post.

Joli OS

I have an old netbook that has been sitting around for a while gathering dust. It's slow and not good for much so I decided to see if I could bring it back to life as a 'web-book' whilst, at the same time, giving myself a chance to play around with something new.

My initial thought was to Chrome OS (Chromium) but I thought I should really look around to see what else is out there. After a bit I came across Joli OS. LikeChrome OS, Joli is built upon the Linux Ubunto OS. I have little to no knowledge of Linux so this was fast going from a little play to a mini adventure.

Similar to Chrome OS, Joli OS is a web centric operating system that is light weight and ideal for netbooks. It focuses largely on web apps, but solely. One big difference between Chrome OS and Joli OS is that Joli has the ability to install native apps, not just webapps. This means that unlike Chrome OS it is possible to install Skype, Spotify and Gimp on your machine and because Joli is based on Ubunto, in theory you could install any ubuntu compliant software. But wait, it doesn't stop there, half way through my adventure I stumbled across Wine (no, not the beverage). Wine is a nifty Windows emulator for Linux that allows you to run windows apps on Joli too. Cool!

Now, remember, I'm still on my under powered, out of date little netbook so I don't necessarily want loads of apps athough, it is nice to have the option to add skype or pidgin to fill the litle gaps where the webstore doesn't cater for my needs.

All in all Joli OS has truly recycled my old netbook. I think it's a great little system that was perfect for my needs. This was also my first voyage into a Linux operating system and honestly, it wasn't so bad, in fact it was pleasant, maybe a second voyage is required.

www.jolicloud.com

Getting schema information from an edmx file with POCO

In this example I have two tables in my database, Customer and Company. Customer belongs to the dbo schema whereas Company belongs to the 'other' schema. By default the edmx file does not offer up information about the schema of the original tables however this is something that is stored in the underlying xml.

To retrieve this information we need to edit our tt template. NOTE: I'd recommend getting tangible T4 Editor when working with .tt files as it provides syntax highlighting and intellisense which makes working with these file much easier.

At the top of our tt template (after the input file has been declared) we need to create a new store item collection that will grab the schema information using the code below.

StoreItemCollection sic;
loader.TryCreateStoreItemCollection(inputFile, out sic);
EntityContainer sicEntityContainer = sic.GetItems().FirstOrDefault();

Then from within the foreach (EntityType entity in ItemCollection.GetItems()...) loop you can get the current schema name with the following:

string schemaName = "Unknown";
if (sicEntityContainer != null)
{
  EntitySet eset = sicEntityContainer.GetEntitySetByName(code.Escape(entity), true);
  schemaName = eset.MetadataProperties["Schema"].Value.ToString();
}

Now we have the schema name we can do what we like with it. You might want to add a readonly property by adding the following just after the class is created

public string Schema
{
    get { return "<#= schemaName #>"; }
}

With these small changes our auto generated classes now look like this:

Hope this helps