3

Background: I have a huge table many columns and many entries in a db. I have some preset queries that return data but I need the ability to let the user further refine the query (add more filters).

So my question is I have code as follows:

var dbquery = (from a in db.test where id > 100 select a);

This returns a iQueryable. Now I can futher filter it (and still have the query run on the db) when i do this

var dbquery2 = dbquery.Where(b => b.quanity > 20);

But what I really want is for the user to be able to input the filters and have it still run on the db. I have lookat at PropertyDescriptors but they always say there is no approriate linq to sql translation. How can I do something like this:

var dbqueryuser = dbquery.where(c => c.(user specified field) > 20);

Do I need to do custom generated SQL statements or can I somehow generate valid 

Expression<Func<test,bool>>

statements using reflection?

Note: This is building on a previous question I asked here Linq to custom sql

I basicly need to take the filter list but get the queries to run on the db (as the db returns a lot of data and I want to get as much filtered at the db side rather than user side)

Community
  • 1
  • 1
lesyriad
  • 115
  • 1
  • 8
  • Yes, you can generate `Expression>` dynamically, not with reflection but with `Expression` class methods. There are many examples on SO of doing that, if you want more concrete answer, you should post how your filter definition looks like. – Ivan Stoev Jan 02 '17 at 18:26

1 Answers1

1

You have to generate a custom Expression, just for example (it won't compile):

var param = Expression.Parameter(typeof(Entity));
var property = Expression.PropertyOrField(param, propertyName);
var greater = Expression.GreaterThan(property, Expression.Constant(20));
var lambda = (Expression<Func<Entity, bool>>)Expression.Lambda(greater, param);
Maksim Simkin
  • 9,561
  • 4
  • 36
  • 49
  • Works :) thanks How can I do that on properties that are nullable though? It says there is no greaterthan operator that can compare a Nullable with System.Int32 – lesyriad Jan 02 '17 at 18:47
  • They are not comparable, you should get property Value from nullable, or better, call method "getValueOrDefault" , or read for example hier: http://stackoverflow.com/questions/586097/compare-nullable-types-in-linq-to-sql there is a lot of stuff about expressions on SO – Maksim Simkin Jan 02 '17 at 18:49
  • Ill look at that :) thanks but I did find another solution var testnull = Expression.TypeAs(Expression.Constant(20), typeof(Nullable)); var greater = Expression.GreaterThan(property,testnull); – lesyriad Jan 02 '17 at 18:55