Linq Order with a substring

Hello all,

I have an interesting problem:

PO-Iro-1710
PO-Iro-0110
PO-Iro-4700
PO-Iro-0813
PO-Iro-0910
PO-Iro-1410

I would like to write a query to return this list in a specific order. (first by the last two digits and then by then second to last two digits. Ex:

PO-Iro-0813
PO-Iro-4700
PO-Iro-1710
PO-Iro-0110
PO-Iro-0910
PO-Iro-1410


Here is what I have so far:

I'm getting: Unrecognized expression node: ArrayIndex

Any help would be awesome!

Thanks
Purchase oPurchase = (from c in db.Purchases 
                      where c.ReferencePO.ToUpper().Contains(sSupplierCode.ToUpper())
                      select c).OrderBy(c => c.ReferencePO.Split(new[] { '-' })[2].Substring(2))
                      .ThenBy(c => c.ReferencePO.Split(new[] { '-' })[2].Substring(0, 2)).Take(1).SingleOrDefault();

Open in new window

LVL 13
copyPasteGhostAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

lazyberezovskyCommented:
Try
".Take(1).SingleOrDefault()" removed and "var" added.
var result = (from c in db.Purchases 
                      where c.ReferencePO.ToUpper().Contains(sSupplierCode.ToUpper())
                      select c).OrderBy(c => c.ReferencePO.Split(new[] { '-' })[2].Substring(2))
                      .ThenBy(c => c.ReferencePO.Split(new[] { '-' })[2].Substring(0, 2));

Open in new window

0
copyPasteGhostAuthor Commented:
Sorry for the Dealy...

When I try your code I get the same error:

{"Unrecognized expression node: ArrayIndex"}

Any other ideas?
0
Fab_CCommented:
Hi copyPasteGhost,

Personally I would split out the string parts that you wish to order on into an anonymous type query (along with the object itself), and filter using those; then select the object - code below.
You can also use .FirstOrDefault() instead of .Take(1).SingleOrDefault() ; additionally if you have only one argument that you want to split by you can use .Split('-') instead of .Split(new[] {'-'}).
I have used my recommendations above in my code sample purely due to my personal preference / habit - please feel free to keep using your methods.

I hope this helps;

Fab
Purchase oPurchase = (from c in db.Purchases 
                      where c.ReferencePO.ToUpper().Contains(sSupplierCode.ToUpper())
                      select new
		      {
			c,
			LastTwo = c.ReferencePO.Split('-')[2].Substring(2),			
			FirstTwo = c.ReferencePO.Split('-')[2].Substring(0,2)
		      }).OrderBy(c => c.LastTwo).ThenBy(c => c.FirstTwo).FirstOrDefault().c;

Open in new window

0
Learn SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

copyPasteGhostAuthor Commented:
Cool let me try that. I see what you're doing....
0
copyPasteGhostAuthor Commented:
nope:  Unrecognized expression node: ArrayIndex

Stacktrace:

"   at System.Data.Linq.SqlClient.QueryConverter.VisitArrayIndex(BinaryExpression b)\r\n   at System.Data.Linq.SqlClient.QueryConverter.VisitInner(Expression node)\r\n   at System.Data.Linq.SqlClient.QueryConverter.Visit(Expression node)\r\n   at System.Data.Linq.SqlClient.QueryConverter.VisitExpression(Expression exp)\r\n   at System.Data.Linq.SqlClient.QueryConverter.VisitMethodCall(MethodCallExpression mc)\r\n   at System.Data.Linq.SqlClient.QueryConverter.VisitInner(Expression node)\r\n   at System.Data.Linq.SqlClient.QueryConverter.Visit(Expression node)\r\n   at System.Data.Linq.SqlClient.QueryConverter.VisitExpression(Expression exp)\r\n   at System.Data.Linq.SqlClient.QueryConverter.VisitNew(NewExpression qn)\r\n   at System.Data.Linq.SqlClient.QueryConverter.VisitInner(Expression node)\r\n   at System.Data.Linq.SqlClient.QueryConverter.Visit(Expression node)\r\n   at System.Data.Linq.SqlClient.QueryConverter.VisitSelect(Expression sequence, LambdaExpression selector)\r\n   at System.Data.Linq.SqlClient.QueryConverter.VisitSequenceOperatorCall(MethodCallExpression mc)\r\n   at System.Data.Linq.SqlClient.QueryConverter.VisitMethodCall(MethodCallExpression mc)\r\n   at System.Data.Linq.SqlClient.QueryConverter.VisitInner(Expression node)\r\n   at System.Data.Linq.SqlClient.QueryConverter.Visit(Expression node)\r\n   at System.Data.Linq.SqlClient.QueryConverter.VisitOrderBy(Expression sequence, LambdaExpression expression, SqlOrderType orderType)\r\n   at System.Data.Linq.SqlClient.QueryConverter.VisitSequenceOperatorCall(MethodCallExpression mc)\r\n   at System.Data.Linq.SqlClient.QueryConverter.VisitMethodCall(MethodCallExpression mc)\r\n   at System.Data.Linq.SqlClient.QueryConverter.VisitInner(Expression node)\r\n   at System.Data.Linq.SqlClient.QueryConverter.Visit(Expression node)\r\n   at System.Data.Linq.SqlClient.QueryConverter.VisitThenBy(Expression sequence, LambdaExpression expression, SqlOrderType orderType)\r\n   at System.Data.Linq.SqlClient.QueryConverter.VisitSequenceOperatorCall(MethodCallExpression mc)\r\n   at System.Data.Linq.SqlClient.QueryConverter.VisitMethodCall(MethodCallExpression mc)\r\n   at System.Data.Linq.SqlClient.QueryConverter.VisitInner(Expression node)\r\n   at System.Data.Linq.SqlClient.QueryConverter.Visit(Expression node)\r\n   at System.Data.Linq.SqlClient.QueryConverter.VisitFirst(Expression sequence, LambdaExpression lambda, Boolean isFirst)\r\n   at System.Data.Linq.SqlClient.QueryConverter.VisitSequenceOperatorCall(MethodCallExpression mc)\r\n   at System.Data.Linq.SqlClient.QueryConverter.VisitMethodCall(MethodCallExpression mc)\r\n   at System.Data.Linq.SqlClient.QueryConverter.VisitInner(Expression node)\r\n   at System.Data.Linq.SqlClient.QueryConverter.ConvertOuter(Expression node)\r\n   at System.Data.Linq.SqlClient.SqlProvider.BuildQuery(Expression query, SqlNodeAnnotations annotations)\r\n   at System.Data.Linq.SqlClient.SqlProvider.System.Data.Linq.Provider.IProvider.Execute(Expression query)\r\n   at System.Data.Linq.DataQuery`1.System.Linq.IQueryProvider.Execute[S](Expression expression)\r\n   at System.Linq.Queryable.FirstOrDefault[TSource](IQueryable`1 source)\r\n   at Purchase.GetNextPO(String sSupplierCode) in D:\\Projects\\RSCore\\CoreDB\\Purchase.cs:line 474\r\n   at RSCoreWeb.DBTester.btnTest_Click(Object sender, EventArgs e) in D:\\Projects\\RSCore\\RSCoreWeb\\DBTester.aspx.cs:line 17\r\n   at System.Web.UI.WebControls.Button.OnClick(EventArgs e)\r\n   at System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument)\r\n   at System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument)\r\n   at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument)\r\n   at System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)\r\n   at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)"
0
Fab_CCommented:
Hmmm it seems that it really doesn't like the Split()[] command...
Based on your example in your original post, do you really need to split the string? I.e. are you always ordering based off the last 4 characters of the entire ReferencePO number?

If so, you could always skip the split and use the string length instead:
Purchase oPurchase = (from c in db.Purchases 
                      where c.ReferencePO.ToUpper().Contains(sSupplierCode.ToUpper())
                      select new
                      {
                        c,
                        LastTwo = c.ReferencePO.Substring(c.ReferencePO.Length - 2),                     
                        FirstTwo = c.ReferencePO.Substring(c.ReferencePO.Length - 4, 2)
                      }).OrderBy(c => c.LastTwo).ThenBy(c => c.FirstTwo).FirstOrDefault().c;

Open in new window

0
copyPasteGhostAuthor Commented:
the code is like this:

XX-XXX-9999

The last section can have multiple digits...and not just 4.

That's why I was running with the slipt.... I'm open to anything else you can think of....
0
Fab_CCommented:
Ah I see.

Well it seems like the problem stems from the Split function not having an SQL equivalent - what we need to try and do is get the work done on the client side rather than in SQL, so I would do something like in the following code :

The first query will return all the Purchases from the DB into a List - the second query will then run directly on the client instead of trying to do it in the database in SQL.

*fingers crossed*
var purchases = (from c in db.Purchases 
                 where c.ReferencePO.ToUpper().Contains(sSupplierCode.ToUpper())
                 select c).ToList();

Purchase oPurchase = (from c in purchases
                      select new
                      {
                        c,
                        LastTwo = c.ReferencePO.Split('-')[2].Substring(2),                     
                        FirstTwo = c.ReferencePO.Split('-')[2].Substring(0,2)
                      }).OrderBy(c => c.LastTwo).ThenBy(c => c.FirstTwo).FirstOrDefault().c;

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
copyPasteGhostAuthor Commented:
cool it worked but it is sorting on the first two first and then on the second....

Strange huh?
0
copyPasteGhostAuthor Commented:
My bad.

Thanks!
0
Fab_CCommented:
Glad to have helped! Good luck with the rest of your project :)

Cheers,

Fab
0
copyPasteGhostAuthor Commented:
Thanks.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.