Create Dynamic LINQ BinaryExpression using data in related tables

Question

Hello, I have an application where all queries are created dynamically based on a simple data message received by a WCF service. A data message is, put simply, a collection of columnname/column value pairs, with the addition of an operator, e.g. Equals, Less Than, etc.

Simple Data Message of ColumnName-Value-Operator

Name, Joe, Equals
Age, 35, Less Than
Occupation, Geek, Equals
Rate, 1000, Greater Than

etc...

I have been somewhat successfully using dynamic binary expressions based on the contents of the datamessage.

BinaryExpression expression = null;
ParameterExpression parameter = Expression.Parameter(typeof(MessageType), "p");

foreach (row in DataMessage)
{
    BinaryExpression exp = DataLib.MakeQueryFilter(typeof(MessageType),
    row.ColumnName,row.ColumnValue,column.DataOperator.ToString(), parameter);

    expression = expression == null ? exp : Expression.AndAlso(expression, exp);

    results = DataContext.MessageType.Where(Expression.Lambda<Func<Media, bool>>(expression, parameter));

}



public static BinaryExpression MakeQueryFilter(Type type, string propertyName, object value, string dataoperator, ParameterExpression parameter)
    {

    //var type = oType.GetType();
    object queryvalue = null;

    var property = type.GetProperty(propertyName);

    Type propertyType = property.PropertyType;
    if ((propertyType.IsGenericType) && (propertyType.GetGenericTypeDefinition() == typeof(Nullable)))
        propertyType = propertyType.GetGenericArguments()[0];


    // convert the value appropriately 
    if (propertyType == typeof(System.Int32))
        queryvalue = Convert.ToInt32(value);
    if (property.PropertyType == typeof(DateTime))
        queryvalue = Convert.ToDateTime(value);
    if (property.PropertyType == typeof(Double))
        queryvalue = Convert.ToDouble(value);
    if (property.PropertyType == typeof(String))
        queryvalue = Convert.ToString(value);
    if (property.PropertyType == typeof(Guid))
        queryvalue = new Guid(value.ToString());

    var propertyAccess = Expression.MakeMemberAccess(parameter, property);
    var constantValue = Expression.Constant(queryvalue);

    Type[] types = new Type[2];
    types.SetValue(typeof(Expression), 0);
    types.SetValue(typeof(Expression), 1);

    var methodInfo = typeof(Expression).GetMethod(dataoperator, types);
    var equality2 = (BinaryExpression)methodInfo.Invoke(null, new object[] { propertyAccess, constantValue });

    return equality2;

}

The problem I am encountering is when I want to query through a relationship to a value in another table, and even go two->nth relationships deep. Something like this:

Name, Joe, Equals
Age, 35, Less Than
Jobs.Occupation, Geek, Equals
Jobs.Occupation.Salary.Rate, 1000, Greater Than

I have no problem writing the LINQ query by hand:

var results = from m in DataContext.MessageType
              where m.Name == "Joe"
              & m.Age == 35
              & m.Jobs.Occupation == "Geek"
              & m.Jobs.Occupation.Salaray.Rate >= 1000
              select m;

Any pointers how I can dynamically create this query? Any help is greatly appreciated. Thanks.

Eric S.

Answer 1

Note the use of expanding PropertyAccess.

{
  BinaryExpression expression = null;
  ParameterExpression parameter = Expression.Parameter(typeof(DataContext.Table), "p");

  string columns = "Relation1.Relation2.Column1";
  string value = "ABC123";

  BinaryExpression exp = MakeQueryFilter(typeof(DataContext.Table),  columns,value,'Equal', parameter);

  expression = expression == null ? exp : Expression.AndAlso(expression, exp);

  results = DataContext.Table.Where(Expression.Lambda<Func<Table, bool>>(expression, parameter));
}

public static BinaryExpression MakeQueryFilter(Type type, string propertyNames, object value, string dataoperator, ParameterExpression parameter)
{

  string[] acolumns = columns.Split('.');

  var property = typeof (MediaEvent).GetProperty(acolumns[0]);
  MemberExpression propertyAccess = Expression.MakeMemberAccess(parameter, property);

  Type propertyType = property.PropertyType;
  if ((propertyType.IsGenericType) && (propertyType.GetGenericTypeDefinition() == typeof(Nullable)))
   propertyType = propertyType.GetGenericArguments()[0];

  if (acolumns.Length > 1)
  {
    for (int i = 1; i < acolumns.Length; i++)
    {
       propertyAccess = Expression.MakeMemberAccess(propertyAccess, propertyAccess.Type.GetProperty(acolumns[i]));
     }
  }

  object queryvalue = null;
  // convert the value appropriately 
  if (propertyType == typeof(System.Int32))
   queryvalue = Convert.ToInt32(value);
  if (property.PropertyType == typeof(DateTime))
   queryvalue = Convert.ToDateTime(value);
  if (property.PropertyType == typeof(Double))
   queryvalue = Convert.ToDouble(value);
  if (property.PropertyType == typeof(String))
   queryvalue = Convert.ToString(value);
  if (property.PropertyType == typeof(Guid))
   queryvalue = new Guid(value.ToString());

  var constantValue = Expression.Constant(queryvalue);

  Type[] types = new Type[2];
  types.SetValue(typeof(Expression), 0);
  types.SetValue(typeof(Expression), 1);

  var methodInfo = typeof(Expression).GetMethod(dataoperator, types);
  var equality2 = (BinaryExpression)methodInfo.Invoke(null, new object[] { propertyAccess, constantValue });

  return equality2;

 }