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

本文是小编为大家收集整理的关于在linq-to-sql查询中调用字符串操作方法的问题的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

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

我尝试过的一些事情:

a) 最初连接键的创建是在同一个查询中,没有改变任何东西

b) 将非字符串字段转换为字符串(与 .ToString 一起工作的另一整罐工作在 linq 中不起作用.尝试了 String.Concat 和 String.Format,在某些情况下工作正常,但在您尝试参考时却不行该值稍后)

c) 使用 concat etc 而不是 '+' 将事物连接在一起.

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

有很多行,所以我不想将数据转换为列表/数组等,但如果这是唯一的选择,那么任何建议都会受到赞赏.

非常感谢!- 标记

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

-- 去除字符的方法---

   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=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")))' 转换为 SQL 并且无法将其视为本地表达式.

推荐答案

因为它试图构建表达式的SQL查询,而MFUtil.StripNonNumeric不能翻译成SQL.

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

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

本文地址:https://www.itbaoku.cn/post/1557011.html

问题描述

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.

Some things I've tried:

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

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) 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.

Many thanks! - Mark

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

-- The Exception Message--

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.

推荐答案

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_)
});
相关标签/搜索