System.NotSupportedException using DateTime? in Linq to Entities

Question

I have a System.NotSupportedException using DateTime? in Linq to Entities.

My original method looks like this:

public TransportOrderLine GetLastTransportOrderLine(bool isDriver, int? driverId, int? haulierId , DateTime date, IDatabaseContext db)
{
    var lines =
        db.Query<TransportOrderLine>()
          .Where(x => ((isDriver && x.TransportOrder.DriverId == driverId) ||
                      (!isDriver && x.TransportOrder.HaulierId == haulierId)) &&
                      x.StartDatePlanned.HasValue &&
                      EntityFunctions.TruncateTime(x.StartDatePlanned) <= date)
                      .ToList();

    return lines.OrderByDescending(x => x.TransportOrder.Sequence)
                .ThenByDescending(x => x.Sequence).LastOrDefault();
}

For some reason, I'm having an exception (NotSupportedException) when linq to entities executes the DateTime part.

TransportOrderLine.StartDatePlanned is of type DateTime?.

I also split my code like this already:

var lines = db.Query<TransportOrderLine>()
              .Where(x => ((isDriver && x.TransportOrder.DriverId == driverId) ||
                         (!isDriver && x.TransportOrder.HaulierId == haulierId)))
              .ToList(); //ok


lines = lines.Where(x => x.StartDatePlanned.HasValue).ToList(); //ok

lines = lines.Where(x => EntityFunctions.TruncateTime(x.StartDatePlanned)<= date)
             .ToList(); //error here

I also tried this:

lines = lines.Where(x => (DateTime)EntityFunctions
             .TruncateTime((DateTime)x.StartDatePlanned.Value)
             .Value <= date).ToList(); //error here

Anyone knows a solution.

Thanks for your attention :)

Try `EntityFunctions.TruncateTime(x.StartDatePlanned.Value)` ? — Tim.Tang, Aug 22 '14 at 08:51
Tim. Tang, I have tried that already. See the last code block. — Kjell Derous, Aug 22 '14 at 08:52
see this :http://stackoverflow.com/questions/16736252/using-datetime-in-linq-to-entities — Tim.Tang, Aug 22 '14 at 08:54
@IgorSemin `EntityFunctions` exists *specifically* to be functions that the database knows about. — Rawling, Aug 22 '14 at 08:54
@IgorSemin this will convert `LINQ2EF` to `LINQ2Object` ,and sql server do know what is `EntityFunctions.TruncateTime` — Tim.Tang, Aug 22 '14 at 08:56
I did something based on Igor his answer. var lines = db.Query() .Where( *queryToDb*).ToList().Where(x => (DateTime)x.StartDatePlanned.Value <= date); This works fine now! Thx — Kjell Derous, Aug 22 '14 at 09:10
Please, answer yourself and mark as valid, or delete this question — JotaBe, Aug 22 '14 at 10:07

score 0 · Accepted Answer · answered Aug 22 '14 at 11:34

This is my solution:

var lines =
    db.Query<TransportOrderLine>()
      .Where(x => ((isDriver && x.TransportOrder.DriverId == driverId) ||
                  (!isDriver && x.TransportOrder.HaulierId == haulierId)) &&
                  x.StartDatePlanned.HasValue)
                  .ToList().Where(x => (DateTime)x.StartDatePlanned.Value <= date);

System.NotSupportedException using DateTime? in Linq to Entities

1 Answers1