How to write LINQ query with column name as parameter still in a type safe way

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.

Draughts

Answer 1

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.

Mechanistic

Hi Mike, thanks for your very detailed response. Yes, expressions look pretty good in this case but actual search columns are in Performance table rather Investment table. Not sure how can I these expressions on Performance table. – Draughts

My mistake, give me a few minutes. – Mechanistic

I've edited my code example to handle filtering a column from one table in a join, while selecting out an element from the other. – Mechanistic

Actually, after realizing that the compilation of the lambda may cause you problems in EF, I rewrote the example slightly differently so that you can use the uncompiled expression. It's a little akward, but this should work for you! – Mechanistic

Answer 2

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);

Butler

Thanks for your concrete answer dbaseman, this is exactly what I was looking for. Implemented the changes straight away but then hit this problem "The LINQ expression node type 'Invoke' is not supported in LINQ to Entities". Looked a few places for this problem and answer comes down to using predicates which defeates the purpose of passing SearchColumn as parameter. Any idea how to fix this? – Draughts

Thanks dbaseman, your code was equally good to be marked as "Answer" but unfortunately Lambda doesn't go really well with Linq to Entities. – Draughts

@Rahul no worries, I learned something valuable from your question and Mike C's answer, so thanks. – Butler

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

Rory

Thanks Abhinav, I liked your approach and this is what I do in my SQL queries, but cannot use hardcoded strings in this case. – Draughts

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.

Robedechambre

Wow that's interesting way of doing it! and definitely better than what I suggested :P – Rory

Thanks Enigmativity, looks a cool solution but the actual search columns are in Performances table. So in this case not sure if investments.Where(wheres[searchColumn]) will actually work? – Draughts