正则表达式替换以在LINQ中协助Orderby [英] Regex Replace to assist Orderby in LINQ

问题描述

我正在使用LINQ to SQL从数据库中提取记录，按字符串字段对它们进行排序，然后对它们执行一些其他工作.不幸的是，我排序所依据的名称"字段像这样从数据库中出来

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

我想按数字顺序对名称"字段进行排序.现在，我正在使用Regex对象将"ADAPT1"替换为"ADAPT01"，等等.然后，我使用另一个LINQ查询再次对记录进行排序.我为此提供的代码看起来像

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
    }

我的问题是foreach循环引发异常

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"

我还想知道是否只有一种LINQ查询有一种更干净的方法来做到这一点.任何建议，将不胜感激.

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

推荐答案

通过枚举查询(调用ToList)来强制元素的水化.从那时起，您的操作将针对内存对象，并且这些操作将不会转换为SQL.

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();

这篇关于正则表达式替换以在LINQ中协助Orderby的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！