score:4

Accepted answer

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.

score:1

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

score:1

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.

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

Related Query

More Query from same tag