Split number from string for order by clause in Entity Framework

Question

I have string column contains 1st Batch, 2nd Batch, 10th Batch etc. Now I am trying to sort them using this string field on ascending order e.g. 1, 2, 3 but having no luck.

public ActionResult PublicGrid()
{
    ViewBag.members = db.Members.Count().ToString();    

    return View(db.Members.OrderBy(a => Regex.Replace(a.Batch, @"[^\d]", String.Empty)).ThenBy(a=> a.LastName).ToList());
}

I have tried the above reg expression but getting error -

LINQ to Entities does not recognize the method 'System.String Replace(System.String, System.String, System.String)' method, and this method cannot be translated into a store expression.

I have tried this Linq expression but still, throws error -

var members = (from m in db.Members
                      orderby Char.IsDigit(Convert.ToChar(m.Batch)) ascending
                      select m).ToList();

I have tried creating a static function to split the string like below but still not working.

public ActionResult PublicGrid()
{
    ViewBag.members = db.Members.Count().ToString();

    return View(db.Members.OrderBy(a => NumberOnly((a.Batch))).ThenBy(a=> a.LastName).ToList());
}

public static string NumberOnly(string strRaw)
{
    string numbers = string.Empty;

    foreach (char c in strRaw)
    {            
        if (Char.IsNumber(c))
        {
            numbers += c;
        }
    }
    return numbers;
}

Any help much appreciated. Thanks in advance.

Someone mentioned this question is similar to previously asked questions but I could not find any similarity.

Answer 1

Thanks Stephen for pointing the light on my issue. I have to materialize the dataset first then apply the sorting condition. I have changed my code and it seems to be sorting correctly after the change. Here is my code in case if someone faces the similar issue -

public ActionResult PublicGrid()
    {
        ViewBag.members = db.Members.Count().ToString();

        var members = db.Members.ToList();

        return View(members.OrderBy(a => NumberOnly((a.Batch))).ThenBy(a=> a.LastName).ToList());

    }

    public static int NumberOnly(string strRaw)
    {

        string numbers = string.Empty;

        foreach (char c in strRaw)
        {

            if (Char.IsNumber(c))
            {
                numbers += c;
            }
        }

        return Int32.Parse(numbers);
    }

Answer 2

Disclaimer: I didn't try it with a real EF model and context. But both Substring and Length are supported by query providers

Also, if you are going to do Skip and Take after the OrderBy, it may be better to let the SQL engine deal with it... otherwise you have to materialize ALL results to sort in memory (just a blind guess)

Notice that this works because I'm assuming suffix has a fixed length (which is the only interesting part about the suffix... one could as well just declare suffixLength = 8)

var batchSuffix = "xx Batch";

// Use db.Members instead of this
var query = new List<Member>
{
    new Member("1st Batch"), new Member("2nd Batch"), new Member("3rd Batch"), new Member("38th Batch"),
    new Member("12nd Batch"), new Member("23rd Batch"), new Member("324th Batch"), new Member("32th Batch")
}.AsQueryable();

var result = query
    .Select(m => new
    {
        Member = m,
        BatchNumber =  m.Batch.Substring(0, m.Batch.Length - batchSuffix.Length)
    })
    .OrderBy(m => m.BatchNumber.Length)
    .ThenBy(m => m.BatchNumber)
    .Select(m => m.Member)
    .ToList();

Answer 3

While your solution may work, I see some room for improvement:

NumberOnly

• char.IsNumber checks for a unicode category which contains more characters than '0'..'9', so parsing may fail in general.
• If the first character is not a number, you parse an empty string -> exception.
• Building a string character by character just to parse it as an int is inefficient.
• Would prefer to name it to something more specific.

What about this?

private static int IntPrefix(string s) => s
    .TakeWhile(ch => ch >= '0' && ch <= '9')
    .Aggregate(0, (a, c) => 10 * a + (c - '0'));

member ordering

• You materialize a list, then enumerate that list for sorting (which internally has to also materialize it), then build another list; we see way too many .ToList()s on here.
• The NumberOnly may be called many times on a single entity during the sort; better to precalculate that.

The sorted list can be expressed like this:

db.Members
    .AsEnumerable()
    .Select(m => new { Member = m, BatchNo = IntPrefix(m.Batch) })
    .OrderBy(mb => mb.BatchNo)
    .ThenBy(mb => mb.Member.LastName)
    .Select(mb => mb.Member)
    .ToList();