sql - 链接到实体，如何修复开始解析为 sql CHARINDEX 并忽略索引

Question

我有以下模型

public class Exchange
{
    public int Id { get; set; }

    [Index("ExchangeIdx", 1)]
    public int ExchangeSetId { get; set; }

    [Required]
    [MaxLength(10)]
    [Index("ExchangeIdx", 2)]
    public string BasePrefix { get; set; }

    [Required]
    [MaxLength(10)]
    [Index("ExchangeIdx", 3)]
    public string DestPrefix { get; set; }

}

我正在调用这个函数

var exchange = context.Exchanges.FirstOrDefault(x => 
   x.ExchangeSetId == exchangeSetId && 
   x.BasePrefix.StartsWith(baseNumber.Substring(4)) && 
   baseNumber.StartsWith(x.BasePrefix) &&
   destNumber.StartsWith(x.DestPrefix));

上面试图重现下面的sql

SELECT TOP 1 * FROM Exchanges 
   where ExchangeSetId = 1 
   and BasePrefix like left('0732055827', 4) + '%' 
   and '0732055827' like BasePrefix +'%' 
   and '0732612680' like DestPrefix +'%'

但是它生成了以下令人讨厌的工作:

exec sp_executesql N'SELECT TOP (1) 
    [Extent1].[Id] AS [Id], 
    [Extent1].[ExchangeSetId] AS [ExchangeSetId], 
    [Extent1].[BasePrefix] AS [BasePrefix], 
    [Extent1].[DestPrefix] AS [DestPrefix]
    FROM [dbo].[Exchanges] AS [Extent1]
    WHERE ([Extent1].[ExchangeSetId] = @p__linq__0) 
    AND (( CAST(CHARINDEX(SUBSTRING(@p__linq__1, 4 + 1, ( CAST(LEN(@p__linq__1) AS int)) - 4), [Extent1].[BasePrefix]) AS int)) = 1) 
    AND (( CAST(CHARINDEX([Extent1].[BasePrefix], @p__linq__2) AS int)) = 1) AND (( CAST(CHARINDEX([Extent1].[DestPrefix], @p__linq__3) AS int)) = 1)',N'@p__linq__0 int,@p__linq__1 nvarchar(4000),@p__linq__2 nvarchar(4000),@p__linq__3 nvarchar(4000)',@p__linq__0=1,@p__linq__1=N'0732055827',@p__linq__2=N'0732055827',@p__linq__3=N'0732612680'

如您所见，它正在为 StartsWith 生成 CHARINDEX。

问题是这忽略了我设置的所有索引，并且需要很多秒才能在 500 万条记录上运行。

我如何利用链接到实体，并满足我的上述要求(包括索引)？

我唯一能想到的就是使用存储过程或原始 SQL 语句，这会破坏整个实体。

Answer 1

到目前为止我找到的唯一合适的解决方案如下

var exchange = context.Database.SqlQuery<Exchange>(
    "SELECT TOP 1 * FROM Exchanges where ExchangeSetId = @ExchangeSetId and BasePrefix like left(@BaseNumber, 3) + '%' and @BaseNumber like BasePrefix +'%' and @DestNumber like DestPrefix +'%'",
    new SqlParameter("ExchangeSetId", exchangeSetId),
    new SqlParameter("AreaCode", areaCode),
    new SqlParameter("BaseNumber", baseNumber.Substring(2)),
    new SqlParameter("DestNumber", destNumber.Substring(2))).Select(x => new ExchangeViewModel(x)).FirstOrDefault();