LINQ2SQL Expression - Conversion failed when converting date

0

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'

c#
lambda
linq-to-sql
asked on Stack Overflow Oct 29, 2018 by Muflix • edited Oct 30, 2018 by Lewis86

2 Answers

2

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.

answered on Stack Overflow May 7, 2021 by Wizzyno
0

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);
answered on Stack Overflow Oct 30, 2018 by Muflix • edited Oct 30, 2018 by Muflix

User contributions licensed under CC BY-SA 3.0