Geek Answers Handbook

Linq error for entity when using datetimeoffset

I am getting an odd error and I cannot fix it. Can anyone help?

The code below does not work because it delivers the value o.ordered.DateTime.ToShortDateString() (it works when this part is commented out). o.ordered is a datetimeoffset . The error here is given below. I tried several different versions, for example, using date and tostring , and not 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 };
+4
source share
3 answers

The data provider used to translate LINQ code to SQL does not understand ToShortDateString . Because of this, you cannot use it in a LINQ query that is sent to the database. You must 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 creates 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 = xocity, customersOrderRef = xocustomersOrderRef, date = (xoactualDelivery ?? xoplannedDelivery), date1 = (xoactualCease ?? xoactualCease), number = xonumber, ordered = xoordered.DateTime.ToShortDateString(), postCode = xopostCode, status = xsstatus, stockCode = xostockCode, UpdatedByAccount = xoUpdatedByAccount };

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

+7
source

There is no easy solution. In LINQ to Entities you cannot use many standard methods, such as transforms. Usually you just do whatever you can in the request, then call ToList , and then you can use whatever method you want.

+2
source

An alternative to ToShortDateString is to use EntityFunctions.TruncateTime (o.ordered.DateTime). This will require the addition of the System.Data.Objects namespace.

Also, the conversion to Tolist () will first load the data into memory, and then apply conditions to it, where, as before applying Tolist (), the conversion request will be transferred to DB if it is IQueryable.

+1
source

More articles:


All Articles