How to write LINQ query with column name as parame

Question

I am seeking help on how to achieve this with LINQ in a type safe way.

I need to perform search on "Performance" table with many columns. Based on the criteria specified for search I need to pick columns and perform search on those columns with given values.

private static IQueryable<Investment> PerformanceSearch(IQueryable<Investment> investments, **??? searchColumn**, double minValue, double maxValue)
{
  var entity = ExtendedEntities.Current;

  investments = from inv in entity.Investments 
                join performance in entity.Performances on inv.InvestmentID equals perfromance.InvestmentID
                where **performance.searchColumn** >= minValue && **performance.searchColumn** = maxValue
  return investments;
}

Now I am seeking your help on:

1. How to pass column "searchColumn" to this method in a type safe way? I was thinking of creating a dictionary object to accommodate some way to maintain column names from entity framework. But not sure how to achieve this.

2. How to perform LINQ query using the columnName passed and applying where clause.

I cannot use If Else or Switch case as below is a list of possible search columns...

/*
 * Search Columns can be:
 *      "Return1Month", "Return2Months", "Return3Months", ... almost 10 more and
 *      "Risk1Month", "Risk2Months", "Risk3Months", ... almost 10 more and
 *      "TrackingError1Month", "TrackingError2Months", "TrackingError3Months", ... almost 10 more and
 *      2 more similar set of columns ... 
 */

I spent time on Stackoverflow, Microsoft and other blogs and considered using Dynamic LINQ but it's not type safe. It seems that it is achievable using expressions but couldn't get that working.

Any advice is appreciated.

EDIT -

Another item to mention - all search columns are present in "performance" table.

Answer 1

I think you should be able to do this using just a Func<TIn,TOut> parameter (expressions not needed in this case). Make the function generic to be type safe whatever the type of the column might be. Here's what I'm thinking ...

private static IQueryable<Investment> PerformanceSearch<TMember>(
                              IQueryable<Investment> investments, 
                              Func<Performance,TMember> SearchColumn, 
                              TMember minValue, 
                              TMember maxValue)
{
    var entity = ExtendedEntities.Current;

    investments = from inv in entity.Investments 
        join perfromance in entity.Performances on inv.InvestmentID equals perfromance.InvestmentID
        where SearchColumn(perfromance) >= minValue && SearchColumn(perfromance) <= maxValue
    return investments;
}

Then you'd invoke it like this:

var results = PerformanceSearch<double>(investments, p => p.Return1Month, 10.0, 20.0);

Answer 2

Hands down, LINQ expressions are the best way to dynamically build LINQ queries in a strongly typed manner. You are absolutely right to discard the Dynamic LINQ Library! LINQ Expressions are challenging to grasp at first, but I promise you that the end pay off is well worth the effort.

Here is an example that uses LINQ expressions to accomplish what you want. You'll notice it doesn't include any string column names, switch statements, helper classes, or enums. You will need to import the System.Linq.Expressions namespace for this to work:

EDIT: The example now includes filtering by a column on one joined table, while selecting an element from another. I also removed the investments parameter from the method, as you don't actually need to pass that in. You are just accessing the EF tables directly in the method (which I substitute for _performance and _investments).

    public static IQueryable<Investment> PerformanceSearch(Expression<Func<Performance, double>> searchColumn, double minValue, double maxValue) {

        // LINQ Expression that represents the column passed in searchColumn
        // x.Return1Month
        MemberExpression columnExpression = searchColumn.Body as MemberExpression;

        // LINQ Expression to represent the parameter of the lambda you pass in
        // x
        ParameterExpression parameterExpression = (ParameterExpression)columnExpression.Expression;

        // Expressions to represent min and max values
        Expression minValueExpression = Expression.Constant(minValue);
        Expression maxValueExpression = Expression.Constant(maxValue);

        // Expressions to represent the boolean operators
        // x.Return1Month >= minValue
        Expression minComparisonExpression = Expression.GreaterThanOrEqual(columnExpression, minValueExpression);

        // x.Return1Month <= maxValue
        Expression maxComparisonExpression = Expression.LessThanOrEqual(columnExpression, maxValueExpression);

        // (x.Return1Month >= minValue) && (x.Return1Month <= maxValue)
        Expression filterExpression = Expression.AndAlso(minComparisonExpression, maxComparisonExpression);

        // x => (x.Return1Month >= minValue) && (x.Return1Month <= maxValue)
        Expression<Func<Performance, bool>> filterLambdaExpression = Expression.Lambda<Func<Performance, bool>>(filterExpression, parameterExpression);

        // use the completed expression to filter your collection
        // This requires that your collection is an IQueryable.
        // I believe that EF tables are already IQueryable, so you can probably
        // drop the .AsQueryable calls and it will still work fine.
        var query = (from i in _investments
                     join p in _performance.AsQueryable().Where(filterLambdaExpression)
                       on i.InvestmentId equals p.InvestmentId
                     select i);

        return query.AsQueryable();

    } 

