在格式化日期LINQ到实体查询导致异常 [英] Formatting date in Linq-to-Entities query causes exception

问题描述

我有实体类的datetime提起，我想选择不同的周一YYYY格式的日期时间提起值和填充下拉列表。

I have Entity class with datetime filed, I want to select distinct 'mon-yyyy' format datetime filed value and populate drop down list.

下面的代码给我的错误：

the following code giving me the error:

var env = db.Envelopes.Select(d => new
        {
            d.ReportDate.Year,
            d.ReportDate.Month,
            FormattedDate = d.ReportDate.ToString("yyyy-MMM")
        }).Select(d => d.FormattedDate)

    List<SelectListItem> _months = new List<SelectListItem>();         

    foreach (var mname in env)
    {
        _months.Add(new SelectListItem() { Text = mname, Value = mname });
    }

错误消息：

LINQ到实体无​​法识别
的方法'System.String
的ToString（System.String）的方法，和
这种方法不能被翻译成
A店的表情。

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

我怎样才能纠正这个错误信息？

How can I correct this error message?

由于
SR

Thanks SR

推荐答案

记住，你的查询将被翻译成SQL和发送到数据库。您试图格式化日期是不是在查询中，这就是为什么你看到那个特定的错误消息的支持。您需要检索的结果，然后将数据已经被物化后格式化。

Remember that your query is going to be translated to SQL and sent to the database. Your attempt to format the date is not supported in the query, which is why you are seeing that particular error message. You need to retrieve the results and then format after the data has been materialized.

一种选择是只需选择，因为它是日期。当您遍历结果，将其添加到您的列表格​​式化。但你也可以实现与使用方法链在单个语句格式化的日期列表的建设

One option is to simply select the date as it is. As you iterate over the result, format it as you add it to your list. But you can also achieve the construction of the list with the formatted date in a single statement by using method chaining.

List<SelectListItem> _months = db.Envelopes.OrderByDescending(d => d.ReportDate)
        .Select(d => d.ReportDate)
        .AsEnumerable() // <-- this is the key method
        .Select(date => date.ToString("MMM-yyyy"))
        .Distinct()
        .Select(formattedDate => new SelectListItem { Text = formattedDate, Value = formattedDate })
        .ToList(); 

该方法 .AsEnumerable（）将迫使对数据库和其余部分的查询的第一部分的执行将在存储器中的结果是工作

The method .AsEnumerable() will force the execution of the first portion of the query against the database and the rest will be working with the results in memory.

这篇关于在格式化日期LINQ到实体查询导致异常的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！