i new ef. have table list of projects. have found query in software finds projects .
public project[] findall() { var projects = new list<project>(); using (var db = new projetdbconext()) { var qprojects = project in db.projectset project.createdatetime != null select project; projects = qprojects.tolist(); } return projects.toarray(); } this seems fine , not sure how parametrize it. need because implementing search feature trying re use query logic ef.
this takes list of tuples . each tuple has attribute , list of search terms.
eg. tuple(firstname , { prasaanth ,bill } ; tuple( lastname , { neelakandan , gates } ;
this means need write select query search projects firstname prasaanth or bill . if list has 1 term.
eg. tuple( company , { microsoft} ; need search 1 condition in query.
public project[] loadsearchprojects(list<system.tuple<string, list<string>>> searchterms) { var projects = new list<project>(); using (var db = new projetdbconext()) { foreach (system.tuple<string, list<string>> pair in searchterms) { string attribute = pair.item1; list<string> terms = pair.item2; /// logic here } } return projects.toarray(); } i can write if condition :
if(attribute.equals("firstname"){ // query project.firstname in conditon } but have many attributes search on.
i know ado.net way of doing :
mycommands = new sqlcommand(" select projects from persons '"+attibute+"' = search terms ... i don't know how in ef query.
1 ) there way ef allows me search on dynamic attributes ? or parametrize using '"+attribute+"' ??
2) there better data structure use simplify structure instead of using list<tuple<string, list<string>> ?
3) recommend use 3rd party linqkit or dynamic linq not sure how integrate ef querying.
my apologies if of sounds collegeboy code. please let me know if additional details needed.
regards, prasaanth
update : working method per andriy's answer. question here doesnt work if particular entry in database name null.
private static expression<func<tentity, bool>> buildstringfilter<tentity, tprop>( tuple<string, list<string>> filter) { // entity project table var entity = expression.parameter(typeof (tentity)); var prop = expression.property(entity, filter.item1); //check if contains returns true var body = filter.item2 .select(v => expression.equal(expression.call(prop, typeof (string).getmethod("contains"), new expression[] { expression.constant(v) }), expression.constant(true))) .aggregate(expression.or); var result = (expression<func<tentity, bool>>) expression.lambda(body, entity); return result; } any way can modify expression contains method :
prop, typeof (string).getmethod("contains"), new expression[] { expression.constant(v)
works if value of attribute (prop) null ?
you can build filter expression using snippet:
public static expression<func<tentity, bool>> buildfilter<tentity, tprop>( keyvaluepair<string, ienumerable<tprop>> filter) { var entity = expression.parameter(typeof(tentity)); var prop = expression.property(entity, filter.key); var body = filter.value .select(v => expression.equal(prop, expression.constant(v))) .aggregate((curr, next) => expression.or(curr, next)); var result = (expression<func<tentity, bool>>)expression.lambda(body, entity); return result; } and call like:
var filter = new keyvaluepair<string, ienumerable<string>> ( "firstname", new [] {"alice", "bob"} ); var predicate = buildfilter<item, string>(filter); var result = ctx.items.where(predicate); also, see how to: use expression trees build dynamic queries.
Comments
Post a Comment