Extended Entity Framework supports complex filtering conditions (multiple keyword fuzzy matching)

I encountered a difficult technical problem with Linq to EF query before. The existing product table Product needs to fuzzy match the product name based on multiple keywords. Now I will share the solution.

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

Copy the codeThe code is as follows:

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

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

Copy the codeThe code is as follows:

 
select * from  
where 
ProductName like 'Product1%' 
UNION 
select * from  
where 
ProductName like 'Product2%' 

Then it is very simple to replace the on-road SQL with Linq To EF, and I won’t post it anymore. But writing a Query for each condition is a lot of work. If there are too many conditions, the generated SQL statements are also very large and it is very laborious to write.

Plan 2
We are inspired by the Contains function of Linq To EF, which converts Contains into IN expressions.
So can we write Expression directly and convert the conditions into the above SQL statement? The answer is yes. The following is the specific Linq To EF extension to implement the above solution.

Copy the codeThe code is as follows:

 
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 = (value => (Expression)(, startsWithMethod, (value, typeof(TValue)))); 
var body = <Expression>(((accumulate, equal) => (accumulate, equal))); 
var p = (typeof(TElement)); 
return <Func<TElement, bool>>(body, p); 
} 

usage:

Copy the codeThe code is as follows:

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

Create extension methods to make calling simple

Copy the codeThe code is as follows:

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

Monitor generated SQL statements through SQL Profile

Copy the codeThe code is as follows:

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