c# - linq to sql startwith performance indexed columns

Question

I use entity code first. Indexed columns:

• SourceCatalogId
• Disabled
• CategoryPath

40 000 rows in the Table,

My problem is the query takes 40s!!

var result = DBContext.Set<SourceProduct>()
            .Include(x => x.SalesHistories, x => x.SourceCatalog)
            .Where(p => p.SourceCatalogId == 2)
            .where(p => p.Disabled == false)
            .where(x => x.CategoryPath.StartsWith("MyPath"))
            .orderby(x => x.ShortDesignation)
            .Skip(1)
            .Take(10)
            .toList();

SQL via sql profiler:

exec sp_executesql N'SELECT TOP (10) 
[Project1].[SourceProductId] AS [SourceProductId], 
[Project1].[SourceSKU] AS [SourceSKU], 
[Project1].[SourceCatalogId] AS [SourceCatalogId], 
[Project1].[ManufacturerReference] AS [ManufacturerReference], 
[Project1].[Disabled] AS [Disabled], 
[Project1].[EAN] AS [EAN], 
[Project1].[ShortDesignation] AS [ShortDesignation], 
[Project1].[FullDesignation] AS [FullDesignation], 
[Project1].[Description] AS [Description], 
[Project1].[Url] AS [Url], 
[Project1].[CategoryPath] AS [CategoryPath], 
[Project1].[Condition] AS [Condition], 
[Project1].[BuyingPriceHT] AS [BuyingPriceHT], 
[Project1].[ShippingPriceHT] AS [ShippingPriceHT], 
[Project1].[PublicSellingPriceHT] AS [PublicSellingPriceHT], 
[Project1].[PictureUrl1] AS [PictureUrl1], 
[Project1].[PictureUrl2] AS [PictureUrl2], 
[Project1].[PictureUrl3] AS [PictureUrl3], 
[Project1].[PictureUrl4] AS [PictureUrl4], 
[Project1].[Quantity] AS [Quantity], 
[Project1].[AddDate] AS [AddDate], 
[Project1].[UpdateDate] AS [UpdateDate], 
[Project1].[Followers] AS [Followers]
FROM ( SELECT [Project1].[SourceProductId] AS [SourceProductId], [Project1].[SourceSKU] AS [SourceSKU], [Project1].[SourceCatalogId] AS [SourceCatalogId], [Project1].[ManufacturerReference] AS [ManufacturerReference], [Project1].[Disabled] AS [Disabled], [Project1].[EAN] AS [EAN], [Project1].[ShortDesignation] AS [ShortDesignation], [Project1].[FullDesignation] AS [FullDesignation], [Project1].[Description] AS [Description], [Project1].[Url] AS [Url], [Project1].[CategoryPath] AS [CategoryPath], [Project1].[Condition] AS [Condition], [Project1].[BuyingPriceHT] AS [BuyingPriceHT], [Project1].[ShippingPriceHT] AS [ShippingPriceHT], [Project1].[PublicSellingPriceHT] AS [PublicSellingPriceHT], [Project1].[PictureUrl1] AS [PictureUrl1], [Project1].[PictureUrl2] AS [PictureUrl2], [Project1].[PictureUrl3] AS [PictureUrl3], [Project1].[PictureUrl4] AS [PictureUrl4], [Project1].[Quantity] AS [Quantity], [Project1].[AddDate] AS [AddDate], [Project1].[UpdateDate] AS [UpdateDate], [Project1].[Followers] AS [Followers], row_number() OVER (ORDER BY [Project1].[ShortDesignation] ASC) AS [row_number]
    FROM ( SELECT 
        [Extent1].[SourceProductId] AS [SourceProductId], 
        [Extent1].[SourceSKU] AS [SourceSKU], 
        [Extent1].[SourceCatalogId] AS [SourceCatalogId], 
        [Extent1].[ManufacturerReference] AS [ManufacturerReference], 
        [Extent1].[Disabled] AS [Disabled], 
        [Extent1].[EAN] AS [EAN], 
        [Extent1].[ShortDesignation] AS [ShortDesignation], 
        [Extent1].[FullDesignation] AS [FullDesignation], 
        [Extent1].[Description] AS [Description], 
        [Extent1].[Url] AS [Url], 
        [Extent1].[CategoryPath] AS [CategoryPath], 
        [Extent1].[Condition] AS [Condition], 
        [Extent1].[BuyingPriceHT] AS [BuyingPriceHT], 
        [Extent1].[ShippingPriceHT] AS [ShippingPriceHT], 
        [Extent1].[PublicSellingPriceHT] AS [PublicSellingPriceHT], 
        [Extent1].[PictureUrl1] AS [PictureUrl1], 
        [Extent1].[PictureUrl2] AS [PictureUrl2], 
        [Extent1].[PictureUrl3] AS [PictureUrl3], 
        [Extent1].[PictureUrl4] AS [PictureUrl4], 
        [Extent1].[Quantity] AS [Quantity], 
        [Extent1].[AddDate] AS [AddDate], 
        [Extent1].[UpdateDate] AS [UpdateDate], 
        [Extent1].[Followers] AS [Followers]
        FROM [dbo].[SourceProducts] AS [Extent1]
        WHERE ([Extent1].[SourceCatalogId] = @p__linq__0) AND (0 = [Extent1].[Disabled]) AND ([Extent1].[CategoryPath] LIKE @p__linq__1 ESCAPE N''~'')
    )  AS [Project1]
)  AS [Project1]
WHERE [Project1].[row_number] > 0
ORDER BY [Project1].[ShortDesignation] ASC',N'@p__linq__0 bigint,@p__linq__1 nvarchar(4000)',@p__linq__0=2,@p__linq__1=N'MyPath%'

