generic data grid filtering

I have been working on a generic solution to filter data using Nhibernate. The code looks like this:

private ICriteria GetPagedCriteria<T>(GridResult<T> GridResult, bool sort)
        {
            var query = Session.CreateCriteria(typeof(T));

            foreach (string alias in GridResult.NHibernatePaths)
            {
                query.CreateAlias(alias, alias.Replace(".", "_"));
            }

            foreach (PropertyValueOperators pvo in GridResult.FilterList)
            {
                if(String.IsNullOrEmpty(pvo.Value) == false)
                {
                    switch (pvo.LikeOperator)
                    {
                        case "Contains":
                            query.Add(Expression.InsensitiveLike(Projections.Cast(NHibernateUtil.String, Projections.Property(pvo.Property)), String.Format("%{0}%",pvo.Value), MatchMode.Exact));
                        break;
                        case "EndsWith":
                            query.Add(Expression.InsensitiveLike(Projections.Cast(NHibernateUtil.String, Projections.Property(pvo.Property)), String.Format("%{0}", pvo.Value), MatchMode.Exact));
                        break;
                        case "Equals":
                            query.Add(Expression.InsensitiveLike(Projections.Cast(NHibernateUtil.String, Projections.Property(pvo.Property)), pvo.Value, MatchMode.Exact));
                        break;
                        case "Starts With":
                            query.Add(Expression.InsensitiveLike(Projections.Cast(NHibernateUtil.String, Projections.Property(pvo.Property)), String.Format("{0}%", pvo.Value), MatchMode.Exact));
                        break;
                        default:
                            throw new ArgumentException("LikeOperator not recognised");
                    }
                }
            }

            if (sort)
            {
                foreach (var pair in GridResult.SortList)
                {
                    var func = pair.Value
                       ? new Func<string, NHibernate.Criterion.Order>(NHibernate.Criterion.Order.Asc)
                       : new Func<string, NHibernate.Criterion.Order>(NHibernate.Criterion开发者_如何学运维.Order.Desc);
                    query.AddOrder(func(pair.Key));
                }
            }

            return query;
        }

Unfortunatley this creates SQL with lots of 'likes' and 'casts' which is very inefficient. To be expected I know ... I am not yet a NHibernate expert so would appreciate any comments to make this generic solution more efficient. Many thanks in advance.

Christian

There is no way how to avoid using like operators in your case. Well, Equal operator is simple, "begins with" that is translater to "like 'value%'" will be efficient, too, since it is left side match and it can use index, "contains" and "ends with" have to be translated to like operations as you already implemented.

If you really want to avoid like-based seach you have to change you approach - do not use CriteriaAPI and use Lucene.NET with NHibernate.Search which might match your needs better. I did not use them in production project but they seem perfect.

Contains, Endswith and Startswith cannot be converted to something similar in sql without like. A sql database is not ideal for these kind of queries. You can better use a search database like Lucene, combined with NHiberante.Search