Monday, 18 January 2010

Generics and Dynamic LINQ

In between running around like a headless chicken on various potential BI projects, I’m still doing “real” code as part of a project that is actually going somewhere. Specifically a less-plumbing implementation of a Windows Forms DataGridView using virtual mode. My starting point was this article, so I came up a with a grid that could plumbed into data access code via two methods with the following signature:

int GetCount(Filter filter)
List<T> GetData(int rowIndex, int pageSize, F filter, S sorter)
Where type T is the class of the data payload, F is the class of the filter payload, and S is the sorter payload class. I use payload here to highlight that the data contained by these objects is subject to implementation specifics. So no prescription is made about how F and S work. However I do have to provide a concrete implementation for test (and very likely production) purposes so I decided to go with a LINQ to SQL implementation. There are numerous (very good) examples of this kind of dynamic behaviour written by folk much cleverer than I, but as this code is being written for other developers to consume I decided to play it safe and use the System.Linq.Dynamic code from the Visual Studio 2008 samples (which can also be found in the \Program Files (x86)\Microsoft Visual Studio 9.0\Samples\1033 folder or thereabouts if you have the MSDN library installed). The file in question is called Dynamic.cs and can be found in the LinqSamples\DynamicQuery project. ScottGu did an introductory post on this many moons ago but never followed it up, so I’ve actually had to do some work (!!) and fill in the blanks myself. F becomes the Filter class, while S becomes the Sorter class. As all we need to define a filter is:
  • Field name
  • Comparison operator (=, !=, >=, >, <=, <, etc)
  • Value
Or in code (with DataContract attributes applied so this can be used across WCF):
using System.Runtime.Serialization;

namespace Public.DynamicQuery
{
  /// <summary>
  /// Comparisons supported by the dynamic query interface
  /// </summary>
  public enum ComparisonOperator
  {
      EqualTo,
      NotEqualTo,
      MoreThan,
      LessThan,
      MoreThanOrEqualTo,
      LessThanOrEqualTo,
      StartsWith,
      EndsWith,
      Contains,
      IsEmpty,
      IsNotEmpty
  }

  /// <summary>
  /// Filter criteria for a given field/property
  /// </summary>
  [DataContract]
  public class FilterCriteria
  {
      /// <summary>
      /// The name of the field/property to be filtered on
      /// </summary>
      [DataMember]
      public string FieldName { get; set; }

      /// <summary>
      /// The compare operator to be applied
      /// </summary>
      [DataMember]
      public ComparisonOperator ComparisonOperator { get; set; }

      /// <summary>
      /// The value which is being filtered against
      /// </summary>
      [DataMember]
      public object Value { get; set; }
  }
}
So the Filter class is basically a list of this information with a few helper methods to make it easier to use with the System.Dynamic.Linq namespace:
using System.Collections.Generic;
using System.Diagnostics;
using System.Runtime.Serialization;
using System.Text;

namespace Public.DynamicQuery
{
  /// <summary>
  /// Specifies a boolean condition
  /// </summary>
  public enum BooleanOperator
  {
      And,
      Or
  }

  /// <summary>
  /// Provides a serializable representation of query filters that
  /// can be used in Dynamic Linq expression trees
  /// </summary>
  [DataContract]
  public class Filter
  {
      #region Private properties

      private static Dictionary<ComparisonOperator, string> m_Dictionary;

      #endregion

      #region Constructors/destructors

      /// <summary>
      /// Creates a new instance of the Filter class
      /// </summary>
      public Filter()
      {
          lock (this)
          {
              if (m_Dictionary == null)
              {
                  m_Dictionary = GetComparisonOperatorToLinqStrings();
              }
          }
      }

      #endregion

      #region Public members

      /// <summary>
      /// Indicates how the filter criteria are evaluated in relation to one another
      /// </summary>
      [DataMember]
      public BooleanOperator BooleanCompare { get; set; }

      /// <summary>
      /// A list of filter criteria
      /// </summary>
      [DataMember]
      public List<FilterCriteria> Criteria { get; set; }

