1

I've recently upgraded from ASP.NET-Core 2.2 to ASP.NET-Core 3.1. I have quantities that are strings (most are just numbers but we have some that are 100 Feet, 1 Spool, etc...) In 2.2 I could sort these by using PadLeft, but this throws an error in ASP.NET-Core3.0 and above. Does anyone know a good workaround for using LINQ queries which sort numbers that are strings? (so you don't get "999", then "99", then "888", "88", etc...)

My old solution was:

  IQueryable<InventoryItem> Items;
  Items = from m in _context.Inventory.Where(m => m.Removed == 0)
                                              select m;
    case "Quantity":
         Items = Items.OrderByDescending(s => s.Quantity.PadLeft(maxlen, '0'));
         break;

This no longer works because ASP.NET-Core 3.1 evaluates it at the server instead of at client. It doesn't understand .PadLeft()

Added: I am paginating, so the query is running on this line:

 items = await source.Skip(
                    (pageIndex - 1) * pageSize)
                    .Take(pageSize).ToListAsync();

If I go toList before then, I'm not limiting the results from the query to the number of results chosen...

Serge
  • 40,935
  • 4
  • 18
  • 45
pcalkins
  • 1,188
  • 13
  • 20
  • Can you [edit] your post with a [mre]? – gunr2171 Sep 01 '21 at 17:51
  • If there is no Skip/Take which limit query result, just add `AsEnumerable` before `OrderBy` it will be the same as in EF Core 2.2. – Svyatoslav Danyliv Sep 01 '21 at 18:25
  • I added the type and initial query. – pcalkins Sep 01 '21 at 19:03
  • There is pagination later... I think I just need to wait to do the sorting after I get back a list at the end of all other deferred queries. So it's a re-ordering of my code and sorting the List instead of sorting via query. Unless there's a solution that I can use before going toList.. – pcalkins Sep 01 '21 at 20:09
  • Actually that won't work as Serge point out to me. Order of pages is determined by sorting... back to the drawing board. – pcalkins Sep 01 '21 at 20:42
  • I think I'm going to have to bite the bullet and separate this into qty (ints) and units (strings). ea., feet, inches, etc... Using a string type in the first place was really a hack to import some excel files that had non-standardized quantities... Either that or go to list with everything and then sort with the pad in place. – pcalkins Sep 02 '21 at 21:31
  • I've temporarily solved this by adding a field "IntegerQuantity" to the table. Then ran a seed which try/parsed the strings to integer (if failed set to 0) and added those values to the integer column. Then when I sort I sort by IntegerQuantity. 99% of the data is convertable.... so I just have to fix all the string values when I have the time. Then delete the string "QTY" field and add a "Units" string field seeded to "each"... then go back and fix the non-each values when I have the time. – pcalkins Oct 21 '21 at 17:22

2 Answers2

2

You have an error because Linq can't convert your code to Sql query. So you have to download data from db at first using ToList (or ToArray) for example. After this you can use any kind of algorithm to sort the list.

Items = _context.Inventory.Where(m => m.Removed == 0).ToList();

Items = items.OrderByDescending(s => s.Quantity.PadLeft(maxlen,'0'));
Serge
  • 40,935
  • 4
  • 18
  • 45
  • Type problems. (didn't want to cast from IQueryAble to IEnumerable) I added my type to the OP. – pcalkins Sep 01 '21 at 19:22
  • @pcalkins I didn' t know this , just don't use context, look at my update answer. – Serge Sep 01 '21 at 19:28
  • I'm starting to think I should initialize as IEnumerable instead... unsure of the consequences of doing that. I think they both uses deferred queries? – pcalkins Sep 01 '21 at 19:30
  • @pcalkins ToLIst is AsEnumerable. – Serge Sep 01 '21 at 19:33
  • one of my last calls is for pagination which takes IQueryable. It converts to list after limiting the query. I think I may have to do the sorting after the pagination returns the list. I think your solution will work then... So I have to re-order my process here. Sorting doesn't need to be deferred. (but might take a slight performance hit here?) – pcalkins Sep 01 '21 at 19:51
  • @pcalkins I updated my answer. Pls check. – Serge Sep 01 '21 at 20:04
  • @pcalkins If you want to use paginattion I don't think that this will work for you since you need to sort all data at data base server and after this to take the part you need. Think about using a stored procedure maybe , since you can create much more complicated sql script. – Serge Sep 01 '21 at 20:17
  • was this ever actually working correctly in ASP.NET-Core 2.2? (still deferred?) I know it worked, but was it doing it efficiently, all in the query? – pcalkins Sep 01 '21 at 21:06
  • 2
    @pcalkins The versions before 5 it if they can't convert to sq autiomatically selected ALL records from Db to web server and after this sorted it Lots of people didn' t realize it and thought everything was fine. Now ef5+ gives the error if it can not convert to sql. – Serge Sep 01 '21 at 21:10
0

Try to do this. Convert.ToInt32 is automatically converted from EF into a query.

Items = Items.OrderByDescending(s => Conert.ToInt32(s.Quantity));
Den
  • 650
  • 6
  • 15
  • it errors out when it finds a non-number... usually I would tryparse those calls, but don't think you can do it here? It'd convert to bool... not sure if I can put all that if/then logic in there. – pcalkins Sep 02 '21 at 16:55
  • Tryparse doesn't convert in SQL query. Sorry I think you have only number.... – Den Sep 02 '21 at 17:46
  • In this answare you can find some help. https://stackoverflow.com/a/35450462/15714806 – Den Sep 02 '21 at 17:51