Regex Replace to assist Orderby in LINQ

Question

I'm using LINQ to SQL to pull records from a database, sort them by a string field, then perform some other work on them. Unfortunately the Name field that I'm sorting by comes out of the database like this

Name
ADAPT1
ADAPT10
ADAPT11
...
ADAPT2
ADAPT3

I'd like to sort the Name field in numerical order. Right now I'm using the Regex object to replace "ADAPT1" with "ADAPT01", etc. I then sort the records again using another LINQ query. The code I have for this looks like

    var adaptationsUnsorted = from aun in dbContext.Adaptations
                              where aun.EventID == iep.EventID
                              select new Adaptation
                              {
                                  StudentID = aun.StudentID,
                                  EventID = aun.EventID,
                                  Name = Regex.Replace(aun.Name,
                                     @"ADAPT([0-9])$", @"ADAPT0$1"),
                                  Value = aun.Value
                              };

    var adaptationsSorted = from ast in adaptationsUnsorted
                            orderby ast.Name
                            select ast;

    foreach(Adaptation adaptation in adaptationsSorted)
    {
        // do real work
    }

The problem I have is that the foreach loop throws the exception

System.NotSupportedException was unhandled
  Message="Method 'System.String Replace(System.String, System.String,
    System.String)' has no supported translation to SQL."
  Source="System.Data.Linq"

I'm also wondering if there's a cleaner way to do this with just one LINQ query. Any suggestions would be appreciated.

Answer 1

Implement a IComparer<string> with your logic:

var adaptationsUnsorted = from aun in dbContext.Adaptations
                          where aun.EventID == iep.EventID
                          select new Adaptation
                          {
                              StudentID = aun.StudentID,
                              EventID = aun.EventID,
                              Name = aun.Name,
                              Value = aun.Value
                          };

var adaptationsSorted = adaptationsUnsorted.ToList<Adaptation>().OrderBy(a => a.Name, new AdaptationComparer ());

foreach (Adaptation adaptation in adaptationsSorted)
{
    // do real work
}

public class AdaptationComparer : IComparer<string>
{
    public int Compare(string x, string y)
    {
        string x1 = Regex.Replace(x, @"ADAPT([0-9])$", @"ADAPT0$1");
        string y1 = Regex.Replace(y, @"ADAPT([0-9])$", @"ADAPT0$1");
        return Comparer<string>.Default.Compare(x1, y1);
    }
}

I didn't test this code but it should do the job.

Answer 2

Force the hydration of the elements by enumerating the query (call ToList). From that point on, your operations will be against in-memory objects and those operations will not be translated into SQL.

List<Adaptation> result =
  dbContext.Adaptation
  .Where(aun => aun.EventID = iep.EventID)
  .ToList();

result.ForEach(aun =>
  aun.Name = Regex.Replace(aun.Name,
    @"ADAPT([0-9])$", @"ADAPT0$1")
);
result = result.OrderBy(aun => aun.Name).ToList();

Answer 3

I wonder if you can add a calculated+persisted+indexed field to the database, that does this for you. It would be fairly trivial to write a UDF that gets the value as an integer (just using string values), but then you can sort on this column at the database. This would allow you to use Skip and Take effectively, rather than constantly fetching all the data to the .NET code (which simply doesn't scale).