Linq to entity framework error when using datetimeoffset

Question

Im getting an odd error and I cant fix it. Can someone help?

The below code fails because it dosent like o.ordered.DateTime.ToShortDateString() (it works when that part is commented out). o.ordered is a datetimeoffset. There error it gives is below. I have tried a few diffrent versions like using date and tostring rather than toshortdatestring.

LINQ to Entities does not recognize the method 'System.String ToShortDateString()' method, and this method cannot be translated into a store expression. 

        var BeOrders = from o in BEdb.onlineOrders
                       join s in BEdb.order_Statuses
                       on o.status equals s.ID
                       where o.custCode == pp.AccountID
                       select new DataLayer.OrderStatusItem {
                           city = o.city,
                           customersOrderRef = o.customersOrderRef,
                           date = (o.actualDelivery ?? o.plannedDelivery),
                           date1 = (o.actualCease ?? o.actualCease),
                           number = o.number,
                           ordered = o.ordered.DateTime.ToShortDateString(),
                           postCode = o.postCode,
                           status = s.status,
                           stockCode = o.stockCode,
                           UpdatedByAccount = o.UpdatedByAccount
                       };

Daniel Hilgarth · Accepted Answer · 2011-09-02T10:24:41.663

The data provider used to translate the LINQ code to SQL doesn't understand ToShortDateString. Because of that, you can't use it in a LINQ query that is sent to the database. You need to call this method after the data has been returned from the database:

    var BeOrders = (from o in BEdb.onlineOrders
                   join s in BEdb.order_Statuses
                   on o.status equals s.ID
                   where o.custCode == pp.AccountID
                   select new {
                       city = o.city,
                       customersOrderRef = o.customersOrderRef,
                       date = (o.actualDelivery ?? o.plannedDelivery),
                       date1 = (o.actualCease ?? o.actualCease),
                       number = o.number,
                       ordered = o.ordered,
                       postCode = o.postCode,
                       status = s.status,
                       stockCode = o.stockCode,
                       UpdatedByAccount = o.UpdatedByAccount
                   }).ToList()
                     .Select(x => new DataLayer.OrderStatusItem {
                       city = x.city,
                       customersOrderRef = x.customersOrderRef,
                       date = x.date,
                       date1 = x.date1,
                       number = x.number,
                       ordered = x.ordered.DateTime.ToShortDateString(),
                       postCode = x.postCode,
                       status = x.status,
                       stockCode = x.stockCode,
                       UpdatedByAccount = x.UpdatedByAccount
                   };

BTW: There is another solution that produces shorter code:

    var BeOrders = (from o in BEdb.onlineOrders
                   join s in BEdb.order_Statuses
                   on o.status equals s.ID
                   where o.custCode == pp.AccountID
                   select new { o, s }).ToList()
                   .Select(x => new DataLayer.OrderStatusItem
                   {
                       city = x.o.city,
                       customersOrderRef = x.o.customersOrderRef,
                       date = (x.o.actualDelivery ?? x.o.plannedDelivery),
                       date1 = (x.o.actualCease ?? x.o.actualCease),
                       number = x.o.number,
                       ordered = x.o.ordered.DateTime.ToShortDateString(),
                       postCode = x.o.postCode,
                       status = x.s.status,
                       stockCode = x.o.stockCode,
                       UpdatedByAccount = x.o.UpdatedByAccount
                   };

The difference between those two versions is that the first version only requests those columns from the database you need where as the second version will return all columns of the two tables.

That code gives the error "The specified type member 'DateTime' is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported." Moving .DateTime to the list works fine. Thanks — Tom Squires, Sep 02 '11 at 10:06

score 2 · Answer 2 · answered Sep 02 '11 at 10:02

2

There is no simple solution. In LINQ to Entities you can't use many standard methods like conversions. Usually you just do everything you can in query, then call ToList, and then you can use any method you want.

answered Sep 02 '11 at 10:02

Piotr Auguscik

3,651
1
22
30

score 1 · Answer 3 · answered Sep 04 '13 at 19:59

1

Alternative to ToShortDateString is to use the EntityFunctions.TruncateTime(o.ordered.DateTime) . This will require the namespace to add System.Data.Objects.

Also converting to Tolist() will first load the data in memory and then apply conditions on it where as before applying the Tolist() will give the conversion request to DB if it is IQueryable.

answered Sep 04 '13 at 19:59

Yasir Ali

21
2

I'm so mad there's a TruncateTime method, but no TruncateOffset method, so it's impossible for me to compare the DateTime part only of a DateTimeOffset. TruncateTime has two overloads, one for DateTime? and one for DateTimeOffset?. The latter truncates the time component but keeps the offset. I need one that just truncates the offset. – Triynko Dec 15 '16 at 19:33

Linq to entity framework error when using datetimeoffset

3 Answers3

Linked