Before encountering a tricky LINQ to EF query for technical issues, the existing product table product, which needs to match the product name based on multiple keyword blur, is now shared with the solution.
Problem Description
According to the requirements, we need to write the following SQL statement to query the product
Copy Code code as follows:
SELECT * FROM dbo. Product
where
(ProductName like ' product1% ' or
ProductName like ' product2% ')
How do you translate the above SQL statements into EF?
Programme I
You can use union to convert the above SQL statements into the following form:
Copy Code code as follows:
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 hit SQL with LINQ to EF, which is not posted. But each condition must write a query, the workload is big. If you have too many conditions, the resulting SQL statement is also very large and laborious to write.
Programme two
We are inspired by the contains functionality of LINQ to EF, and LINQ to EF converts contains into an in expression.
So can we write the expression directly and convert the condition into the above SQL statement? The answer is yes. The following is a specific LINQ to EF extension to achieve the above scenario.
Copy Code code 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 Code code 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 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 an extension method to make the call simple
Copy Code code 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 ();
}
To monitor the generated SQL statements through SQL profile
Copy Code code as follows:
--Region Parameters
DECLARE @p0 NVarChar (3) = ' p1% '
DECLARE @p1 N VarChar (3) = ' p2% '
--endregion
SELECT [t0].[ Id], [t0]. [ProductName]
from [Product] as [t0]
WHERE ([t0].[ ProductName] Like @p0) OR ([t0].[ ProductName] like @p1)