You would call PerformanceSearch this way, using this simple console app as an example:

    private static IList<Investment> _investments;
    private static IList<Performance> _performance;

    static void Main(string[] args) {

        // Simulate your two Entity Framework tables
        BuildMockDataset();

        // Return1Month is on Performance, but I return IQueryable<Investment>;
        var results = PerformanceSearch(x => x.Return1Month, 300, 1000);

    }

This example is generic enough to allow you to pass a double property from Performance as searchColumn, specifying min and max values as double.

Answer 3

private static IQueryable<Investment> PerformanceSearch(IQueryable<Investment> investments, string searchColumn, double minValue, double maxValue)
{
  var entity = ExtendedEntities.Current;

  investments = from inv in entity.Investments 
                join perfromance in entity.Performances on inv.InvestmentID equals perfromance.InvestmentID
                where
                (
                    (searchColumn = "Return1Month" && perfromance.Return1Month >= minValue && perfromance.Return1Month <= maxValue) ||
                    (searchColumn = "Return2Months" && perfromance.Return2Months >= minValue && perfromance.Return2Months <= maxValue) ||
                    (searchColumn = "Return3Months" && perfromance.Return3Months >= minValue && perfromance.Return3Months <= maxValue) ||
                    (searchColumn = "Risk1Month" && perfromance.Risk1Month >= minValue && perfromance.Risk1Month <= maxValue)
                    // continue like this for as many columns, unless you want to use reflection
                )
  return investments;
}

another option is something we used for a dynamic reporting system, on the fly code-generation and compilation:

http://msdn.microsoft.com/en-us/library/microsoft.csharp.csharpcodeprovider.aspx

Answer 4

You could build a dictionary containing your strongly-typed where clauses like so:

var wheres = new Dictionary<string, Expression<Func<Performance, bool>>>()
{
    { "Return1Month", p => p.Return1Month >= minValue && p.Return1Month <= minValue },
    { "Return2Months", p => p.Return2Months >= minValue && p.Return2Months <= minValue },
    { "Return3Months", p => p.Return3Months >= minValue && p.Return3Months <= minValue },
    { "Risk1Month", p => p.Risk1Month >= minValue && p.Risk1Month <= minValue },
    { "TrackingError1Month", p => p.TrackingError1Month >= minValue && p.TrackingError1Month <= minValue },
    /* etc */
};

The complete method would look like this:

private static IQueryable<Investment> PerformanceSearch(IQueryable<Investment> investments, string searchColumn, double minValue, double maxValue)
{
    var entity = ExtendedEntities.Current;

    var wheres = new Dictionary<string, Expression<Func<Performance, bool>>>()
    {
        { "Return1Month", p => p.Return1Month >= minValue && p.Return1Month <= minValue },
        { "Return2Months", p => p.Return2Months >= minValue && p.Return2Months <= minValue },
        { "Return3Months", p => p.Return3Months >= minValue && p.Return3Months <= minValue },
        { "Risk1Month", p => p.Risk1Month >= minValue && p.Risk1Month <= minValue },
        { "TrackingError1Month", p => p.TrackingError1Month >= minValue && p.TrackingError1Month <= minValue },
        /* etc */
    };

    var investments = (
        from inv in entity.Investments 
        join perfromance in entity.Performances.Where(wheres[searchColumn]) on inv.InvestmentID equals perfromance.InvestmentID
        select inv;

    return investments;
}

Building the dictionary for each call is blazingly fast compared to the actual database call so don't worry too much about it. If you do decide to worry then make the dictionary a static private field.