none
Create a dynamic Join/Subquery for any expression RRS feed

  • Question

  • Hello everyone!

    I need to retreive information from table T1 based on a filter from colum C2 from table T2. Both are related through a C1 column. My issue is that the filter for column C2 is completely dynamic and is sent to me through a client defined expression in a string. I can parse it and create my own WHERE statement, but is contained in a string too, so I can't use lambda.

    I tried to use this WHERE statement as a subquery in the original query (as concatened strings, also), i.e.:

    "it.C1 = (SELECT x.C1 FROM T2 AS x WHERE ... )"
    "it.C1 IN (SELECT x.C1 FROM T2 AS x WHERE ... )"

    but both times it throws a runtime Exception telling me that the operator (= or IN) is not valid to compare string with a collection of rows (or something like that).

    How can I use this very dynamic WHERE statement to filter my query? Or any other approach that could help me?

    In advance, many thanks!


    I hope you are OK.
    Tuesday, July 5, 2011 11:23 PM

Answers

  • I ended up using a totally different solution. No Dynamic Linq required. Thanks.
    I hope you are OK.
    • Marked as answer by PanchoPonceN Wednesday, July 6, 2011 9:32 PM
    Wednesday, July 6, 2011 9:32 PM

All replies

  • Hi,

    For building dynamic queries you should use Dynamic LINQ.

    In this way, I build dynamic queries between related table more or less in this way:

    var formatExpr = string.Format("{0}.{1} = @0", T1.NavigationPropertyToT2, T2.PropertyName);
    Expression<Func<T1, bool>> expression = DynamicExpression.ParseLambda<T1, bool>(formatExpr, value);
    


    In your case if you want a contais condition, it could be something like this:

     

              var parameter = System.Linq.Expressions.Expression.Parameter(typeof(TEntity), "p");
              var property = System.Linq.Expressions.Expression.Property(parameter, "propertyName");
              var containsArg = System.Linq.Expressions.Expression.Coalesce(property, System.Linq.Expressions.Expression.Constant(0));
              var callContains = System.Linq.Expressions.Expression.Call(
                              typeof(System.Linq.Enumerable),
                              "Contains",
                              new Type[] { typeof(string) },  // type arg for Contains<TEntity>()
                              System.Linq.Expressions.Expression.Constant(Values.Select(value => value.ToString().ToUpper()).ToList()),
                              containsArg
                              );
              Expression<Func<TEntity, bool>> expression = System.Linq.Expressions.Expression.Lambda<Func<TEntity, bool>>(callContains, parameter);
     
    

     

    Hope this could help you,

    JA Reyes.


    Please remember to Vote & "Mark As Answer" if this post is helpful to you.
    Por favor, recuerda Votar y "Marcar como respuesta" si la solución de esta pregunta te ha sido útil.
    Wednesday, July 6, 2011 7:12 AM
  • I ended up using a totally different solution. No Dynamic Linq required. Thanks.
    I hope you are OK.
    • Marked as answer by PanchoPonceN Wednesday, July 6, 2011 9:32 PM
    Wednesday, July 6, 2011 9:32 PM