In the last one before where clause, if I remove "escape N''~''" in:

WHERE ([Extent1].[SourceCatalogId] = @p__linq__0) AND (0 = [Extent1].[Disabled]) AND ([Extent1].[CategoryPath] LIKE @p__linq__1 ESCAPE N''~'')

the query takes 4s.

Is it normal ? Index uses ? How i can solve it with startWith ?

EDIT

Index attribut for categoryPath:

[Index("IX_SourceProduct_SourceCatalogId_Disabled_CategoryPath", 3), StringLength(400)]
    public string CategoryPath { get; set; }

EDIT2

OK i thing that I'm pretty close, I think the probleme is stored procedure.

string search = "julien";
            var list = db.Users.Where(x => x.Name.StartsWith(search));
            string query = list.ToString();

=> SELECT [Extent1].[UserId] AS [UserId], [Extent1].[Name] AS [Name] FROM [dbo].[Users] AS [Extent1] WHERE [Extent1].[Name] LIKE @p__linq__0 ESCAPE N'~'

var list2 = db.Users.Where(x => x.Name.StartsWith("julien"));
            string query2 = list2.ToString();

=> SELECT [Extent1].[UserId] AS [UserId], [Extent1].[Name] AS [Name] FROM [dbo].[Users] AS [Extent1] WHERE [Extent1].[Name] LIKE N'julien%'

So if I use variable in the query in get a stored procedure, if I use const I get select.

In the stored procedure( generated by entity) makes appear @p__linq__0 so add ESCAPE N'~' to avoid wildCaractere in the variable.

So now the question is simplier. How avoid query with variable ? it's possible ? thanks

Answer 1

So what you need to do here is take the value of a variable and use it as a constant in an Expression that you are generating. This is actually quite possible. What we'll need is an expression that accepts the parameter you want as the parameter of your real selector, as second parameter that is a placeholder for the constant value, and then the value that you want to be a constant. We can then replace all instances of the parameter with the value of the constant, leaving just a function that maps the real parameter to the result:

public static Expression<Func<TSource, TResult>> EmbedConstant
    <TSource, TResult, TConstant>(
    this Expression<Func<TSource, TConstant, TResult>> expression,
    TConstant constant)
{
    var body = expression.Body.Replace(
        expression.Parameters[1],
        Expression.Constant(constant));
    return Expression.Lambda<Func<TSource, TResult>>(
        body, expression.Parameters[0]);
}

This relies on the following methods for replacing all instances of one expression with another:

public static Expression Replace(this Expression expression,
    Expression searchEx, Expression replaceEx)
{
    return new ReplaceVisitor(searchEx, replaceEx).Visit(expression);
}
internal class ReplaceVisitor : ExpressionVisitor
{
    private readonly Expression from, to;
    public ReplaceVisitor(Expression from, Expression to)
    {
        this.from = from;
        this.to = to;
    }
    public override Expression Visit(Expression node)
    {
        return node == from ? to : base.Visit(node);
    }
}

This allows you to map this:

string search = "julien";
var list = db.Users.Where(x => x.Name.StartsWith(search));
string query = list.ToString();

Into this:

string search = "julien";
Expression<Func<User, string, bool>> predicate = 
    (item, searchTerm) => item.Name.StartsWith(searchTerm);
var list = db.Users.Where(predicate.EmbedConstant(search));
string query = list.ToString();