Extended Entity Framework supports complex filtering conditions (fuzzy match of multiple keywords)

Previously, I encountered a technical problem with the query of a complicated Linq to EF. The Product name of the existing Product table needs to be fuzzy matched Based on Multiple keywords. Now I will share the solution.

Problem description
As required, we need to write the following SQL statement to query the product
Copy codeThe Code is as follows:
Select * from dbo. Product
Where
(ProductName like 'product1% 'or
ProductName like 'product2% ')

How can I convert the preceding SQL statements into EF statements?
Solution 1
You can use Union to convert the preceding SQL statement into the following format:
Copy codeThe Code is as follows:
Select * from dbo. Product
Where
ProductName like 'product1%'
UNION
Select * from DocutapCMS. dbo. Product
Where
ProductName like 'product2%'

Then it is very easy To replace the SQL statement with "Linq To EF", and it will not be posted here. However, a Query is required for each condition, resulting in heavy workload. If there are too many conditions, the generated SQL statement is also very large, and it is very laborious to write.

Solution 2
We are inspired by the Contains function of Linq To EF, which converts Contains To IN expressions.
Can we directly write an Expression to convert the conditions into the preceding SQL statement? The answer is yes. The following describes how To implement the specific extensions of the above scheme.
Copy 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 = 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:
Copy codeThe Code is as follows:
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;
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;
Var items2 = query1.ToList ();
}

Create extension methods to make calls simple
Copy 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 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 ();
}

Use SQL Profile to monitor generated SQL statements
Copy 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)