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

问题描述

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