The Entity Framework extension supports the complex filtering condition of multiple keyword fuzzy matching

Previously, I encountered a difficult technical problem of Linq to EF query. The existing product table Product needs to match the product name vaguely according to multiple keywords. Now I will share the solution.

Problem description
Depending on the requirements, we need to write the following SQL statement to query the product

 
select * from dbo.Product 
where 
(ProductName like 'Product1%' or 
ProductName like 'Product2%') 

How do I convert the above SQL statement to EF?
Plan 1
You can use Union to convert the above SQL statements into the following:

 
select * from dbo.Product 
where 
ProductName like 'Product1%' 
UNION 
select * from DocutapCMS.dbo.Product 
where 
ProductName like 'Product2%' 

Then it's very easy to replace the SQL on the road with Linq To EF, and I won't post it here. But you have to write an Query for each condition, which is a lot of work. If there are too many conditions, the resulting SQL statement is also very large and laborious to write.

Scheme 2
We took a cue from Linq To EF's Contains function, which converts Contains to IN expressions.
So can we just write Expression and convert the condition to the SQL statement above? The answer is yes. The following are the specific Linq To EF extensions that implement the above scheme.

 
public static Expression<Func<TElement, bool>> BuildContainsExpression<TElement, TValue>(Expression<Func<TElement, TValue>> valueSelector, 
　　IEnumerable<TValue> values) 
{ 
　　var startsWithMethod = typeof (string).GetMethod("StartsWith", new[] { typeof(string) }); 
　　var startWiths = values.Select(value => (Expression)Expression.Call(valueSelector.Body, startsWithMethod, Expression.Constant(value, typeof(TValue)))); 
　　var body = startWiths.Aggregate<Expression>(((accumulate, equal) => Expression.Or(accumulate, equal))); 
　　var p = Expression.Parameter(typeof(TElement)); 
　　return Expression.Lambda<Func<TElement, bool>>(body, p); 
} 

Usage:

 
private static void QueryProducts(IQueryable<Product> query) 
{ 
var productNames = new string[] {"P1", "P2"}; 
var query1 = from a in query.Where(BuildContainsExpression<Product, string>(d=>d.ProductName, productNames)) 
select a; 
var items2 = query1.ToList(); 
} 
private static void QueryProducts(IQueryable<Product> query) 
{ 
var productNames = new string[] {"P1", "P2"}; 
var query1 = from a in query.Where(BuildContainsExpression<Product, string>(d=>d.ProductName, productNames)) 
select a; 
var items2 = query1.ToList(); 
} 

Create extension methods to make the call simple

 
public static IQueryable<TElement> WhereOrLike<TElement, TValue>(this IQueryable<TElement> query, 
　　Expression<Func<TElement, TValue>> valueSelector, IEnumerable<TValue> values) 
{ 
return query.Where(BuildContainsExpression<TElement, TValue>(valueSelector, values)); 
} 
private static void QueryProducts2(IQueryable<Product> query) 
{ 
var productNames = new string[] {"P1", "P2"}; 
query.WhereOrLike(d=>d.ProductName, productNames).ToList(); 
} 

Monitor the generated SQL statements through SQL Profile

 
-- Region Parameters 
DECLARE @p0 NVarChar(3) = 'P1%' 
DECLARE @p1 NVarChar(3) = 'P2%' 
-- EndRegion 
SELECT [t0].[Id], [t0].[ProductName] 
FROM [Product] AS [t0] 
WHERE ([t0].[ProductName] LIKE @p0) OR ([t0].[ProductName] LIKE @p1)