2

I have a generic class where I need to convert constructor parameters into DateTime?:

var startDate = CompareValueFirst == null ? (DateTime?)null : Convert.ToDateTime(CompareValueFirst);
var endDate = CompareValueSecond == null ? (DateTime?)null : Convert.ToDateTime(CompareValueSecond);

Then I make a Linq Expression like this:

var startDateRight = startDate != null ? Expression.Constant(new DateTime(startDate.Value.Year, startDate.Value.Month, startDate.Value.Day, 0, 0, 0), typeof(DateTime?)) : Expression.Constant(startDate);
var endDateRight = endDate != null ? Expression.Constant(new DateTime(endDate.Value.Year, endDate.Value.Month, endDate.Value.Day, 23, 59, 59), typeof(DateTime?)) : Expression.Constant(endDate);

(which is probably not yet the most optimized form of code, but that's not the point)

and on the basis of that datetimes I make an expression:

var parameter = Expression.Parameter(typeof(T), "x");
var left = propertyName.Split('.').Aggregate((Expression)parameter, Expression.Property);
var expression = Expression.AndAlso(
Expression.MakeBinary(ExpressionType.GreaterThanOrEqual, left, startDateRight),
Expression.MakeBinary(ExpressionType.LessThanOrEqual, left, endDateRight));

which finally is used for result expression creation:

Expression<Func<T, bool>> ExpressionResult;
ExpressionResult = Expression.Lambda<Func<T, bool>>(expression, parameter);
var results = list.Where(ExpressionResult);

on DB I get an error:

Conversion failed when converting date and/or time from character string.

because format of the resulting datetime in query is:

'2022-08-25T00:00:00.0000000'

but should be:

'2022-08-25 00:00:00'

Question: how to take control over that resulting datetime format and set it appropriately? (if some parts of code above are not consistent sorry for that, I have my code divided into many methods and had to glue it together into a more or less coherent whole)

Soner Gönül
  • 97,193
  • 102
  • 206
  • 364
Kentucker
  • 33
  • 4
  • What is the type of `left`? Is it `DateTime` or `DateTime?`? You may be running into an old bug in EF Core caused by it assuming column types are datetime2? – NetMage Aug 25 '22 at 20:15
  • I think if you add a `Expression.Convert(Expression.Constant(...), typeof(DateTimeOffset))` you can work around the issue. – NetMage Aug 25 '22 at 20:40
  • `left` is `Linq.Expression` – Kentucker Aug 25 '22 at 20:40
  • What is the static type of the `Expression` in `left`? (e.g. the value of `left.Type`) – NetMage Aug 25 '22 at 20:54
  • From what I can tell, the SQL Server `datetime` type only supports up to 3 digits of precision on the seconds. – NetMage Aug 25 '22 at 20:57
  • @NetMage I see, so the type of `Expression` in `left` is `Nullable DateTime` and generally speaking that's the type that I thought of to base on. And yes, I agree about that 3 digits of precision - for me it doesn't matter, can be 3 digits of precision on the seconds, can be 0, just to handle that format somehow. As for that suggestion with `Expression.Convert` - where in the code you think should I use it? And do you think is there an option to format `DateTime?` instead of convertion? – Kentucker Aug 25 '22 at 21:12
  • I assume your SQL Server column is of type `datetime` and not `datetime2` which is the recommended SQL Server type and the assumed EF Core type. If you override your column database type on `OnModelCreating` with `HasColumnType("datetime")` that should fix the issue assuming you can't just change the SQL database to `datetime2`, – NetMage Aug 25 '22 at 21:28

0 Answers0