I have this Linq expression
var searchDate = DateTime.ParseExact("17.12.2018", "dd.MM.yyyy",
CultureInfo.InvariantCulture,
DateTimeStyles.None);
result = result.Where(DatePredicate("LaunchWeekStartDate", searchDate, 3));
and I call this expression
public static Expression<Func<General, bool>> DatePredicate(string columnName,
DateTime
searchValue,
int? operatorId)
{
var type = typeof(General);
var x = Expression.Parameter(type, "x");
var member = Expression.Property(x, columnName);
Expression expression;
var constant = Expression.Constant(searchValue, typeof(DateTime));
// Greater Than >
if (operatorId == 2)
{
expression = Expression.GreaterThan(member, constant); // THIS THROW ERROR
return Expression.Lambda<Func<General, bool>>(expression, x);
}
// Less Than <
else if (operatorId == 4)
{
expression = Expression.LessThan(member, constant); // THIS THROW ERROR
return Expression.Lambda<Func<General, bool>>(expression, x);
}
// Equal
var column = type.GetProperties().FirstOrDefault(p => p.Name == columnName);
expression = column == null
? expression = Expression.Constant(true) // THIS WORKS
: expression = Expression.Equal(member, constant); // THIS THROW ERROR
return Expression.Lambda<Func<General, bool>>(expression, x);
}
The error that I am getting is
System.Data.SqlClient.SqlException (0x80131904): Conversion failed when converting date and/or time from character string.
This works
var searchDate = DateTime.ParseExact("17.12.2018", "dd.MM.yyyy",
CultureInfo.InvariantCulture,
DateTimeStyles.None);
result = result.Where(x => x.LaunchWeekStartDate == searchDate);
The database column and class property of LaunchWeekStartDate is of type DateTime.
public class General
{
[Key]
public Int64 RowNumber {get; set;}
// ...
public DateTime LaunchWeekStartDate { get; set; }
}
Please, can you explain what is the issue here ?
Edit:
Instead of Express.Equal
I am trying to use Express.Call
with CompareTo
method.
var member = Expression.Property(x, columnName);
MethodInfo method = typeof(DateTime).GetMethod("CompareTo", new[] { typeof(DateTime) });
constant = Expression.Constant(searchValue, typeof(DateTime));
var call = Expression.Call(member, method, constant);
return Expression.Lambda<Func<General, bool>>(call, x);
But I am getting an exception that I don't understand:
System.ArgumentException: Expression of type 'System.Int32' cannot be used for return type 'System.Boolean'
Check out my answer on this post here
The solution is how to make EntityFramework generate an SqlCommand with parameters so that Data Type Mapping can convert the CLR DateTime to SQL Server equivalent.
this works, i copied it from this 9 years old blog https://www.tabsoverspaces.com/231060-comparing-date-only-in-ef
expression = Expression.And(
Expression.Equal(
Expression.MakeMemberAccess(member, typeof(DateTime).GetMember("Day").Single()),
Expression.Constant(searchValue.Day)
),
Expression.And(
Expression.Equal(
Expression.MakeMemberAccess(member, typeof(DateTime).GetMember("Month").Single()),
Expression.Constant(searchValue.Month)
),
Expression.Equal(
Expression.MakeMemberAccess(member, typeof(DateTime).GetMember("Year").Single()),
Expression.Constant(searchValue.Year)
)
)
);
but it seems to me little bit complicated for such task.
Also i dont know, how to make GreaterThan
and LessThen
with this approach :/
Edit : Now it works also for Greater than and Less Than
// Greater Than >
expression = Expression.GreaterThan(Expression.MakeMemberAccess(member, typeof(DateTime).GetMember("Date").Single()), constant);
// Less Than <
expression = Expression.LessThan(Expression.MakeMemberAccess(member, typeof(DateTime).GetMember("Date").Single()), constant);
// Equal
expression = Expression.Equal(Expression.MakeMemberAccess(member, typeof(DateTime).GetMember("Date").Single()), constant);
User contributions licensed under CC BY-SA 3.0