score:4
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);
Source: stackoverflow.com
Related Query
- How to write LINQ query with column name as parameter still in a type safe way
- how to write a Linq query with a EF code first Many to Many relationship
- How To Write Distance LINQ query i have Lat, Long in table with varchar type SQL Server
- How to write join query with multiple column - LINQ
- How to write a LINQ to Entities query with List in a "WHERE" condition
- How can I write the following code more elegantly using LINQ query syntax?
- add where clauses to linq query with generic column name
- How to write this LINQ Query in a better way
- How can I issue a LINQ query to a SQL Server to check if a column value starts with a word?
- LINQ - Accessing a column with the column name as a string parameter
- How to write a generic LINQ query for filtering of data based on first name middle name and last name
- MVC Linq Query with dynamic column name in WHERE clause
- How do I write this crosstab type query in Linq (tables & data provided)
- How to write SQL SELECT INNER JOIN with multiple conditions (with LIKE) query to LINQ to SQL
- Replacing var with type name in Linq query
- What is the best way to write a two column query in LINQ to Entity 6? And save the results to two different variables?
- How to create an anonymous type within linq query with TypeScript
- How can I set custom property of an Entity type in LINQ to Entities query and still return a IQueryable<T>?
- How to Select top (5) contributors group by Business type code in c# linq query
- How to write a count and group by month query with linq
- How to write a LINQ query to select from a collection with given set of matching IDs
- How to code this LINQ query in a better way
- How to write aggregate query in LINQ reusing part of the select code
- How to "select all" in LINQ query with nullable column
- How do I write a LINQ query which will generate a JSON string with the following format?
- How to write a LINQ query or Lambda expression on one to many relation with filter
- How to write a LINQ query with inner join conditions and IN sub query
- How to query XML with the same element and attribute name using linq
- Best way to write Dynamic Query with dynamic result with Linq or Expression Tree
- how to write linq query with where clause to get records between 9 am to 5 pm
More Query from same tag
- Linq To SQL LEFT JOIN (OR Statement)
- Entity Framework Associations
- why doesn't .Except() and Intersect() work here using LINQ?
- why is this linq query return a boolean and not the first result of the select?
- Casting linq to model class fails "Unable to cast object of type 'System.Data.Entity.Infrastructure.DbQuery`1"
- How to analyse LINQ queries
- how to select records back on foreign keys
- Get most recent datetime column with LINQ
- Can I write case when query in entity framework core and linq?
- How to get all elements in collection when building an array of items?
- LINQ Distinct set by column value
- How to select hour in priority
- Call a Linq to SQL user defined function multiple times in one trip to the DB
- How to change the order of the list?
- Left Join in Entity Framework 3.5
- How to get list of the grouped value of group by LINQ extension
- Is there any real difference between "and" and "wheres" in linq
- Convert Dictionary<int, int?> to Dictionary<int, int> by skipping null values with LINQ
- Why in generated DataBaseManagerController table header is generated using lambda expression in ASP MVC 4
- How to combine 2 tables using Entity Framework 6 and Linq in an MVC Project?
- Linq left joining 3 tables
- How to assign two different entities result set into single var variable using linq to sql c#
- LEFT OUTER JOIN in Linq - How to Force
- Rationale behind renaming of higher order list operations
- Combine inner join and left join in Entity Framework query
- How to limit the number of cycles of a loop under some condition?
- 'ICollection<Table1>' does not contain a definition for Table2
- convert generic list to datatable using linq..?
- nhibernate linq basics
- How to filter datatable rows in debug mode