在linq-to-sql查询中调用字符串操作方法时出现问题 [英] Problem calling string manipulation method within linq-to-sql query

问题描述

尝试使用LINQ调用字符串操作方法时，我遇到了一个令人沮丧的问题.我现在已经做了很多搜索，并尝试了各种方法来使下面标记为"FAILS"的行起作用.当前会引发异常.

I'm having a frustrating issue trying to use LINQ to call a string manipulation method. I've done lots of searching now and have tried various method to get the line noted as 'FAILS' below to work. It currently throws an exception.

我尝试过的一些事情:

a)最初，级联键的创建是在同一查询中，没有做任何更改

a) Initially the creation of the concatenated key was in the same query, didn't change anything

b)将非字符串字段转换为字符串(另一种完整的.ToString项在linq中不起作用.尝试了String.Concat和String.Format，在某些情况下可以正常运行，但在尝试引用时无法正常运行)稍后再输入该值)

b) Converting the non-string fields to strings (another whole can of works with .ToString not working in linq. String.Concat and String.Format were tried, work ok in some cases but not when you try to refer to that value later on)

c)使用concat等代替'+'将事物连接在一起.

c) Using the concat etc instead of the '+' to join the things together.

如您所见，将字符串附加到非字符串似乎相当宽容，但是调用该方法时不是.

As you can see it seems fairly tolerant of appending strings to non-strings, but not when that method is invoked.

有很多行，所以我不希望将数据转换为列表/数组等，但是如果这是唯一的选择，那么任何建议都值得赞赏.

There are lots of rows so I'd prefer not to convert the data to a list/array etc, but if that's the only option then any suggestions appreciated.

非常感谢！ -马克

    var vouchers = from v in db.Vouchers
                   select new
                   {
                       v.Amount,
                       v.Due_Date,
                       v.Invoice_Date,
                       v.PO_CC,
                       v.Vendor_No_,
                       v.Invoice_No_,
                       invoiceNumeric = MFUtil.StripNonNumeric(v.Invoice_No_)
                   };


    var keyedvouchers = from vv in vouchers
                        select new
                        {
                            thekey = vv.Vendor_No_ + "Test", // works with normal string
                            thekey2 = vv.Amount + "Test", // works with decimal
                            thekey3 = vv.Invoice_Date + "Test", // works with date
                            thekey4 = vv.invoiceNumeric, // works
                            thekey5 = vv.invoiceNumeric + "Test" // FAILS
                        };

-去除字符的方法---

-- The method to strip chars ---

   public static string StripNonNumeric(string str) 
   {
        StringBuilder sb = new StringBuilder();
        foreach (char c in str) 
        {
            // only append if its withing the acceptable boundaries
            // strip special chars: if ((c >= '0' && c <= '9') || (c >= 'A' && c <= 'Z') | || (c >= 'a' && c <= 'z') | c == '.' || c == '_') 

            // strip any nonnumeric chars
            if (c >= '0' && c <= '9')
                {
                sb.Append(c);
                }
        }
        return sb.ToString();
    }

-异常消息-

System.InvalidOperationException未通过用户代码处理 Message =无法翻译表达式'Table(Voucher).Select(v => new<> f__AnonymousType0 7(Amount = v.Amount, Due_Date = v.Due_Date, Invoice_Date = v.Invoice_Date, PO_CC = v.PO_CC, Vendor_No_ = v.Vendor_No_, Invoice_No_ = v.Invoice_No_, invoiceNumeric = StripNonNumeric(v.Invoice_No_))).Select(vv => new <>f__AnonymousType1 5(thekey =(vv.Vendor_No_ +"Test")，thekey2 =(Convert(vv.Amount)+"Test)，thekey3 =(Convert(vv.Invoice_Date)+" Test)，thekey4 = vv.invoiceNumeric，thekey5 =(vv.invoiceNumeric +" Test)))''转换为SQL，因此无法将其视为本地表达式

System.InvalidOperationException was unhandled by user code Message=Could not translate expression 'Table(Voucher).Select(v => new <>f__AnonymousType07(Amount = v.Amount, Due_Date = v.Due_Date, Invoice_Date = v.Invoice_Date, PO_CC = v.PO_CC, Vendor_No_ = v.Vendor_No_, Invoice_No_ = v.Invoice_No_, invoiceNumeric = StripNonNumeric(v.Invoice_No_))).Select(vv => new <>f__AnonymousType15(thekey = (vv.Vendor_No_ + "Test"), thekey2 = (Convert(vv.Amount) + "Test"), thekey3 = (Convert(vv.Invoice_Date) + "Test"), thekey4 = vv.invoiceNumeric, thekey5 = (vv.invoiceNumeric + "Test")))' into SQL and could not treat it as a local expression.

推荐答案

这是因为它试图构建表达式的SQL查询，并且MFUtil.StripNonNumeric无法转换为SQL.

It's because it tries to build an SQL query of the expression and the MFUtil.StripNonNumeric cannot be translated into SQL.

尝试先返回它，然后将结果转换为列表，然后使用第二个查询将其转换.

Try returning it first and then convert the reult into a list and then use a second query to convert it.

var vouchers_temp = from v in db.Vouchers
               select new
               {
                   v.Amount,
                   v.Due_Date,
                   v.Invoice_Date,
                   v.PO_CC,
                   v.Vendor_No_,
                   v.Invoice_No_
               };


var vouchers = vouchers_temp.ToList().Select( new {
                   Amount,
                   Due_Date,
                   Invoice_Date,
                   PO_CC,
                   Vendor_No_,
                   Invoice_No_,
                   invoiceNumeric = MFUtil.StripNonNumeric(Invoice_No_)
});

这篇关于在linq-to-sql查询中调用字符串操作方法时出现问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！