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
                       };

Answer 1

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.

Answer 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.

Answer 3

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.