CODEWITHSUNDEEP
c#linqlinq-to-sql
lesyriad
lesyriad

Reputation: 115

Linq-to-sql user generated predicate

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)

Upvotes: 3

Views: 325

Answers (1)

Maksim Simkin
Maksim Simkin

Reputation: 9679

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

Upvotes: 1

Related Questions