BPM C# foreach loop with OrderBy string as int

All,

I have a BPM custom code widget that requires a foreach loop with an order by statement like this.

foreach (var nextcert in (from Cert_Row in Db.UD06 where Cert_Row.Company == Session.CompanyID && Cert_Row.Key2 == rowVDR.VendorID select Cert_Row).OrderBy(x=>x.ShortChar01).OrderByDescending(t=>t.Key5))

That works except that Key5 is a string values that stores an integer and so the sort does not sort numerically as needed. Poking around on the internet, I found and tried this…

foreach (var nextcert in (from Cert_Row in Db.UD06 where Cert_Row.Company == Session.CompanyID && Cert_Row.Key2 == rowVDR.VendorID select Cert_Row).OrderBy(x=>x.ShortChar01).OrderByDescending(t=>t.Key5.PadLeft(6,'0')))
(see the PadLeft added to the end of the command)

Though the syntax checks passes the syntax checker in the custom code widget, the BPM throws an error when this version is used.

Any ideas as to how I can get the string to sort as numeric?

Thanks so much!

PadLeft is not supported cause it can’t be easily translated to SQL

1 Like

I just did a search on google, and found several references to using a sql function… like this… maybe something to try.

.OrderByDescending(SqlFunctions.StringConvert((double)t.Key5))
2 Likes

Thanks to both @josecgomez and @timshuwy for their input. Though I could not make Tim’s suggestion work, both suggestions inspired me to look again. I found that this works to solve my issue:

foreach (var nextcert in (from Cert_Row in Db.UD06 where Cert_Row.Company == Session.CompanyID && Cert_Row.Key2 == rowVDR.VendorID select Cert_Row).OrderBy(x=>x.ShortChar01).ThenByDescending(t=>t.Key5.Length).ThenByDescending(t=>t.Key5))

Sorting by the length of Key5 then by Key5 is a neat little trick to get around the issue without padding. I like Tim’s idea better i.e. finding a way to convert the string to an int on the fly within the sort function, I could not figure out a syntax that would work.

What I have works for now, always looking to make it better.

Michael Thompson

2 Likes