      /// <summary>
      /// Returns the string part of a dynamic linq predicate for this filter object with parameter placeholders
      /// (use the GetDynamicLinqParameters() method to get the parameters)
      /// </summary>
      /// <returns></returns>
      public string GetDynamicLinqString()
      {          
          if (Criteria != null && Criteria.Count > 0)
          {
              StringBuilder output = new StringBuilder();
              Debug.Assert(m_Dictionary != null);

              int parameterCounter = 0;
              int criteriaIndex = 0;

              foreach (FilterCriteria criteria in Criteria)
              {
                  criteriaIndex++;

                  if (criteria.ComparisonOperator == ComparisonOperator.IsEmpty ||
                      criteria.ComparisonOperator == ComparisonOperator.IsNotEmpty)
                  {
                      parameterCounter--;
                  }

                  string filterString = GetDynamicLinqStringPart(
                      criteria, criteriaIndex, parameterCounter);
                  output.Append(filterString);

                  parameterCounter++;
              }

              return output.ToString();
          }

          return "true";
      }

      /// <summary>
      /// Returns the parameter array part of a dynamic linq predicate for this filter object
      /// </summary>
      /// <returns></returns>
      public object[] GetDynamicLinqParameters()
      {
          List<object> objectList = new List<object>();

          if (Criteria != null && Criteria.Count > 0)
          {
              foreach (FilterCriteria criteria in Criteria)
              {
                  if (criteria.ComparisonOperator != ComparisonOperator.IsEmpty &&
                      criteria.ComparisonOperator != ComparisonOperator.IsNotEmpty)
                  {
                      objectList.Add(criteria.Value);
                  }
              }
          }

          return objectList.ToArray();
      }

      #endregion

      #region Private methods

      private Dictionary<ComparisonOperator, string> GetComparisonOperatorToLinqStrings()
      {
          Dictionary<ComparisonOperator, string> dictionary =
              new Dictionary<ComparisonOperator, string>();

          dictionary.Add(ComparisonOperator.Contains, "{0}.Contains(@{1})");
          dictionary.Add(ComparisonOperator.EndsWith, "{0}.EndsWith(@{1})");
          dictionary.Add(ComparisonOperator.EqualTo, "{0} == @{1}");
          dictionary.Add(ComparisonOperator.IsEmpty, "{0} == null");
          dictionary.Add(ComparisonOperator.IsNotEmpty, "{0} != null");
          dictionary.Add(ComparisonOperator.LessThan, "{0} < @{1}");
          dictionary.Add(ComparisonOperator.LessThanOrEqualTo, "{0} <= @{1}");
          dictionary.Add(ComparisonOperator.MoreThan, "{0} > @{1}");
          dictionary.Add(ComparisonOperator.MoreThanOrEqualTo, "{0} >= @{1}");
          dictionary.Add(ComparisonOperator.NotEqualTo, "{0} != @{1}");
          dictionary.Add(ComparisonOperator.StartsWith, "{0}.StartsWith(@{1})");

          return dictionary;
      }

      private string GetDynamicLinqStringPart(FilterCriteria criteria,
          int criteriaIndex, int parameterCounter)
      {
          StringBuilder output = new StringBuilder();

          if (criteria.ComparisonOperator == ComparisonOperator.IsEmpty ||
              criteria.ComparisonOperator == ComparisonOperator.IsNotEmpty)
          {
              output.Append(string.Format(m_Dictionary[criteria.ComparisonOperator],
                  criteria.FieldName));
          }
          else
          {
              output.Append(string.Format(m_Dictionary[criteria.ComparisonOperator],
                          criteria.FieldName, parameterCounter.ToString()));
          }

          if (criteriaIndex < Criteria.Count)
          {
              output.Append(string.Format(" {0} ", BooleanCompare.ToString()));
          }

          return output.ToString();
      }

      #endregion
  }
}
All we need to sort things is the field name and sort order:
using System.Runtime.Serialization;
namespace Public.DynamicQuery
{
  public enum SortDirection
  {
      Ascending,
      Descending
  }

  /// <summary>
  /// Sort directions for a single field
  /// </summary>
  [DataContract]
  public class SortCriteria
  {
      /// <summary>
      /// The field/property name to sort by
      /// </summary>
      [DataMember]
      public string FieldName { get; set; }

