Trancate time in expression treee RSS

• • Reply
  sabbirspider

  Member

  27 Points

  163 Posts

  Trancate time in expression treee

  Oct 10, 2011 09:19 PM|sabbirspider|LINK

  <div> <div>

  I am making a dynamic search by linq expression tree.I am problem with date time field.my partial source code ...

  ....

  ParameterExpression paramExpression = Expression.Parameter(typeof(ExpenseCategory), "x");
  Expression left = Expression.Property(paramExpression, typeof(ExpenseCategory).GetProperty("CreateDate"));
  Expression right = Expression.Constant(dToDate.Date, typeof(DateTime));
  Expression result3 = Expression.LessThanOrEqual(left, right);

  if dToDate(Input date by user)=12/31/2011:12:00:00AM and if CreateDate in datebase contain time like 12/31/2011:15:08:00AM,12/31/2011:16:08:00AM etc i don't any resut for the time.How can i remove time in expression.i added the line on the above after line 2

  left = Expression.Property(paramExpression, typeof(DateTime).GetProperty("Date"));i get the following error

  Property 'System.DateTime Date' is not defined for type 'Inventory.Models.ExpenseCategory'.

  How can i format CreateDate property in expression tree by trancating the time?please help

  </div> </div>
  sabbir
• • Reply
  Decker Dong...

  All-Star

  94120 Points

  18123 Posts

  Re: Trancate time in expression treee

  Oct 13, 2011 11:11 PM|Decker Dong - MSFT|LINK

  Hello:)

  Having thought this problem for a few days....Hummmm……In my mind I think to directly achieve it is hard... But we can do the tricks like this:

  1) Take out the DateTime String and use Parse method to put convert it from a string to DateTime.

  2) Then use the constructor to fill it with Year, Month and Day.

  The following codes are:

   // Suppose "12/31/2011 12:00:00AM" are value taken out from the db's table

   DateTime dt1 = DateTime.Parse("12/31/2011 12:00:00AM");     

  // This will make Year=Month=Day=0, all are equal, so you can compare it         

   dt1 = new DateTime(dt1.Year, dt1.Month, dt1.Day);       

• • Reply
  texx

  Participant

  1513 Points

  414 Posts

  Re: Trancate time in expression treee

  Oct 14, 2011 10:53 AM|texx|LINK

  If you are looking for all ExpenseCategorys created on or before the date input by the users, what about just making the time portion of the dToDate 23:59:59.

  Easy way I have used is something like

  DateTime dToDate = DateTime.Parse(txtDateTimeInput.Text);

  dToDate = dToDate.Today.AddDays(1).AddSeconds(-1);

  dToDate.Today returns only the date with a time of 12:00:00am, then add 1 day, and subtract one second. That should make dToDate now the date the user selected with a time of 11:59:59 pm.

  Then your query should show the proper results ?  Or are you just wanting all ExpenseCategorys that were created on the input date, no matter the time ? If that is the case, I think you will have to build two expressions with a greaterthanorequalto midnight, and a lessthanorequalto 11:59:59 pm.

  Robin
• • Reply
  sabbirspider

  Member

  27 Points

  163 Posts

  Re: Trancate time in expression treee

  Oct 14, 2011 01:42 PM|sabbirspider|LINK

  Many many thanks for your reply.I was in frastation because no one is answering my question.i have posted this three times.I know ur solution but how can it possible in ExpressionTree,I think it is not possible in your way in ExpressionTree.

  What i want to do is to call EntityFunctions.TruncateTime to trancate time..

               

  1. Type[] typeArray = new Type[1];

  2.  typeArray.SetValue(typeof(DateTime?), 0);

  3.  DateTime dToDate = DateTime.Parse(collection["cboSearchToDate"].Trim(), provider);

  4.  ParameterExpression left = Expression.Parameter(typeof(ExpenseCategory), "x");

  5.  left = Expression.Property(paramExpression, typeof(ExpenseCategory).GetProperty("CreateDate"));

  6.  MethodInfo methodInfo=typeof(EntityFunctions).GetMethod("TruncateTime", typeArray);

  7.  left = Expression.Call(methodInfo, left);

  8.  right = Expression.Constant(dToDate.Date, typeof(DateTime)); 

  9.  Expression result3 = Expression.LessThanOrEqual(left, right);

       But while executing line 7 it show me the following error..

  Expression of type 'System.DateTime' cannot be used for parameter of type 'System.Nullable`1[System.DateTime]' of method 'System.Nullable`1[System.DateTime] TruncateTime(System.Nullable`1[System.DateTime])'

  How can i execute EntityFunctions.TruncateTime method call in Expression three.Please let me know.I think it can solve my problem.

  sabbir
• • Reply
  zeekb

  None

  0 Points

  1 Post

  Re: Trancate time in expression treee

  Jul 04, 2012 08:33 PM|zeekb|LINK

  Hi Sabir,

  If you can't beat them, join them. Just make your datetime columns nullable and put the not null logic in your business and presentation layer. I know that it's not the most elegant solution in the world, but you will have the TruncateTime method working; that's for sure.

  Good Luck!

• • Reply
  chris03

  None

  0 Points

  1 Post

  Re: Trancate time in expression treee

  Aug 15, 2013 11:45 AM|chris03|LINK

  .

• • Reply
  koen.jans

  None

  0 Points

  1 Post

  Re: Trancate time in expression treee

  Mar 30, 2015 03:53 AM|koen.jans|LINK

  For anyone facing this issue;
  To make this work for both DateTime? / DateTime (and DateTimeOffset? / DateTimeOffset), Convert the expression to nullable DateTime/DateTimeOffset first:

  // Note that the "TruncateTime" method only has overloads for DateTime? and DateTimeOffset?
  // If the propertyExpression is not nullable, make it nullable to match the types for the expression.
  if(left.Type == typeof(DateTime) || left.Type == typeof(DateTimeOffset))
     left = Expression.Convert(left, typeof(Nullable<>).MakeGenericType(left.Type));
  var methodInfo = typeof(DbFunctions).GetMethod("TruncateTime", new[]{ left.Type });
  left = Expression.Call(methodInfo, left);
  var result = Expression.LessThanOrEqual(left, right);

  Regards,

  Koen