Why not use entity sql instead for filtering?

Feb 29, 2012 at 11:39 PM

I'm curious if there was a reason why you created the LinqExtensions to take string value that you convert into an expression instead of using System.Data.Entity's .Where(string) overload which takes an esql query? Or does that extension also translate into a dbquery /objectquery ?

Not criticizing but I'm curious if there's any foreseeable downfall to doing this...?

Coordinator
Mar 1, 2012 at 8:19 AM

Hi,

Those extensions aren't my creation, they are part of LINQ Dynamic Query Library (you can read more about it here: http://weblogs.asp.net/scottgu/archive/2008/01/07/dynamic-linq-part-1-using-the-linq-dynamic-query-library.aspx). Those extensions do translate into a dbquery/objectquery in the end.

There reason why I used them is history. I first used them when I was creating my initial set of samples for jqGrid (that was back in 2009) which were using Linq to Sql. Since that time I haven't changed it because that wasn't the part of samples I was focusing on (at some point I changed new sample to EF Code First but still kept the extensions).

I do agree that repository part of the samples could use a more thorough refactorization which would bring them up-to-date (especially know, when I prefer Micro ORM's for this kind of solutions).

To summary, I wouldn't use those extensions if I would be writing those samples from scratch now.

Regards,
Tomasz Pęczek

Mar 1, 2012 at 7:55 PM

come to think of it, i've used this same library before ( LINQ Dynamic Query Library)... i thought it looked familiar!

thanks a lot for your answer,  I am building something now that will make use of esql to be able to pass it up 2 layers into my DAL.

Mar 10, 2012 at 12:54 AM
Edited Mar 15, 2012 at 5:08 AM

I ended up using automapper to then find mapped property names (ViewModels->EF Entity) and then create entity sql string expressions from filters and sorts ....  if i were good at expression trees I'd write much better code to do this using linq expressions.

UPDATE: using AutoMapper for just this purpose is such a horrible idea unless you're already making those mappings for another purpose anyways.

Mar 15, 2012 at 5:07 AM
diegohb wrote:

I ended up using automapper to then find mapped property names (ViewModels->EF Entity) and then create entity sql string expressions from filters and sorts ....  if i were good at expression trees I'd write much better code to do this using linq expressions.


A better solution that just dawned on me is to decorate each Sortable/Filterable column with a EntitySQLSelector attribute... then when you get the jqGridRequest, read the sortname or SearchingFilter(s), find that property in the model using reflection, then retrieve the value of the EntitySQLSelector attribute to build the entity sql filter/sort expression... So when someone orders your grid by ProductCategory, the end result of this might be an entity sql sort expression like "it.Category.Name" (where it is Product) and then you can pass this all the way up to your DAL to use in EF ObjectQuery. or Filtering might be StartsWith(it.Category.Name,'Be'). This would return a filter where the product's category name start with "Be". You'd have one method that specifies all of the possible/supported entity sql canonical functions (http://msdn.microsoft.com/en-us/library/bb738626.aspx) like this:

/// <summary>
        /// Returns the entity sql equivalent for each operator.
        /// </summary>
        /// <param name="pOperator">The operator.</param>
        /// <returns></returns>
        private static string getFormatStringForOperator(JqGridSearchOperators pOperator)
        {
            switch (pOperator)
            {
                case JqGridSearchOperators.Eq:
                    return "it.{0} = @{1}";
                case JqGridSearchOperators.Ne:
                    return "it.{0} != @{1}";
                case JqGridSearchOperators.Lt:
                    return "it.{0} < @{1}";
                case JqGridSearchOperators.Le:
                    return "it.{0} <= @{1}";
                case JqGridSearchOperators.Gt:
                    return "it.{0} > @{1}";
                case JqGridSearchOperators.Ge:
                    return "it.{0} >= @{1}";
                case JqGridSearchOperators.Cn:
                    return "Contains(it.{0},@{1})";
                case JqGridSearchOperators.Nc:
                    return "!Contains(it.{0},@{1})";
                case JqGridSearchOperators.Bw:
                    return "StartsWith(it.{0},@{1})";
                case JqGridSearchOperators.Ew:
                    return "EndsWith(it.{0},@{1})";
                default:
                    throw new NotImplementedException
                        (string.Format
                             ("The operator '{0}' has not been implemented in ToEntitySQL() extension.", pOperator.ToString()));
            }
        }

 

 

Coordinator
Mar 15, 2012 at 11:28 PM

A very interesting approach. I will take a closer look at this as I might use it someday in one of my projects.