Create an elegant LINQ to SQL dynamic query

First, let's take a look at a typical query form.

This scenario is very simple: the customer name, order date, and owner are used as the filter conditions, and then find the order that meets the requirements.

In those distant times, you may not need to write such a simple interface:

Public interface iorderservice
{
Ilist <order> Search (string
Customer, datetime datefrom,
Datetime dateto, int employeeid );
}

How to implement it, storage process, and ORM framework. It is assumed that the stored procedure started in childhood is used:

Create procedure usp_searchorder @ customer nvarchar (20), @ datefrom datetime, @ dateto datetime, @ employeeid INTAS/* save several hundred lines of SQL statements */

Next, write an orderservice class to implement iorderservice, call the above stored procedure, and write a few lines of code to "Rest assured. However, the nightmare began.

The customer's needs are constantly changing. After a while, the engineer who designed this interface was first praised, and then said that the customer needed to add more "one" filter condition. The engineer may also think about this. Add a filter condition "Nothing more" to add a parameter to the interface, a parameter to the stored procedure, and a condition to the WHERE clause ,...... Working hard.

The customer's filtering condition is as follows: since the order contains the "country" field, you want to filter the condition according to the country, and you can select multiple countries, for example:

The engineer may fall down when he sees the figure ......

The above can be used as a joke, but in other words, there is no universal query framework, such an interface alone

Public interface iorderservice
{
Ilist <order> Search (string
Customer, datetime datefrom,
Datetime dateto, int employeeid );
}

Is unable to adapt to the demand changes.

In the era without LINQ, SQL "Strong Man" tries to end the pain of the stored procedure by concatenating strings,

Ilist <order> Search (string
Sqlqurey );

The results entered another era of "SQL injection, currently, few websites can be easily injected. If there is a hardship, there is a motivation to move forward ).

When I came to the time of writing to SQL (I had to admire the full potential of the query by using LINQ), some of my friends would be able to solve the query problem easily with the LINQ expression:

Ilist <order> Search (expression <func <order,
Bool> expression );

Query statement:

Expression <func <order,
Bool> Expression = c =>
C. Customer. contactname. Contains (txtcustomer. Text )&&
C. orderdate> = datetime. parse (txtdatefrom. Text) & C. orderdate <=
Datetime. parse (txtdateto. Text )&&
C. employeeid = int. parse (ddlemployee. selectedvalue );

And then "sleeping" again ". I can't even wake up.

The customer has a new requirement: add the "all" option in the owner's drop-down box. If "all" is selected, search for the order related to all owners.

The engineer brushed a few times, added if else, and added and to assemble expression. Then, new requirements were introduced ,...... Finally, expression is very bloated (of course this story is a bit exaggerated ).

Why is the use of "advanced" tools still falling into the sea of terrible code? Because Microsoft only provides us with a "fishing rod ". This kind of fishing rod can catch things whether in the river or in the sea, and whether you are fishing for sharks or whales, it also guarantees that the fishing rod will not be broken. But some people can catch big fish, while others get a pair of slippers. Because the key bait is useless. That is to say, Microsoft has given us a powerful LINQ expression, but it is not just a matter of getting to the presentation layer. encapsulation is the final principle.

As a result, the call is coming outQuerybuilderHalf cover:

VaR querybuilder = querybuilder. Create <order> ()
. Like (C => C. Customer. contactname, txtcustomer. Text)
. Between (C => C. orderdate, datetime. parse (txtdatefrom. Text ),
Datetime. parse (txtdateto. Text ))
. Equals (C => C. employeeid, Int. parse (ddlemployee. selectedvalue ))
. In (C => C. shipcountry, selectedcountries );

In this way, the code is much refreshed and the logic is very clear. Even those who do not understand the LINQ expression can understand what these statements do, because their semantics is basically the same as that of SQL:

WHERE ([t1].[ContactName] LIKE '%A%') AND (([t0].[OrderDate]) >= '1/1/1990 12:00:00 AM') AND (([t0].[OrderDate]) <= '9/25/2009 11:59:59 PM') AND(([t0].[EmployeeID]) = 1) AND ([t0].[ShipCountry] IN ('Finland', 'USA', 'UK'))

 

For those who use querybuilder, he thinks it is great because he understands what fish are used for fishing, like for fuzzy query, between for range ,......

For those who write this querybuilder, they also feel very comfortable, because they love to write general-purpose code, just like Lao Zhao from the blog Park.

Smart people naturally come up with an approximate implementation method when they see how they are used. It is like a cook who has eaten food cooked by others. Naturally, I know how to cook it.

The implementation method is not difficult. Here is a brief description:

Querybuilder. Create () returnsIquerybuilderInterface, whileIquerybuilderThere is only one interface
ExpressionAttribute:

///
/// Creator of the dynamic query Condition
///
///
Public interface iquerybuilder
{
Expression <funcbool> Expression {
Get; set ;}
}

ThereforeLike, between, equals, inYou can use this expression for unlimited extension.

The following is the implementationLikeExtension Method:

///
/// Create like (Fuzzy) query Conditions
///
/// Entity
/// Creator of the dynamic query Condition
/// Attributes
/// Query value
///
Public static iquerybuilder like (this
Iquerybuilder Q,
Expression <funcstring> property,
String Value)
{
Value = value. Trim ();
If (! String. isnullorempty (value ))
{
VaR parameter = property. getparameters ();
VaR constant = expression. Constant ("%"
+ Value + "% ");
Methodcallexpression methodexp =
Expression. Call (null,
Typeof (sqlmethods). getmethod ("like ",
New Type [] {
Typeof (string), typeof (string)}), property. Body, constant );
Expression <funcbool> Lambda =
Expression. Lambda <funcbool> (methodexp, parameter );

Q. Expression = Q. expression. And (lambda );
}
Return Q;
}

Each method modifies the expression and returns the modified expression to implement chained programming.

What I mean a little bit isIn(This cost me a lot of time, and may last 4 hours ):

///
/// Create in query Conditions
///
/// Entity
/// Creator of the dynamic query Condition
/// Attributes
/// Query value
///
Public static iquerybuilder in (this
Iquerybuilder Q,
Expression <func> property,
Params P [] values)
{
If (values! = NULL
& Amp; values. Length & gt; 0)
{
VaR parameter = property. getparameters ();
VaR constant = expression. Constant (values );
Type type = typeof (P );
Expression nonnullproperty = property. Body;
// If it is nullable type, convert it to X type
If (isnullabletype (type ))
{
Type = getnonnullabletype (type );
Nonnullproperty = expression. Convert (property. Body, type );
}
Expression <funcbool> inexpression = (list, El) => list. Contains (EL );
VaR methodexp = inexpression;
VaR invoke = expression. Invoke (methodexp, constant, property. Body );
Expression <funcbool> Lambda =
Expression. Lambda <funcbool> (invoke, parameter );
Q. Expression = Q. expression. And (lambda );
}
Return Q;
}

If you are interested, you can download the source code at the end of the article and see the other two extension methods.

Well, it seems that it is time to exit again. What? You said onlyLike, between, equals, inNot enough? Oh, you can expand it yourselfIquerybuilderYou can try your best.

Later I made a "strange" extension for another project:

For example, we know that in the print settings, we can directly write the number of pages to filter which pages to print-1, 4, 9, or 1-8.

This raises the following requirements: