How to Rank your search results with multiple search terms using LINQ and EntityFramework

I have always used a ranked search criteria, it’s the only true way to get good results back from a data set.  But I’ve been doing my ranking within C# code.  But this got all the data from the data source and then ranked the results.  This is very poor on performance, as we should only be returning back the results for the page size we require.

So task at hand it to produce a LINQ statement to reterive only the data you require.

Here is the solution:

var entity = new myEntities();

var searchTerm = "a b Ba";

var searchArray = searchTerm.Split(new[] { ' ' }, StringSplitOptions.RemoveEmptyEntries);

var usersAll = entity.User.AsExpandable().Where(TC_User.ContainsInLastName(searchArray));

Console.WriteLine("Total Records: {0}", usersAll.Count());

var users = usersAll
    .Select(x => new { 
        x.LastName, 
        Rank = searchArray.Sum(s => ((x.LastName.Length - x.LastName.Replace(s, "").Length) / s.Length)) });

var results = users.OrderByDescending(o => o.Rank)
    .Skip(0)
    .Take(20);

foreach (var user in results)
{
    Console.WriteLine("{0}, {1}", user.LastName, user.Rank);
}

Console.ReadLine();

You’ll also need to add a new method to your User class to check for that the search term is contain in the LastName

public static Expression<Func<TC_User, bool>> ContainsInLastName(
                                                params string[] keywords)
{
    var predicate = PredicateBuilder.False<TC_User>();
    foreach (string keyword in keywords)
    {
        string temp = keyword;
        predicate = predicate.Or(p => p.LastName.Contains(temp));
    }
    return predicate;
}

One thing that is required is LinqKit, which is available via NuGet to handle the PredicateBuilder and AsExpandable.

Watch out as the results coming back from Sum is a BigInt so if you create a model to return back make sure it is a long type.

ILinqRepository at last

LINQ to SQL is a very easy to use and very powerful tool for many things when dealing with SQL.  But the time has come that I need to wrap it all up and use an iRepository pattern.  I’ve Googled for the IRepository and found a few useful references but none that will complete my picture.  How ever I did get some good ideas when looking around othe rimplementation of the IRepository pattern that others had implmented.

So why whould I use the iRepository when Linq to Sql and so good?

There are a few time, and this keeps happening that when you come to save an object you forget to use the SubmitChanges(), and what happens if there is a conflict.  You can handle conflicts in Linq to Sql but you need to add extra code for each time you SubmitChanges().  The iRepository I am going to use will wrap all this up and sort it out for us in to one place.  There are a number of reasons, but mainly the iRepository will provide a layer that I can use to hold all these methods in one place.

First the pattern will use Generics.  So this means that you can passing in any Linq to Sql object and it will perform a task.

So here is the Interface I’ll be using:

public interface IRepository<T> where T : class
    {
        /// <summary>
        /// Return all instances of type T.
        /// </summary>
        /// <returns></returns>
        IEnumerable<T> All();

        /// <summary>
        /// Return all instances of type T that match the expression exp.
        /// </summary>
        /// <param name="exp"></param>
        /// <returns></returns>
        IEnumerable<T> FindAll(Func<T, bool> exp);

        /// <summary>Returns the single entity matching the expression. Throws an exception if there is not exactly one such entity.</summary>
        /// <param name="exp"></param><returns></returns>
        T Single(Func<T, bool> exp);

        /// <summary>Returns the first element satisfying the condition.</summary>
        /// <param name="exp"></param><returns></returns>
        T First(Func<T, bool> exp);

        /// <summary>
        /// Mark an entity to be deleted when the context is saved.
        /// </summary>
        /// <param name="entity"></param>
        void MarkForDeletion(T entity);

        /// <summary>
        /// Create a new instance of type T.
        /// </summary>
        /// <returns></returns>
        T CreateInstance();

        /// <summary>Persist the data context.</summary>
        void SaveAll();

        long Count(Expression<Func<T, bool>> criteria);

        void Save(T entity);
    }

So that is the easy part, next is the concreate implementation of the interface

public class Repository<T> : IRepository<T>
        where T : class
    {
        protected DataContext DataContext;

        /// <summary>
        /// Return all instances of type T.
        /// </summary>
        /// <returns></returns>
        public IEnumerable<T> All()
        {
            return GetTable;
        }

        /// <summary>
        /// Return all instances of type T that match the expression exp.
        /// </summary>
        /// <param name="exp"></param>
        /// <returns></returns>
        public IEnumerable<T> FindAll(Func<T, bool> exp)
        {
            return GetTable.Where(exp);
        }

        /// <summary>See _vertexRepository.</summary>
        /// <param name="exp"></param><returns></returns>
        public T Single(Func<T, bool> exp)
        {
            return GetTable.SingleOrDefault(exp);
        }

        /// <summary>See _vertexRepository.</summary>
        /// <param name="exp"></param><returns></returns>
        public T First(Func<T, bool> exp)
        {
            return GetTable.First(exp);
        }

        /// <summary>See _vertexRepository.</summary>
        /// <param name="entity"></param>
        public virtual void MarkForDeletion(T entity)
        {
            DataContext.GetTable<T>().DeleteOnSubmit(entity);
        }

        /// <summary>
        /// Create a new instance of type T.
        /// </summary>
        /// <returns></returns>
        public virtual T CreateInstance()
        {
            var entity = Activator.CreateInstance<T>();
            GetTable.InsertOnSubmit(entity);
            return entity;
        }

        /// <summary>See _vertexRepository.</summary>
        public void SaveAll()
        {
            DataContext.SubmitChanges();
        }

        public long Count(Expression<Func<T, bool>> exp)
        {
            return GetTable.Where(exp).Count();
        }

        public void Save(T entity)
        {
            Save(new List<T> { entity });
            return;
        }

        public void Save(IEnumerable<T> entities)
        {
            var table = DataContext.GetTable<T>();

            foreach (var entity in entities)
            {
                var entity1 = entity;
                var dbEntity = (from p in table
                                where p == entity1
                                select p).FirstOrDefault();

                if (dbEntity == null)
                    table.InsertOnSubmit(entity);

                try
                {
                    //  Save the changes.
                    DataContext.SubmitChanges();
                }

                //  Detect concurrency conflicts.
                catch (ChangeConflictException)
                {
                    //  Resolve conflicts.
                    DataContext.ChangeConflicts.ResolveAll(RefreshMode.KeepChanges);
                }
            }
        }


        public Repository(DataContext dataContext)
        {
            DataContext = dataContext;
        }

        #region Properties

        private Table<T> GetTable
        {
            get { return DataContext.GetTable<T>(); }
        }

        #endregion
    }

That is it, job done.

You’ll notice that in the Save method it handles quite a lot from inserting a new record, updating an exisiting record if one is found with the addition of an object copier to make life easier and then will save the changes to the database and if there happens to be any conflicts it deals with them.

The Save() will accept either single objects or and IEnumerable of an object making things easier and simpler.

I would love any feed back on what I have produced

To compliment the IRepository concreate class I’ve built a set of tests using MS Test that create and use a test database:

IRepository.zip (4.11 kb)

IRepository.Tests.zip (47.25 kb)

Transaction scoping within Linq to Sql

If you are like me and us are using an IRepository for Linq to SQL you’ll probably at some point need to be able to generate a Transaction.

As the IRepository saves records as it goes and not on a SubmitChanges() we’re going to have to use the Microsoft TransactionScope Class, from the System.Transactions (in System.Transactions.dll), which makes a code block transactional.

Here it is:

using (var transaction = new TransactionScope())
{
    try
    {
	//Do something here

        transaction.Complete();
    }
    catch (Exception ex)
    {
        return;
    }
}

It is also worth having a look at Implementing an Implicit Transaction using Transaction Scoping for further information on what you can do.

After playing with this for a while I found that you some times get a MSDTC error, to fixes this take a look at:

Fix MSDTC (Microsoft Distributed transaction coordinator) by Pongsathon Keng