Link To Entities, how to fix Startswith resolving to sql CHARINDEX and ignoring index

I have the following model

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; }

}

I’m calling this function

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

The above is trying to reproduce the following sql

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

However it’s generating the following nasty piece of work:

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'

As you can see, it’s generating CHARINDEX for the StartsWith.

The problem is this is ignoring all the indexing I have setup, and takes many seconds to run on 5 million records.

How could I take advantage of Link To Entities, and have my above requirements satisfied including the indexes?

The only thing I can think of is to use a stored procedure, or a raw SQL statement, which defeats the whole entity thing.

Answer

The only suitable solution i have found so far is the following

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