C# LinqToSql build dynamic where clause

Question

I have a project where i have some dynamically builded DataGrids (winform) that are generated according to some configuration inside some xml files in a directory. In fact, there is one xml for each DataGrid (the file has the same name of the DataGrid) and this xml contains information about DataGrid's columns and database table on which the grid should be binded to.

With the same information above, a panel (for each grid) is generated, and it contains the fields needed to filter each columns.

At moment i have applied to my project the example described at this link : http://www.codeproject.com/Tips/582450/Build-Where-Clause-Dynamically-in-Linq

to dynamically filter my table using a dynamic where (linq) clause onto table datasource (datasource is simply a select all over the underlying table).

What i would achieve is an optimizations, because if there are a lot of data, the db extraction could be expensive. So i would build a dynamic linq to sql where clause instead of a dynamic linq query over a list with all table entry.

How can i do that?

Thanks in advance.

possible duplicate of [Dynamically generate LINQ queries](http://stackoverflow.com/questions/9505189/dynamically-generate-linq-queries) — Gert Arnold, Oct 29 '14 at 14:32
The link you refer to already generates an `Expression` which can be applied to a `DbSet` or L2S `Table` using a `.Where(expression)`. Why can't you apply it directly to the database instead of your datasource? — Maarten, Oct 29 '14 at 14:34
If you're using Linq2Sql then your query returns an IQueryable object (which, for simplicity sake, is just a wrapper around a SqlCommand). If you apply a Where to the IQueryable that gets transormed into a WHERE Sql statement. This seems to solve your problem, or maybe I didn't understand your concern in the first place. — , Oct 29 '14 at 14:46
@Sphaso I can not use syntax like that : var query = some_linq and then query.Where ( some_contraint ) ; because i do not know the field's name into table on wich apply where clause and the operator that is needed. — Skary, Oct 29 '14 at 15:40
@Maarten i have try a dummy table Person and to use the code in the example to filter table's field "Name" with some value (eg: "a") using operator Op.Contains but does not work. If i watch underlying T-SQL it seems that dynamic filter take no effect. — Skary, Oct 29 '14 at 15:45
@Skary Dynamic LINQ will fix that for you, but this is probably more of a design problem than technology. — , Oct 30 '14 at 16:45
@Sphaso you right with Dynamic Linq it seem to work. My only problem now is how to use .Contains("value") over table's fields that are of type of string. Eg : string dynamicFieldName = "Name" query.Where ( dynamicFieldName + ".Contains(\"Value\")" ); — Skary, Oct 31 '14 at 10:38
I don't understand what's the problem with that where clause. Can you update your question? — , Nov 01 '14 at 11:56

score 0 · Accepted Answer · edited May 23 '17 at 11:57

1) On chaining: Have a look at this answer. It contains some nifty extension methods for predicates. Complete code there, no use duplicating. Case study: Northwind model with the Employee class. The final filter will contain employees from Seattle or WA region and will have an identifier greater than 10. Relevant here, only the base query is executed remotely (a SELECT * FROM Employees), the LINQ to SQL filtering runs on the local cache.

Predicate<Employee> p1 = emp => emp.City == "Seattle";
Predicate<Employee> p2 = emp => emp.Region == "WA";
Predicate<Employee> p3 = emp => emp.EmployeeID > 10;
Predicate<Employee> orp1p1Andp3 = PredicateExtensions.OrAll(new[] { p1, p2}).And(p3);
//identical with
Predicate<Employee> orp1p1Andp3 = p1.Or(p2).And(p3);
//
var query = from emp in this.ctx.Employees select emp;
Func<Employee,bool> selector = emp=>orp1p1Andp3(emp);
dataGrid.ItemsSource = query.Where(selector);

2) On performance: Imho, it's the disconnected vs connected paradigm (DataSet vs DataReader) existing with ADO.NET. You should measure the impact of remote vs local query execution and compiled queries (btw, dynamic filtering you are so keen on will be reduced to Enumerable.Union and Enumerable.Intersect operations for compiled queries). Benchmarking will show you the most appropriate solution for your environment.

C# LinqToSql build dynamic where clause

1 Answers1