      /// <summary>
      /// The direction to order the data in
      /// </summary>
      [DataMember]
      public SortDirection SortOrder { get; set; }
  }
}
Then put these in a list, along with the dynamic LINQ helper methods:
using System.Collections.Generic;
using System.Runtime.Serialization;
using System.Text;

namespace Public.DynamicQuery
{
  /// <summary>
  /// Provides a serializable representation of sort criteria that can be used in expression trees
  /// </summary>
  [DataContract]
  public class Sorter
  {
      /// <summary>
      /// The fields and direction to sort by
      /// </summary>
      [DataMember]
      public List<SortCriteria> SortCriteria { get; set; }

      /// <summary>
      /// Returns the sort criteria as an expression that can be used with Dynamic LINQ
      /// </summary>
      /// <returns></returns>
      public override string ToString()
      {
          StringBuilder output = new StringBuilder("");

          if (SortCriteria != null && SortCriteria.Count > 0)
          {
              foreach (SortCriteria sortCriteria in SortCriteria)
              {
                  output.Append(sortCriteria.FieldName);

                  if (sortCriteria.SortOrder == SortDirection.Ascending)
                  {
                      output.Append(" Ascending,");
                  }
                  else
                  {
                      output.Append(" Descending,");
                  }
              }
              output.Remove(output.Length - 1, 1);
          }
          else
          {
              output.Append("1");
          }

          return output.ToString();
      }
  }
}
Anyway, to cut a long story short I wanted to be able to use these classes with LINQ to SQL in such a way as to derive the method signatures needed for virtual mode. I also needed an update method that could be used to post updates without needing to maintain the data context. So basically I wanted a generic wrapper class that would generate the methods for any LINQ to SQL table/class. Luckily I referred to the .NET gospel according to Juval Lowy (aka Programming .NET Components) which, among other things, contains appendices on Generics and Reflection. In particular I took advantage of the fact that Generics in .NET allow you to constrain the type to subclasses of particular class, or classes with a default constructor – to name a few. Also, all LINQ to SQL data contexts are derived from the DataContext class – which exposes the type-safe GetTable<T>() method for getting at table classes. I also found a very useful answer to a post (which I subsequently lost) on StackOverflow that explained how to pass updates via LINQ to SQL without access to the original data context. I wrapped this up in the ApplyChanges() method – which at least makes sense to me:
using System.Collections.Generic;
using System.Data.Linq;
using System.Linq;
using System.Linq.Dynamic;

