Querybuilder: Create an elegant dynamic query using LINQ to SQL

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 interfaceIorderservice{Ilist<Order> Search (StringCustomer,DatetimeDatefrom,DatetimeDateto,IntEmployeeid );}
 

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

 
Create ProcedureUsp_searchorder
 

 
@ Customer nvarchar (20 ),
 

 
@ Datefrom datetime,
 

 
@ Dateto datetime,
 

@ EmployeeidInt
 

 
As
 

 
/*... Save hundreds of SQL statements below */
 

Next, write a class orderservice to implement iorderservice, call the above stored procedure, and write a few wordsCodeYou can "Sleep. 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 interfaceIorderservice{Ilist<Order> Search (StringCustomer,DatetimeDatefrom,DatetimeDateto,IntEmployeeid );}
 

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 (StringSqlqurey );
 

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:

 
VaRQuerybuilder =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])> ='2014/1/2 12:00:00 am')And([T0]. [orderdate]) <='2014/1/2 11:59:59 PM')And
 

 
([T0]. [employeeid]) = 1)And 
 

 
([T0]. [shipcountry]In('Finand','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 <order> () returnsIquerybuilder <t>Interface, whileIquerybuilder <t>There is only one interfaceExpressionAttribute:

 
/// <Summary> ///Creator of the dynamic query Condition/// </Summary> /// <typeparam name = "T"> </typeparam>Public interfaceIquerybuilder<T> {Expression<Func<T,Bool> Expression {Get;Set;}}
 

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

The following is the implementationLikeExtension Method:

 /// <Summary> /// Create like (Fuzzy) query Conditions  /// </Summary> /// <typeparam name = "T">  Entity  </Typeparam> // <Param name = "Q">  Creator of the dynamic query Condition  </Param> // <Param name = "property">  Attribute  </Param> // <Param name = "value">  Query Value  </Param> /// <returns> </returns>  Public static  Iquerybuilder <T> like <t> ( This Iquerybuilder <T> q, Expression < Func <T, String > 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 < Func <T, Bool > Lambda = Expression . Lambda < Func <T, Bool >>( 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 ):

 /// <Summary> ///  Create in query Conditions /// </Summary> /// <typeparam name = "T">  Entity  </Typeparam> // <Param name = "Q">  Creator of the dynamic query Condition  </Param> // <Param name = "property">  Attribute  </Param> // <Param name = "valuse">  Query Value  </Param> /// <returns> </returns>  Public static  Iquerybuilder <T> in <T, P> ( This  Iquerybuilder <T> q,Expression < Func <T, P> 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 <x> type, convert it to X type  If (Isnullabletype (type) {type = getnonnullabletype (type); nonnullproperty = Expression . Convert (property. Body, type );} Expression < Func <P [], p, Bool > Inexpression = (list, El) => list. Contains (EL ); VaR Methodexp = inexpression; VaR Invoke = Expression . Invoke (methodexp, constant, property. Body ); Expression < Func <T, Bool > Lambda = Expression . Lambda < Func <T, Bool >>( Invoke, parameter); q. Expression = Q. expression. And (lambda );} Return Q ;}

 

If you are interestedArticleLast downloadSource codeSee 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:

Query orders of Bruce and jeffz on the left, and query orders of B until Z on the right.

Also known:Fuzzy, Meaning: ambiguous (Click here to see the meaning of fuzzy)

Summary:

In fact, this article has been brewing for a long time and has gained a lot of programming skills in recent work. It's time to summarize a project. I hope I can continue to share it with you when I have time.

Download source code: coolcode.linq.rar

We recommend that you refer to the new article 《Querybuilder: creates an elegant LINQ to SQL dynamic query (supports EF and. net4)" Refer:

Http://www.cnblogs.com/neuhawk/archive/2007/07/07/809585.html

Http://www.cnblogs.com/billgan/archive/2009/01/08/1371809.html

Http://www.cnblogs.com/lyj/archive/2008/03/25/1122157.html

Http://www.cnblogs.com/126/archive/2007/09/09/887723.html