asp.net-mvc 为什么 LINQ to Entities 无法识别“System.String ToString()”方法？

Why LINQ to Entities does not recognize the method 'System.String ToString()?

提问by Neo

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

在 MVC3 Web 应用程序中出错。 LINQ to Entities does not recognize the method 'System.String ToString()' method, and this method cannot be translated into a store expression.

when i try to fetch values using EF from query :

当我尝试使用 EF 从查询中获取值时：

public class DataRepository
    {
        public mydataEntities1 dbContext = new mydataEntities1();

        public List<SelectListItem> GetPricingSecurityID()
        {
        var pricingSecurityID = (from m in dbContext.Reporting_DailyNAV_Pricing
                                     select new SelectListItem
                                         {
                                                Text = m.PricingSecurityID.ToString(),
                                                Value = m.PricingSecurityID.ToString()
                                         });

        return pricingSecurityID.ToList();
        }
    }

回答by Jay

That can't be converted to SQL. I guess, in theory, it could, but isn't implemented.

那不能转换为SQL。我想，理论上，它可以，但没有实施。

You just need to perform your projection after you have your results:

您只需要在获得结果后执行投影：

var pricingSecurityID = (from m in dbContext.Reporting_DailyNAV_Pricing
                                     select m.PricingSecurityID).AsEnumerable()
    .Select(x => new SelectListItem{ Text = x.ToString(), Value = x.ToString() });

回答by Jon Skeet

If it's already a string, why are you bothering to call ToStringin the first place? I suspect a translation wasn't included in LINQ to Entities because it's pointless. Change your select clause to:

如果它已经是一个字符串，你为什么要首先调用ToString？我怀疑 LINQ to Entities 中没有包含翻译，因为它毫无意义。将您的选择条款更改为：

select new SelectListItem
{
    Text = m.PricingSecurityID,
    Value = m.PricingSecurityID
}

If you reallyneed to do something which isn't supported by LINQ to Entities, use AsEnumerableto transition from a database query to in-process:

如果您确实需要执行 LINQ to Entities 不支持的操作，请使用AsEnumerable从数据库查询转换到进程内查询：

public List<SelectListItem> GetPricingSecurityID()
{
    return dbContext.Reporting_DailyNAV_Pricing
                    .Select(m => m.PricingSecurityID)
                    .AsEnumerable() // Rest of query is local
                    // Add calls to ToString() if you really need them...
                    .Select(id => new SelectListItem { Text = id, Value = id })
                    .ToList();
}

I agree with Jason's objections too, btw. You'd be better off returning a List<string>which is rendered elsewhere.

我也同意杰森的反对意见，顺便说一句。您最好返回在List<string>其他地方呈现的a 。

Also note that if you're justgoing to use a single selectclause or just a whereclause, query expressions really don't add much - calling the LINQ extension methods can end up with less clutter, particularly if you want to call methods which aren'tsupported in query expressions (such as ToList).

还要注意的是，如果你只打算使用一个单一的select条款或只是一个where子句，查询表达式真的没有增加多少-调用LINQ扩展方法可以用更少的混乱中结束，特别是如果你要调用哪个方法AREN” t在查询表达式中受支持（例如ToList）。

回答by jason

Because it's trying to convert it to SQL, and it can't. Drop off the call to ToString, and do a projection before you return to the caller. So, replace your selectclause with

因为它试图将其转换为 SQL，但它不能。放下对 的呼叫ToString，并在返回呼叫者之前进行投影。因此，将您的select条款替换为

select m.PricingSecurityID

and then say

然后说

return pricingSecurityID
           .AsEnumerable()
           .Select(x => x.ToString())
           .Select(x => new SelectListItem { Text = x, Value = x })
           .ToList();

Also, I note that you're mixing UI concerns and data querying concerns. This is generally a bad practice. Really, you should just be returning the list of IDs and let the UI portion of your code worry about finagling it into the right form.

另外，我注意到您混合了 UI 问题和数据查询问题。这通常是一种不好的做法。实际上，您应该只返回 ID 列表，让代码的 UI 部分担心将其调整为正确的形式。

回答by C Cartheuser

How about this. In this example, both the VDN field in the db and the Skill field are integers. I'm looking for matches from both fields so I have 2 compares.

这个怎么样。在此示例中，db 中的 VDN 字段和技能字段都是整数。我正在寻找两个领域的匹配项，所以我有 2 个比较。

Include this:

包括这个：

using System.Data.Objects.SqlClient; // needed to convert numbers to strings for linq

When comparing numbers do this:

比较数字时，请执行以下操作：

        // Search Code
            if (!String.IsNullOrEmpty(searchString))
            {
                depts = depts.Where(d => SqlFunctions.StringConvert((double)d.VDN).Contains(searchString.ToUpper())
                || SqlFunctions.StringConvert((double)d.Skill).Contains(searchString.ToUpper()));
            }
        // End Search Code

Workie.

上班族。

回答by Muhammad Alaa

Sadly EF does not know how to convert .ToString() You must use embedded function SqlFunctions.StringConvert: http://msdn.microsoft.com/en-us/library/dd466292.aspxAlso there is no overload for int so you must typecast to double :-(

可悲的是 EF 不知道如何转换 .ToString() 您必须使用嵌入式函数 SqlFunctions.StringConvert：http: //msdn.microsoft.com/en-us/library/dd466292.aspx此外，int 没有重载，因此您必须类型转换为双 :-(

