How to compare only date part with linq expression? -
i want make column filter grid view. simple want filter grid view column stuff. here have created 1 iqueryable returns queryable result.
here code :
------------------------------------------updated------------------------------------------------
public static iqueryable<t> filterforcolumn<t>(this iqueryable<t> queryable, string colname, string searchtext) { if (colname != null && searchtext != null) { var parameter = expression.parameter(typeof(t), "m"); var propertyexpression = expression.property(parameter, colname); system.linq.expressions.constantexpression searchexpression = null; system.reflection.methodinfo containsmethod = null; system.linq.expressions.methodcallexpression body = null; system.nullable<datetime> nextdate = null; expression ex1 = null; expression ex2 = null; switch (colname) { case "jobid": case "filesize": case "totalfilesize": int64? size = convert.toint64(searchtext); searchexpression = expression.constant(searchtext); containsmethod = typeof(int64?).getmethod("equals", new[] { typeof(int64?) }); body = expression.call(propertyexpression, containsmethod, searchexpression); break; case "publishdate": case "birth_date": case "anniversary_date": case "profile_updated_datetime": case "completedon": datetime? currentdate = datetime.parseexact(searchtext, "dd/mm/yyyy", null); nextdate = currentdate.value.adddays(1); ex1 = expression.greaterthanorequal(propertyexpression, expression.constant(currentdate)); ex2 = expression.lessthan(propertyexpression, expression.constant(nextdate)); body = expression.andalso(ex1, ex2); break; case "created_datetime": case "reminder_date": case "news_date": case "thought_date": case "subscriptiondatetime": case "register_datetime": case "createdon": datetime dt1 = datetime.parseexact(searchtext, "dd/mm/yyyy", null); nextdate = currentdate.value.adddays(1); ex1 = expression.greaterthanorequal(propertyexpression, expression.constant(currentdate)); ex2 = expression.lessthan(propertyexpression, expression.constant(nextdate)); body = expression.andalso(ex1, ex2); break; default : searchexpression = expression.constant(searchtext); containsmethod = typeof(string).getmethod("contains", new[] { typeof(string) }); body = expression.call(propertyexpression, containsmethod, searchexpression); break; } var predicate = expression.lambda<func<t, bool>>(body, new[] { parameter }); return queryable.where(predicate); } else { return queryable; } } here solution gives compile time error :
error 9 cannot implicitly convert type 'system.linq.expressions.binaryexpression' 'system.linq.expressions.methodcallexpression' f:\easyweb\app_code\helper.cs 47 28 f:\easyweb\ here @ case of datetime want filter datetime column date not whole datetime value.
here put required things :
select [t8].[id], [t8].[title], [t8].[value] [publisher], [t8].[value2] [touser], [t8].[sent_datetime] [publishdate], [t8].[isfileattached] [isfile], [t8].[category_name] [categoryname], [t8].[value3] [status_name], [t8].[value4] [group_name], [t8].[totalfilesize] [filesize] ( select [t0].[id], [t0].[title], ( select top (1) [t3].[value] ( select ([t2].[first_name] + ' ') + [t2].[last_name] [value], [t2].[id] [dbo].[tbl_user_master] [t2] ) [t3] [t3].[id] = [t0].[user_id] ) [value], ( select top (1) [t5].[value] ( select ([t4].[first_name] + ' ') + [t4].[last_name] [value], [t4].[id] [dbo].[tbl_user_master] [t4] ) [t5] ([t5].[id]) = [t0].[touser_id] ) [value2], [t0].[sent_datetime], [t0].[isfileattached], [t1].[category_name], ( select top (1) [t6].[status_name] [dbo].[tbl_status_master] [t6] ([t6].[id]) = [t0].[status_id] ) [value3], ( select top (1) [t7].[group_name] [dbo].[tbl_group_master] [t7] ([t7].[id]) = [t0].[group_id] ) [value4], [t0].[totalfilesize], [t0].[touser_id], [t0].[user_id] [dbo].[tbl_post_history] [t0] inner join [dbo].[tbl_category_master] [t1] on [t0].[category_id] = [t1].[id] ) [t8] (cast(convert(char(10), [t8].[sent_datetime], 102) datetime) = '12/24/2013' ) , (([t8].[touser_id] = 3) or ([t8].[touser_id] null)) , ([t8].[user_id] <> 3) order [t8].[sent_datetime] desc here shows required output or logic queryable.
but, here 1 drawback occurs while method equals check whole date time. possible method force check date part column?
------------------------------------2-updated-----------------------------------------
for overwhelming problem use technique :
public static iqueryable<t> filterforcolumn<t>(this iqueryable<t> queryable, string colname, string searchtext) { if (colname != null && searchtext != null) { var parameter = expression.parameter(typeof(t), "m"); var propertyexpression = expression.property(parameter, colname); system.linq.expressions.constantexpression searchexpression = null; system.reflection.methodinfo containsmethod = null; system.linq.expressions.binaryexpression body = null; datetime? currentdate = null; datetime? nextdate = null; expression ex1 = null; expression ex2 = null; switch (colname) { case "jobid": case "filesize": case "totalfilesize": int64? size = convert.toint64(searchtext); searchexpression = expression.constant(searchtext); containsmethod = typeof(int64?).getmethod("equals", new[] { typeof(int64?) }); //body = expression.call(propertyexpression, containsmethod, searchexpression); break; case "publishdate": case "birth_date": case "anniversary_date": case "profile_updated_datetime": case "completedon": currentdate = datetime.parseexact(searchtext, "dd/mm/yyyy", null); nextdate = currentdate.value.adddays(1); ex1 = expression.greaterthanorequal(propertyexpression, expression.constant(currentdate)); ex2 = expression.lessthan(propertyexpression, expression.constant(nextdate)); body = expression.andalso(ex1, ex2); break; case "created_datetime": case "reminder_date": case "news_date": case "thought_date": case "subscriptiondatetime": case "register_datetime": case "createdon": currentdate = datetime.parseexact(searchtext, "dd/mm/yyyy", null); nextdate = currentdate.value.adddays(1); ex1 = expression.greaterthanorequal(propertyexpression, expression.constant(currentdate)); ex2 = expression.lessthan(propertyexpression, expression.constant(nextdate)); body = expression.andalso(ex1, ex2); break; default : searchexpression = expression.constant(searchtext); containsmethod = typeof(string).getmethod("contains", new[] { typeof(string) }); //body = expression.call(propertyexpression, containsmethod, searchexpression); break; } var predicate = expression.lambda<func<t, bool>>(body, new[] { parameter }); return queryable.where(predicate); } else { return queryable; } } it's give me run time error :
server error in '/easyweb' application. binary operator greaterthanorequal not defined types 'system.nullable`1[system.datetime]' , 'system.datetime'. description: unhandled exception occurred during execution of current web request. please review stack trace more information error , originated in code. exception details: system.invalidoperationexception: binary operator greaterthanorequal not defined types 'system.nullable`1[system.datetime]' , 'system.datetime'. source error: line 44: currentdate = datetime.parseexact(searchtext, "dd/mm/yyyy", null); line 45: nextdate = currentdate.value.adddays(1); line 46: ex1 = expression.greaterthanorequal(propertyexpression, expression.constant(currentdate)); line 47: ex2 = expression.lessthan(propertyexpression, expression.constant(nextdate)); line 48: body = expression.andalso(ex1, ex2); source file: f:\easyweb\app_code\helper.cs line: 46 stack trace: [invalidoperationexception: binary operator greaterthanorequal not defined types 'system.nullable`1[system.datetime]' , 'system.datetime'.] system.linq.expressions.expression.getuserdefinedbinaryoperatororthrow(expressiontype binarytype, string name, expression left, expression right, boolean lifttonull) +752213 system.linq.expressions.expression.getcomparisonoperator(expressiontype binarytype, string opname, expression left, expression right, boolean lifttonull) +221 system.linq.expressions.expression.greaterthanorequal(expression left, expression right, boolean lifttonull, methodinfo method) +53 system.linq.expressions.expression.greaterthanorequal(expression left, expression right) +8 helper.filterforcolumn(iqueryable`1 queryable, string colname, string searchtext) in f:\easyweb\app_code\helper.cs:46 admin_post_history.fillgrid(string commandname, string columnname, string searchtext) in f:\easyweb\admin\post_history.aspx.cs:79 admin_post_history.btnsearch_click(object sender, eventargs e) in f:\easyweb\admin\post_history.aspx.cs:2375 system.web.ui.webcontrols.button.onclick(eventargs e) +111 system.web.ui.webcontrols.button.raisepostbackevent(string eventargument) +110 system.web.ui.webcontrols.button.system.web.ui.ipostbackeventhandler.raisepostbackevent(string eventargument) +10 system.web.ui.page.raisepostbackevent(ipostbackeventhandler sourcecontrol, string eventargument) +13 system.web.ui.page.raisepostbackevent(namevaluecollection postdata) +36 system.web.ui.page.processrequestmain(boolean includestagesbeforeasyncpoint, boolean includestagesafterasyncpoint) +1565 version information: microsoft .net framework version:2.0.50727.3053; asp.net version:2.0.50727.3053 -------------------------------- 3 update-------------------------------------------
here i'm querying :
case "inbox": lbl_disply.text = "inbox"; lbut_showinbox.font.bold = true; lbut_showoutbox.font.bold = false; lbut_showdraffs.font.bold = false; lbut_showtrash.font.bold = false; var query1 = db.posts.where(p => (p.touser_id.equals(user_id) || p.touser_id.equals(null)) && p.user_id != user_id).orderbydescending(p=>p.sent_datetime).select(p => new { id = p.id, title = p.title, publisher = db.users.where(u => u.id.equals(p.user_id)).select(u => u.first_name + ' ' + u.last_name).firstordefault(), touser = db.users.where(u => u.id.equals(p.touser_id)).select(u => u.first_name + ' ' + u.last_name).firstordefault(), publishdate = p.sent_datetime, isfile = p.isfileattached, categoryname = db.categories.where(c => c.id.equals(p.category_id)).select(c => c.category_name).firstordefault(), status_name = db.status.where(s => s.id.equals(p.status_id)).select(s => s.status_name).firstordefault(), group_name = db.groups.where(g => g.id.equals(p.group_id)).select(g => g.group_name).firstordefault(), filesize = p.totalfilesize }).filterforcolumn(columnname, searchtext).tolist(); at grid view filling
dataset mydataset = new dataset(); datatable dt = new datatable(); dt.columns.add(new datacolumn("id", typeof(int))); dt.columns.add(new datacolumn("isread", typeof(bool))); dt.columns.add(new datacolumn("isimp", typeof(bool))); dt.columns.add(new datacolumn("title", typeof(string))); dt.columns.add(new datacolumn("publisher", typeof(string))); dt.columns.add(new datacolumn("touser", typeof(string))); dt.columns.add(new datacolumn("publishdate", typeof(datetime?))); dt.columns.add(new datacolumn("isfile", typeof(bool))); dt.columns.add(new datacolumn("categoryname", typeof(string))); dt.columns.add(new datacolumn("status_name", typeof(string))); dt.columns.add(new datacolumn("group_name", typeof(string))); dt.columns.add(new datacolumn("filesize", typeof(string))); foreach (var item in query1) i declared publishdate typeof(datetime?) gives me run time error :
server error in '/easyweb' application. dataset not support system.nullable<>. description: unhandled exception occurred during execution of current web request. please review stack trace more information error , originated in code. exception details: system.notsupportedexception: dataset not support system.nullable<>. source error: line 101: dt.columns.add(new datacolumn("publisher", typeof(string))); line 102: dt.columns.add(new datacolumn("touser", typeof(string))); line 103: dt.columns.add(new datacolumn("publishdate", typeof(datetime?))); line 104: dt.columns.add(new datacolumn("isfile", typeof(bool))); line 105: dt.columns.add(new datacolumn("categoryname", typeof(string))); source file: f:\easyweb\admin\post_history.aspx.cs line: 103 stack trace: [notsupportedexception: dataset not support system.nullable<>.] system.data.datacolumn..ctor(string columnname, type datatype, string expr, mappingtype type) +4826536 system.data.datacolumn..ctor(string columnname, type datatype) +12 admin_post_history.fillgrid(string commandname, string columnname, string searchtext) in f:\easyweb\admin\post_history.aspx.cs:103 admin_post_history.page_load(object sender, eventargs e) in f:\easyweb\admin\post_history.aspx.cs:59 system.web.util.callihelper.eventargfunctioncaller(intptr fp, object o, object t, eventargs e) +14 system.web.util.callieventhandlerdelegateproxy.callback(object sender, eventargs e) +35 system.web.ui.control.onload(eventargs e) +99 system.web.ui.control.loadrecursive() +50 system.web.ui.page.processrequestmain(boolean includestagesbeforeasyncpoint, boolean includestagesafterasyncpoint) +627 version information: microsoft .net framework version:2.0.50727.3053; asp.net version:2.0.50727.3053 ---------------------------------4 update----------------------------------------
i check dbml file , screen shot verify it's nullable
to achieve want can use predicate
where sent_datetime >= '20131224' , sent_datetime < '20131225' so can use expression
datetime currentdate = datetime.parseexact("06/01/2008", "dd/mm/yyyy", null); datetime nextdate = currentdate.adddays(1); expression ex1 = expression.greaterthanorequal( propertyexpression, expression.constant(currentdate)); expression ex2 = expression.lessthan( propertyexpression, expression.constant(nextdate)); expression body = expression.andalso(ex1, ex2); var predicate = expression.lambda<func<t, bool>>(body, new[] { parameter }); of course, here have sargable predicate.
update
i've created complete example you:
public static iqueryable<t> filterforcolumn<t>(this iqueryable<t> queryable, string colname, string searchtext) { if (colname != null && searchtext != null) { var parameter = expression.parameter(typeof(t), "m"); var propertyexpression = expression.property(parameter, colname); system.linq.expressions.constantexpression searchexpression = null; system.reflection.methodinfo containsmethod = null; // must of type expression accept different type of expressions // i.e. binaryexpression, methodcallexpression, ... system.linq.expressions.expression body = null; expression ex1 = null; expression ex2 = null; switch (colname) { case "jobid": case "filesize": case "totalfilesize": int64? size = convert.toint64(searchtext); searchexpression = expression.constant(searchtext); containsmethod = typeof(int64?).getmethod("equals", new[] { typeof(int64?) }); body = expression.call(propertyexpression, containsmethod, searchexpression); break; // section datetime? properties case "publishdate": case "birth_date": case "anniversary_date": case "profile_updated_datetime": case "completedon": datetime currentdate = datetime.parseexact(searchtext, "dd/mm/yyyy", null); datetime nextdate = currentdate.adddays(1); ex1 = expression.greaterthanorequal(propertyexpression, expression.constant(currentdate, typeof(datetime?))); ex2 = expression.lessthan(propertyexpression, expression.constant(nextdate, typeof(datetime?))); body = expression.andalso(ex1, ex2); break; // section datetime properties case "created_datetime": case "reminder_date": case "news_date": case "thought_date": case "subscriptiondatetime": case "register_datetime": case "createdon": datetime currentdate = datetime.parseexact(searchtext, "dd/mm/yyyy", null); datetime nextdate = currentdate.adddays(1); ex1 = expression.greaterthanorequal(propertyexpression, expression.constant(currentdate)); ex2 = expression.lessthan(propertyexpression, expression.constant(nextdate)); body = expression.andalso(ex1, ex2); break; default : searchexpression = expression.constant(searchtext); containsmethod = typeof(string).getmethod("contains", new[] { typeof(string) }); body = expression.call(propertyexpression, containsmethod, searchexpression); break; } var predicate = expression.lambda<func<t, bool>>(body, new[] { parameter }); return queryable.where(predicate); } else { return queryable; } }
Comments
Post a Comment