Dynamic Entity Framework query: dynamic query Introduction

Address: http://www.cnblogs.com/yinzixin/archive/2012/11/30/entity-framework-dynamic-query.html

Dynamic query is a database that supports dynamic Entity Framework queries. It was designed to reduce the development workload for scenarios such as searching, sorting, and paging a dataset in the management system. Its Design Concept is "markup is code ". By writing tags with semantic information on the view, this type of common functional requirements can be achieved without the need for additionalCode. It is not a complete orm and is based on the Entity Framework. Therefore, developers can still use a large number of features provided by the Entity Framework to maintain compatibility with existing EF projects.

Dynamic query is divided into two parts, one of which is a query interface, which is an iqueryable <t> extension method:

 
Public staticIqueryable<T> query <t> (ThisIqueryable<T> data,QuerydescriptorDescriptor)
 

 

Querydescriptor is a simple class that contains the necessary information for a query, such as filtering conditions, sorting information, and paging information.

For example:

QuerydescriptorDescriptor =NewQuerydescriptor{Orderby =NewOrderbyclause{Key ="Price", Order =Ordersequence. ASC}, pagesize = 3, pageindex = 1, conditions =NewQuerycondition[] {NewQuerycondition{Key ="Name", Value ="Rice", Operator =Queryoperator. Contains }}};IntPagecount; var res = CTX. Products. Query (descriptor,OutPagecount );
 

This is equivalent to executing the query:

Select*FromProductWhere[Name]LikeN'% rice %'OrderPriceASC
 

The results are displayed by page. Three data entries are displayed on each page, and the first page is returned. Note that the returned result here is iqueryable <t>, which is actually an Entity Framework query. Before serialization, database operations are not performed, and paging occurs on the server, this can greatly reduce network transmission and memory usage for big data. Of course, manual construction of such a querydescriptor is also very boring. Therefore, dynamic query also implements a series of helper methods and a model Binder for Asp.net MVC to automatically generate querydescriptor. Our final goal is to obtain the data submitted by the page and automatically generate the querydescriptor object. Therefore, you need to register a custom binder and add a line of code in application_start of Global. asax:

 
Modelbinders. Binders. Add (Typeof(Querydescriptor),NewQuerydescriptorbinder());
 

 

Suppose we have the following EF model:

Let's implement a list to filter the product name. In this case, you can use the querytextbox extension method to generate a query field. The view code is as follows:

 <  Div  Class  = "Container"> <  Form  Class  = ". Form-search">  @  Html. querytextbox (  "Name"  ,  "Product name"  ,  Queryoperator  . Contains)  <  Input  Type  = "Submit"  Value  = "Search"  Class  = "BTN"/> </  Form > </  Div  > <  Div  Class  = "Row"> <  Div  Class  = "Span12 offset2"> <  Table  Class  = "Table-striped"> <  Thead  > <  Tr  > <  TD  >  ID  </  TD  > <  TD  >  Category  </ TD  > <  TD  >  Name  </  TD  > <  TD  >  Price  </  TD  > <  TD  >  Description  </  TD  > </  Tr  > </  Thead  > <  Tbody  >  @ Foreach  (  VaR  P  In  @ Model ){  <  Tr  > <  TD  >  @  P. ID  </  TD  > <  TD  >  @  P. Category. Name  </  TD  > <  TD  >  @  P. Name  </ TD  > <  TD  >  @  P. Price  </  TD  > <  TD  >  @  P. Description  </  TD  > </  Tr  >  }  </  Tbody  > </  Table  > </  Div  > </  Div > 

 

The upper part is a form with a querytextbox, and the lower part is a list, which is very simple. View the corresponding action:

 
PublicActionresultIndex (QuerydescriptorDescriptor ){ShopcontainerCTX =NewShopcontainer();VaRResult = CTX. Products. Query (descriptor );ReturnView ("Product", Result );}
 

Because the model binder exists, the action will obtain the querydescriptor information from the page. This screening page is ready. If the customer says that I want to add a filter for the category name and price range, what should I change?You only need to add several querytextbox items to the view form..

 <  Form  Class  = ". Form-search">  @  Html. querytextbox ( "Name"  ,  "Product name"  ,  Queryoperator  . Contains)  @  Html. querytextbox (  "CATEGORY. Name"  ,  "Product category"  ,  Queryoperator  . Contains)  @  Html. querytextbox (  "Price.1"  ,  "Price"  ,  Queryoperator  . Greaterorequal,  "Decimal"  )  @  Html. querytextbox ( "Price.2"  ,  ""  ,  Queryoperator  . Lessorequal,  "Decimal"  )  <  Input  Type  = "Submit"  Value  = "Search"  Class  = "BTN"/> </  Form  > 

Note: The price is displayed twice. You need to add a numeric suffix to differentiate it. If it is not of the string type, add the description of the type, and then OK. The action method does not need to be modified.

What if the customer says this is going to be paged? Two more lines of code are required for paging, but it only takes 2 minutes. First, let's look at the action method:

  Public   actionresult   product (  querydescriptor   descriptor) {descriptor. pagesize = 5; descriptor. orderby =   New   orderbyclause   {key =   "ID" 
    , order =   ordersequence  . ASC };   shopcontainer   CTX =   New   shopcontainer   ();   int   pagecount;   var   result = CTX. products. query (descriptor,   out   pagecount );   pager =   New   pager   (pagecount, descriptor); viewbag. pager = pager;   return   View (  "Index"  , result) ;} 

First, specify the number of items displayed on a page. The order information must be displayed by page. Here, the information is sorted by ID and ascending order. Next, call the query method to obtain data. Note that this is an overloaded query method that returns the total number of pages. This is generally the information required by the paging control. Next, instantiate a pager object. This pager is a page splitter that is contained in dynamic query. If you want to use another third-party page splitter, you can also. The additional information required by pager is the total number of pages. Put this pager on the viewbag, and then the action is completed. The view does not require any changes. If you want to add a paging link, you only need a line of code:

 
@Html. querypager ((Pager) Viewbag. pager );
 

See the results:

 

Project homepage andSource codeIn: http://dynamicquery.codeplex.com/There is an example aboveProgram. More documents are under improvement.

It was not long before this project started. There are still many details to be improved, mainly style and support for more controls, such as checkbox and dropdownlist. I will try again later.