基于Code First EF的MVC应用：字符串序列号范围查询方案咨询

这个问题我之前在项目里也碰到过，Linq to Entities确实没法直接把字符串里的数字部分转成整数来比较，不过有几个实用的解决方案，你可以根据自己的场景来选：

方案1：利用字符串字典序直接比较（最简单，优先考虑） #

如果你的序列号前缀是固定统一的（比如都是SERIAL-NO-），而且数字部分是固定4位的补零格式（比如0020、0050），那其实可以直接用字符串的大小比较来实现区间查询——因为这种格式的字符串字典序和数字的大小顺序是完全一致的。

代码示例：

var startSerial = "SERIAL-NO-0020";
var endSerial = "SERIAL-NO-0050";

var matchingRecords = db.YourEntityTable
                        .Where(e => e.SerialNumber >= startSerial && e.SerialNumber <= endSerial)
                        .ToList();

这个方案的优点是不需要额外处理，EF能直接把Linq表达式转换成SQL的字符串比较，性能也不错。

方案2：用EF支持的字符串函数提取数字部分比较 #

如果序列号的前缀不固定，但最后4位肯定是数字，那可以用EF提供的字符串函数提取最后4位，再转成数字进行比较。这里分EF6和EF Core两种情况：

针对EF6 #

使用SqlFunctions.Right提取最后4位，再转成decimal比较（EF6里SqlFunctions.StringConvert支持转decimal）：

using System.Data.Entity.SqlServer; // 需要引用这个命名空间

int minNumber = 20;
int maxNumber = 50;

var matchingRecords = db.YourEntityTable
                        .Where(e => 
                            Convert.ToDecimal(SqlFunctions.Right(e.SerialNumber, 4)) >= minNumber
                            && Convert.ToDecimal(SqlFunctions.Right(e.SerialNumber, 4)) <= maxNumber)
                        .ToList();

或者也可以把数字转成4位补零的字符串，直接比较提取出的字符串：

var minStr = minNumber.ToString("D4"); // 转成"0020"
var maxStr = maxNumber.ToString("D4"); // 转成"0050"

var matchingRecords = db.YourEntityTable
                        .Where(e => 
                            SqlFunctions.Right(e.SerialNumber, 4) >= minStr
                            && SqlFunctions.Right(e.SerialNumber, 4) <= maxStr)
                        .ToList();

针对EF Core #

使用EF.Functions.Right提取最后4位，直接转成int比较（EF Core支持把这种转换翻译成SQL的CAST操作）：

int minNumber = 20;
int maxNumber = 50;

var matchingRecords = db.YourEntityTable
                        .Where(e => 
                            Convert.ToInt32(EF.Functions.Right(e.SerialNumber, 4)) >= minNumber
                            && Convert.ToInt32(EF.Functions.Right(e.SerialNumber, 4)) <= maxNumber)
                        .ToList();

方案3：添加数据库计算列（最适合频繁查询的场景） #

如果你的系统经常需要按这个数字部分查询，那最推荐的方式是在数据库里加一个计算列，把序列号的数字部分预计算成整数，然后在EF实体里映射这个列。

步骤1：在数据库中添加计算列 #

执行SQL语句（或者在Code First里用迁移）：

ALTER TABLE YourEntityTable
ADD SerialNumberNumeric AS CAST(RIGHT(SerialNumber, 4) AS INT)

如果需要提高查询性能，还可以给这个计算列加索引：

CREATE INDEX IX_YourEntityTable_SerialNumberNumeric ON YourEntityTable(SerialNumberNumeric)

步骤2：在EF实体中映射计算列 #

using System.ComponentModel.DataAnnotations.Schema;

public class YourEntity
{
    public string SerialNumber { get; set; }

    // 标记为数据库计算列，EF不会尝试插入或更新这个值
    [DatabaseGenerated(DatabaseGeneratedOption.Computed)]
    public int SerialNumberNumeric { get; set; }
}

步骤3：查询时直接使用计算列 #

int minNumber = 20;
int maxNumber = 50;

var matchingRecords = db.YourEntityTable
                        .Where(e => e.SerialNumberNumeric >= minNumber && e.SerialNumberNumeric <= maxNumber)
                        .ToList();

这个方案的优点是查询效率极高，而且代码最简洁，适合频繁做这类查询的场景。

方案4：使用原生SQL查询（兜底方案） #

如果上面的方案都不适用，或者需要更复杂的逻辑，可以直接用原生SQL来查询，EF支持直接执行SQL语句：

int minNumber = 20;
int maxNumber = 50;

var matchingRecords = db.YourEntityTable
                        .FromSqlRaw("SELECT * FROM YourEntityTable WHERE CAST(RIGHT(SerialNumber, 4) AS INT) BETWEEN {0} AND {1}", minNumber, maxNumber)
                        .ToList();

注意这里用{0}和{1}是参数化查询，能避免SQL注入问题，不要直接拼接字符串。

内容的提问来源于stack exchange，提问作者Neill