使用相关表中的数据创建动态LINQ BinaryExpression

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.

我有一个应用程序，其中所有查询都是根据WCF服务收到的简单数据消息动态创建的。简单地说，数据消息是一组列名/列值对，并添加了一个运算符，例如，等于，小于等等

Simple Data Message of ColumnName-Value-Operator

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.

我已经成功地使用基于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:

我遇到的问题是当我想通过关系查询另一个表中的值时，甚至深入了解第二个 - >第n个关系。像这样的东西：

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:

我手动编写LINQ查询没有问题：

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.

埃里克S.

2 个解决方案

#1

Note the use of expanding PropertyAccess.

请注意使用扩展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;

 }

#2

I had the same issue. Thanks for the answer! Very useful!

我遇到过同样的问题。感谢你的回答！很有用！

You wrote this:

你写了这个：

caller
{
  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;

 }

Maybe better if you write this:

如果你写这个可能更好：

public static BinaryExpression MakeQueryFilter<T>(string propertyNames, object value, string dataoperator, ParameterExpression parameter) where T : class
    {

        string[] props = propertyNames.Split('.');

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

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

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

        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;

    }