The Entity Framework extension supports the complex filtering condition of multiple keyword fuzzy matching
- 2020-05-19 04:33:17
- OfStack
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
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:
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.
Usage:
Create extension methods to make the call simple
Monitor the generated SQL statements through SQL Profile
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)