Changing a DateTime to use UTC in a LINQ to Entity IQueryable anonymous type

Question

I'm using a LINQ to Entity query and returning an anonymous type. The date values are stored in the database in UTC format so I need to format the result of the date value as UTC when returning it to the client. To do this I use the Date.ToString("u")to format the result as UTC. The only problem is that when using the ToString in a LINQ query it fails because there is no equivalent command to convert it in SQL. How do I return the results of my query in UTC using LINQ to Entity? Here is an example of my query:

Return _db.WorkOrders.Include("Client").Include("Warehouse")
    .OrderByDescending(Function(wo) wo.Date)
    .Where(Function(w)
    (w.Status = WorkOrder.WorkOrderStatus.Submitted)
    .Select(Function(wo) New With {.Client = wo.Client.Name, 
    .Date = wo.Date.ToString("u"), .Warehouse = wo.Warehouse.Name, .Status = wo.Status.ToString})
    .ToDataSourceResult(request.Take, request.Skip, request.Sort, request.Filter)

The query fails on the wo.Date.ToString("u") because there's no equivalent method in SQL.

@DanielShillcock I can't use those functions inside the LINQ query because there's no SQL equivalent so .NET doesn't know what to do with it. See [link](https://stackoverflow.com/questions/6887776/linq-convert-datetime-to-string) — Nse, Sep 07 '17 at 14:31
Materialize you list first using ToList(), then project into your anonymous object. After you use ToList(), you will have all the object and your ToString() will be supported because you are using LINQ to Object instead of SQL. — Fran, Sep 07 '17 at 17:23

score 3 · Accepted Answer · answered Sep 08 '17 at 01:41

Certainly ToString() method is unsupported in LINQ to Entities - there's no SQL equivalent to convert DateTime into string. There is GetUtcDate for UTC conversion in available SqlFunctions but no DateTime to string conversion method exists.

You can try using ToList or AsEnumerable to materialize query results in memory first, performing DateTime to UTC string conversion with LINQ to Objects, then revert back using AsQueryable which resulting IQueryable object to use with ToDataSourceResult as given below:

Return _db.WorkOrders.Include("Client").Include("Warehouse")
       .OrderByDescending(Function(wo) wo.Date)
       .Where(Function(w) (w.Status = WorkOrder.WorkOrderStatus.Submitted)
       .ToList()
       .Select(Function(wo) New With {
           .Client = wo.Client.Name, 
           .Date = wo.Date.ToUniversalTime().ToString(), 
           .Warehouse = wo.Warehouse.Name, 
           .Status = wo.Status.ToString() 
           })
       .AsQueryable() ' since ToDataSourceResult requires IQueryable, this should be added
       .ToDataSourceResult(request.Take, request.Skip, request.Sort, request.Filter)

Changing a DateTime to use UTC in a LINQ to Entity IQueryable anonymous type

1 Answers1