namespace Public.DynamicQuery
{
  public class DataWrapper<T,D>
      where T : class
      where D : DataContext, new()
  {
      /// <summary>
      /// Retrieves a block of data within the given parameters
      /// </summary>
      /// <param name="startIndex">The zero-based index of the first row to retrieve</param>
      /// <param name="pageSize">The number of rows to retrieve</param>
      /// <param name="filter">The filters to apply to the dataset before returning the results</param>
      /// <param name="sorter">The sorting and ordering for the dataset before returning the results</param>
      /// <returns></returns>
      public List<T> GetData(int startIndex, int pageSize,
          Filter filter, Sorter sorter)
      {
          List<T> results = null;

          if (filter == null)
          {
              filter = new Filter();
          }

          if (sorter == null)
          {
              sorter = new Sorter();
          }

          using (DataContext dc = new D())
          {
              var query = (from p in dc.GetTable<T>()
                           select p);

              results = query.Where(filter.GetDynamicLinqString(),
                  filter.GetDynamicLinqParameters())
                  .OrderBy(sorter.ToString()).Skip(startIndex)
                  .Take(pageSize).ToList<T>();
          }

          return results;
      }

      /// <summary>
      /// Returns the total count of records in the dataset with the given filter applied
      /// </summary>
      /// <param name="filter">The filter to apply to the dataset</param>
      /// <returns></returns>
      public int GetCount(Filter filter)
      {
          int resultCount = 0;

          if (filter == null)
          {
              filter = new Filter();
          }

          using (DataContext dc = new D())
          {
              var query = (from p in dc.GetTable<T>()
                           select p);

              resultCount = query.Where(filter.GetDynamicLinqString(),
                  filter.GetDynamicLinqParameters()).Count<T>();
          }

          return resultCount;
      }

      /// <summary>
      /// Applies the given changes back to the dataset
      /// </summary>
      /// <param name="updates">Records which have been updated</param>
      /// <param name="inserts">Records to add</param>
      /// <param name="deletes">Records to be deleted</param>
      public void ApplyChanges(List<T> updates, List<T> inserts, List<T> deletes)
      {
          using (DataContext dc = new D())
          {
              if (updates!=null && updates.Count > 0)
              {                  
                  dc.GetTable<T>().AttachAll<T>(updates);
                  dc.Refresh(RefreshMode.KeepCurrentValues,updates);
              }

              if (inserts!=null && inserts.Count > 0)
              {
                  dc.GetTable<T>().InsertAllOnSubmit<T>(inserts);
              }

              if (deletes != null && deletes.Count > 0)
              {
                  dc.GetTable<T>().AttachAll<T>(deletes);
                  dc.GetTable<T>().DeleteAllOnSubmit<T>(deletes);
              }

              dc.SubmitChanges();
          }
      }
  }
}
So to put this all together we can add in some LINQ to SQL classes pointing to our favourite sample database (in this case the Product table from AdventureWorks) and run the following code in a console application: // Create a filter
            FilterCriteria filterCriteria = new FilterCriteria()
          {
              FieldName = "Name",
              ComparisonOperator = ComparisonOperator.Contains,
              Value = "Hex"
          };

          Filter filter = new Filter()
          {
              BooleanCompare = BooleanOperator.And,
              Criteria = new List<FilterCriteria>()
          };

          filter.Criteria.Add(filterCriteria);

          // Create a sorter
          SortCriteria sortCriteria = new SortCriteria()
          {
              FieldName = "ProductNumber",
              SortOrder = SortDirection.Descending
          };

          Sorter sorter = new Sorter()
          {
              SortCriteria = new List<SortCriteria>()
          };

          sorter.SortCriteria.Add(sortCriteria);

          DataWrapper<Product, AdventureWorksDataContext> dataWrapper =
              new DataWrapper<Product, AdventureWorksDataContext>();
        
          // Run the queries and apply the results
          int resultCount = dataWrapper.GetCount(filter);
          Console.WriteLine("Total dataset: " + resultCount.ToString() + " records");

          List<Product> results = dataWrapper.GetData(3, 3, filter, sorter);

          foreach (Product product in results)
          {
              Console.WriteLine("Name: " + product.Name + ", ProductNumber: "
                  + product.ProductNumber);
          }
Now as to dealing with the latency problem on a virtual mode DataGridView – that’s another problem (although Bea Stollnitz does have an interesting article on that topic). We could also do some type-safety checking on the filter and sorter classes, but you get the general idea.

4 comments:

  1. We have implemented something very similar for building dynamic searches. We have run into a problem for searching navigation properties where a one to many relationship exists. How could you handle the following? The main problem being that the GetDynamicLinqString()method cannot neatly fit inside a where() method.

    Example:

    var results = from person in persons from name in person.names where name.FamilyName.Contains("Smith") select person;

    or

    var results = persons.AsQueryable().SelectMany("Names").Where("FamilyName.Contains(@0)", "Smith").Select("Person");

    ReplyDelete
  2. There you have identified a flaw/shortcoming/feature-gap in my hack. I must admit it was only designed to handle the type of POCOs that I could put through a Windows Forms datagrid so I didn't consider navigation properties.

    These days I would use either WCF Data Services or RIA Services to do this as they support projections over the wire (and provide for a much more elegant programming model).

    ReplyDelete
  3. Can I use enumeration as values?

    like question asked in

    http://stackoverflow.com/q/7075816/185593

    ReplyDelete
  4. Not that I'm aware of - unless you specify a data type for the enumeration type and give each enumeration member a value. I must admit I stopped using dynamic LINQ shortly after I wrote this post!

    ReplyDelete