LINQ查询 - 不能将日期用作其非促进EDM类型.也不能将日期时间转换为字符串

Question

我在linq查询下面有错误：
linq到实体无法识别方法'System.String ToString(System.DateTime('方法，并且该方法无法翻译成商店表达式。

var query =   from i in Expenditure
              join d in Department on i.DepartId equals d.dID
              where i.Status == "Audit"
              group i by new { i.InvoiceId, d.Title, i.InvoiceDate } into g
              select new
              {
                id = g.Key.InvoiceId,
                txt = g.Key.Title + " // " + g.Key.InvoiceId + " // " + Convert.ToString(g.Key.InvoiceDate)
              };

我希望TXT输出如下：

健康//Inv001//12-03-2018
教育//Inv002//23-03-3018

问题是我最初写(没有转换(为：g.key.invoicedate
的Invoicedate(DateTime(字段但是我的错误是：无法施放类型的'System.DateTime'键入'System.Object'。linq到实体仅支持铸造EDM原始或枚举类型。

有人可以建议一个简单的解决方案吗？谢谢。

Answer 1

toString不是SQL函数，因此在SQL查询中无法完成。

尝试以下操作：

var query =   (from i in Expenditure
              join d in Department on i.DepartId equals d.dID
              where i.Status == "Audit"
              group i by new { i.InvoiceId, d.Title, i.InvoiceDate } into g
              select new
              {
                g.Key.InvoiceId,
                g.Key.Title,
                g.Key.InvoiceDate,
              }).ToList()
              .select new
              {
                id = InvoiceId,
                txt = Title + " // " + InvoiceId + " // " + Convert.ToString(InvoiceDate)
              };

.tolist((之后的所有内容都将在内存中完成，您可以在其中使用任何.NET方法。

Answer 2

而不是在数据库中组装txt，而是强制Linq To Entities将逻辑转换为SQL，首先检索数据，然后应用转换

尝试关注

var query =   
         (from i in Expenditure
          join d in Department on i.DepartId equals d.dID
          where i.Status == "Audit"
          group i by new { i.InvoiceId, d.Title, i.InvoiceDate } into g
          select new
          {
            id = g.Key.InvoiceId,
            title = g.Key.Title,
            invoiceId = g.Key.InvoiceId,
            invoiceDate =  g.Key.InvoiceDate
          })
          .ToList()
          .Select(it => new 
            { 
               id = it.id, 
               txt = it.title + " // " + it.invoiceId + " // " + it.invoiceDate 
            });