使用datetimeoffset时实体框架错误

本文关键字：框架 错误 实体 datetimeoffset 使用 | 更新日期: 2023-09-27 18:07:14

我得到一个奇怪的错误，我不能修复它。有人能帮忙吗?

下面的代码失败了，因为它不像o.ordered.DateTime.ToShortDateString()(当该部分被注释掉时它可以工作)。o.ordered是一个datetimeoffset。它给出的误差如下。我已经尝试了几个不同的版本，如使用date和tostring，而不是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
                       };

使用datetimeoffset时实体框架错误

用于将LINQ代码转换为SQL的数据提供程序不理解ToShortDateString。因此，您不能在发送到数据库的LINQ查询中使用它。您需要在从数据库返回数据后调用此方法:

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

顺便说一句:有另一个解决方案可以产生更短的代码:

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

这两个版本的区别在于，第一个版本只从数据库中请求您需要的列，而第二个版本将返回两个表的所有列。

没有简单的解决办法。在LINQ to Entities中，您不能使用许多标准方法，如转换。通常你只是在query中做你能做的一切，然后调用ToList，然后你可以使用任何你想要的方法。

ToShortDateString的替代方法是使用EntityFunctions.TruncateTime(.ordered. datetime)。这将需要添加名称空间System.Data.Objects .

同样转换到Tolist()将首先加载数据到内存中，然后在其上应用条件，在应用Tolist()之前，如果它是IQueryable，将把转换请求给DB

相关文章：