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
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.
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.
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..
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.
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.
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);
Member
27 Points
163 Posts
Trancate time in expression treee
Oct 10, 2011 09:19 PM|sabbirspider|LINK
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>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
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.
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..
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.
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!
None
0 Points
1 Post
Re: Trancate time in expression treee
Aug 15, 2013 11:45 AM|chris03|LINK
.
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:
Regards,
Koen