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?
 
Fab_CConnect With a Mentor Commented:
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
 
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
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
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
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.