locked
how to create dynamic query builder RRS feed

  • Question

  • I have set of tables which is populated from database

    for example employee and dept

    employee                                    dept

    empno                                        deptno

    empname                                   deptname

    when I select the multiple tables and respective fields dynamic query has to be formed

    like if user selects emp and dept and fields like deptno,empname

    so query should be

    select deptno, empname from emp,dept where dept.deptno=emp.empno

    is there any tool available which can used in c# or should we need to build a custom tool

    any code available for this


    vinay

    Monday, September 24, 2012 1:11 PM

Answers

  • Use predicate builder

    A real nice and tidy helper class

    using System;
    using System.Linq;
    using System.Linq.Expressions;
    using System.Collections.Generic;
     
    public static class PredicateBuilder
    {
      public static Expression<Func<T, bool>> True<T> ()  { return f => true;  }
      public static Expression<Func<T, bool>> False<T> () { return f => false; }
     
      public static Expression<Func<T, bool>> Or<T> (this Expression<Func<T, bool>> expr1,
                                                          Expression<Func<T, bool>> expr2)
      {
        var invokedExpr = Expression.Invoke (expr2, expr1.Parameters.Cast<Expression> ());
        return Expression.Lambda<Func<T, bool>>
              (Expression.OrElse (expr1.Body, invokedExpr), expr1.Parameters);
      }
     
      public static Expression<Func<T, bool>> And<T> (this Expression<Func<T, bool>> expr1,
                                                           Expression<Func<T, bool>> expr2)
      {
        var invokedExpr = Expression.Invoke (expr2, expr1.Parameters.Cast<Expression> ());
        return Expression.Lambda<Func<T, bool>>
              (Expression.AndAlso (expr1.Body, invokedExpr), expr1.Parameters);
      }
    }


    Mark Answered, if it solves your question and Vote if you found it helpful.
    Rohit Arora

    • Marked as answer by Mike Feng Monday, October 8, 2012 11:45 AM
    Friday, September 28, 2012 5:20 PM

All replies

  • Hi Vinay,

    I don't think Microsoft has such tool, you need to custom one for yourself.

    Have a nice day.


    Ghost,
    Call me ghost for short, Thanks
    To get the better answer, it should be a better question.

    Tuesday, September 25, 2012 10:39 AM
  • Use predicate builder

    A real nice and tidy helper class

    using System;
    using System.Linq;
    using System.Linq.Expressions;
    using System.Collections.Generic;
     
    public static class PredicateBuilder
    {
      public static Expression<Func<T, bool>> True<T> ()  { return f => true;  }
      public static Expression<Func<T, bool>> False<T> () { return f => false; }
     
      public static Expression<Func<T, bool>> Or<T> (this Expression<Func<T, bool>> expr1,
                                                          Expression<Func<T, bool>> expr2)
      {
        var invokedExpr = Expression.Invoke (expr2, expr1.Parameters.Cast<Expression> ());
        return Expression.Lambda<Func<T, bool>>
              (Expression.OrElse (expr1.Body, invokedExpr), expr1.Parameters);
      }
     
      public static Expression<Func<T, bool>> And<T> (this Expression<Func<T, bool>> expr1,
                                                           Expression<Func<T, bool>> expr2)
      {
        var invokedExpr = Expression.Invoke (expr2, expr1.Parameters.Cast<Expression> ());
        return Expression.Lambda<Func<T, bool>>
              (Expression.AndAlso (expr1.Body, invokedExpr), expr1.Parameters);
      }
    }


    Mark Answered, if it solves your question and Vote if you found it helpful.
    Rohit Arora

    • Marked as answer by Mike Feng Monday, October 8, 2012 11:45 AM
    Friday, September 28, 2012 5:20 PM