var vendors = 
   from v in Vendors  
   select new
   {             
       Code = SqlFunctions.StringConvert((double)v.VendorId)
   }; 

回答by LCJ

I understand that this question is answered and I agree that using AsEnumerable()is the way to go. However I would like to highlight a common scenario that I usually come across where AsEnumerable()is used inefficiently to resolve this error.

我知道这个问题得到了回答，我同意使用AsEnumerable()是要走的路。但是，我想强调一个常见的场景，我通常会遇到在AsEnumerable()解决此错误时效率低下的情况。

From .NET Language-Integrated Query for Relational Data

来自.NET Language-Integrated Query for Relational Data

The AsEnumerable() operator, unlike ToList() and ToArray(), does not cause execution of the query. It is still deferred. The AsEnumerable() operator merely changes the static typing of the query, turning a IQueryable into an IEnumerable, tricking the compiler into treating the rest of the query as locally executed.

与 ToList() 和 ToArray() 不同，AsEnumerable() 运算符不会导致执行查询。它仍然被推迟。AsEnumerable() 运算符仅更改查询的静态类型，将 IQueryable 转换为 IEnumerable，诱使编译器将查询的其余部分视为本地执行。

References

参考

1. Am I misunderstanding LINQ to SQL .AsEnumerable()?
2. Understanding .AsEnumerable() in LINQ to SQL

1. 我误解了 LINQ to SQL .AsEnumerable() 吗？
2. 了解 LINQ to SQL 中的 .AsEnumerable()

Inefficient way

低效方式

IEnumerable<InvoiceDTO> inefficientEnumerable = 
     (from a in db.Invoices
     where a.Practice_Key == practiceKey.FirstOrDefault()
     select a
     ).AsEnumerable().
     Select(x => new InvoiceDTO
                             {
                                 InvoiceID = x.InvoiceID,
                                 PracticeShortName = x.Dim_Practice.Short_Name,
                                 InvoiceDate = x.InvoiceDate,
                                 InvoiceTotal = x.InvoiceAmount,
                                 IsApproved = x.IsApproved,
                                 InvoiceStatus = (
                                                  x.IsApproved == null ? "Pending" :
                                                  x.IsApproved == true ? "Approved" :
                                                  x.IsApproved == false ? "Rejected" : "Unknown"
                                                ),
                                 InvoicePeriodStartDateText = x.InvoicePeriodStart.ToShortDateString(),
                                 InvoicePeriodEndDateText = x.InvoicePeriodEnd.ToShortDateString(),
                                 InvoicePeriodStartDate = x.InvoicePeriodStart,
                                 InvoicePeriodEndDate = x.InvoicePeriodEnd
                             }
                             );

            invoices = inefficientEnumerable.ToList();

Here the AsEnumerableis used for the entire table. All the columns are getting selected eventhough they are not needed.

这里AsEnumerable用于整个表。即使不需要所有列，也会被选中。

Better Way

更好的方法

 IQueryable<InvoiceDTO> invoicesQuery = 
   (from a in db.Invoices
   where a.Practice_Key == practiceKey.FirstOrDefault()
   select new InvoiceDTO
            {
             InvoiceID = a.InvoiceID,
             PracticeShortName = a.Dim_Practice.Short_Name,
             InvoiceDate = a.InvoiceDate,
             InvoiceTotal = a.InvoiceAmount,
             IsApproved = a.IsApproved,
             InvoiceStatus = (
                               a.IsApproved == null ? "Pending" :
                               a.IsApproved == true ? "Approved" :
                               a.IsApproved == false ? "Rejected" :"Unknown"
                               ),
             InvoicePeriodStartDate = a.InvoicePeriodStart,
             InvoicePeriodEndDate = a.InvoicePeriodEnd
          });


          IEnumerable<InvoiceDTO> betterEnumerable = invoicesQuery.AsEnumerable().
          Select(x => new InvoiceDTO
                                 {
                                     InvoiceID = x.InvoiceID,
                                     PracticeShortName = x.PracticeShortName,
                                     InvoiceDate = x.InvoiceDate,
                                     InvoiceTotal = x.InvoiceTotal,
                                     IsApproved = x.IsApproved,
                                     InvoiceStatus = x.InvoiceStatus,
                                     InvoicePeriodStartDateText = x.InvoicePeriodStartDate.ToShortDateString(),
                                     InvoicePeriodEndDateText = x.InvoicePeriodEndDate.ToShortDateString(),
                                     InvoicePeriodStartDate = x.InvoicePeriodStartDate,
                                     InvoicePeriodEndDate = x.InvoicePeriodEndDate
                                 }
                                 );

回答by Waseem Tahir

Try This using VB.NET, Important Point is you need to Get the results AsEnumerable as mentioned in Answers.

使用 VB.NET 试试这个，重要的一点是你需要像答案中提到的那样获取结果 AsEnumerable。

Dim _EventsDaysResult = From ED In TAdbContext.EventPolicies.AsEnumerable
                                    Where ED.EventID = EID
                                    Select New With {ED.EventID, 
                                    .DayInfo = 
                                    ED.EventDay.GetValueOrDefault.ToShortDateString & " ( " & ED.EventDayTitle & " ) "}

回答by Prakash Gouda

return dbContext.Reporting_DailyNAV_Pricing.AsEnumerable().Select(x => new SelectListItem
{
    Text = x.PricingSecurityID.ToString(),
    Value = x.PricingSecurityID.ToString()
}).ToList();