Linq to sql startwith with indexed performance columns

Question

First I use entity code. Indexed Columns:

• SourceCatalogId
• Off
• CategoryPath

40,000 rows in the table,

My query problem takes 40 seconds!

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 through sql proxy:

 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 where clause, if I delete " escape N '' ~ '' in:

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

request takes 4 s.

This is normal? Does the index use? How can I solve this with startWith?

EDIT

Attribute pointer for categoryPath:

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

EDIT2

Good thing I'm pretty close, I think the problem is a 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 a variable in a query to retrieve a stored procedure, if I use const, I select select.

@ P__linq__0 appears in the stored procedure (the generated entity), so add ESCAPE N '~' to avoid wildCaractere in the variable.

So now the question is simpler. How to avoid query with variable? Maybe? thanks

Answer 1

So what you need to do is take the value of the variable and use it as a constant in Expression that you create. This is actually quite possible. We will need an expression that takes the parameter that you want as the parameter of your real selector, a second parameter that is a placeholder for the constant value, and then the value that you want to be a constant. Then we can replace all instances of the parameter with a constant value, leaving only 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]); } 

It depends on the following methods of 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(); 

In it:

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