Dynamic multi-conditional query paging and sorting (i)--MVC and Entity Framework version URL page release

Source: Internet
Author: User

I. Preface

Multi-conditional query paging and sorting each system will have this code to do this can greatly improve the development efficiency so bloggers share their own 6 versions of the multi-conditional query paging and sorting

Two. Current situation

Both the ADO and EF have similar codes for long term searches

There's a couple of bad places.

1. When you add query conditions, you need to change the code, corresponding to write the corresponding code.

2. Support for multi-table queries and or is not very good. And our very common demand cannot be a table of queries

3. This writes the presentation layer directly to the SQL statement or to the lambda Expression of LINQ. The presentation layer should not know the data access technology

4. Sometimes our business logic layer interface is such ilist<***> seach (string name,string age,string classname,int pageindex,int pagesize,string Oderby)

This time more than one query condition corresponding to modify the business logic Layer EF Because the expression tree is passed, it is more miserable.

Three. The next goal we should achieve

1. When adding conditions, you do not need to modify the code only to add the corresponding query box on the view

2. Our multi-conditional queries should be independent of presentation layer technology (MVC or WebForm)

3. Multi-table queries and or operations should be supported

4. More queries should be supported like in not equal operations

5. About paging should not be coupled with data access a personal sense of paging just need to know the total number of pages and the current page and how many pages per page and then the composition of the page code can not be coupled with the EF and other pages should be independent of the control

6. Customers can add their own search conditions it's a powerful feature. How to check the customer to add

7. Unified query interface to achieve conditional increase do not modify the code

8. Paging should support URL rewriting or MVC routes should not generate connections just? pageindex= value of this kind of

Four. I implemented a few multi-conditional query paging version to adapt to a variety of requirements (each will write a version of the implementation and code to provide, there is a good opinion of the welcome message)

1.url get Commit version implement URL page multi-condition query and the advantage of sorting is that we can put the current search criteria, such as the current number of pages in the URL can be easily sent to friends and back browser operations (personal to Dudu boss advice, blog Park should be made this kind of)

2.post submitted version of the search condition is not suitable for the URL of the

3.AJAX+MVC version of (about AJAX implementation I think there are two kinds of 1. Service-side implementation of good content splicing, transmission to the Client 2 direct JSON to the client client to do the stitching)

This version will realize the service side stitching content advantage is that the service side does the stitching simple can do more things to maintain the service-side code convenience especially powerful razor

4.ajax+webapi+knockoutjs version

This version I realized that the server is just passing JSON so that the server is very efficient like this way of development of friends is the front-end splicing character is very bad code will appear very messy this time the front end needs a template engine I use jquery-temp with strong Knockoutjs

5. Dynamically increase the query condition version

This version of my implementation is that customers can add their own query condition query conditions are dynamic

6. Porting to version WebForm

7.EF should get an expression tree so that EF generates SQL statements by itself so it's easy to extend the implementation of other methods

Five. URL get Submit version start

That's a lot of crap. The URL get version of the multi-conditional query and the paging order today

First, the Ugly interface is ugly, but full-featured query pagination sorted

Look at the code of the controller.

We don't have a variety of conditions here to judge which sort of null to use. Our Business logic layer interface does not accept the expression tree's parameters and the data access layer is not coupled but uses the Querymodel object to abstract all query conditions

So that this object can be translated into an EF expression tree can also be translated into SQL statements so our presentation layer MVC does not have to use the bottom of EF

And our page only need to know the current page total data and the size of each page to auto-component pages

About paging many people like to make this extension htmlhelper into Html.pager, so many presentation layers are coupled to this example, such as the layout of the paging from table to ul this is a pure presentation layer

Should have modified the view now to change HtmlHelper and your page code is more powerful HtmlHelper the more content in the change is not easy so my opinion is to do pagination of the best use of HTML. Partial then writes the paging logic to the

Part of the page to realize that paging is only about paging and everything else. What we're going to do is build the pager class and pass it to the template for example

This version should note that after paging to save the query condition

Six.URL get Submit version implementation Analysis

1. First, the multi-criteria query

What we're going to do is build all the conditions into Querymodel and how to build it is the key we want to get the MVC commit content under the name so we can pin a name to the format as

ID (like operation): <input type= "text" name= "[Contains]stuid" value= "@StuId"/>

This way we can get the part of the information we want from the regular and then build the Querymodel object in the model binding and then translate the object into an SQL statement or an expression tree for ADO or EF operations.

And by the way, the idea is that the heavy pawnage in the garden has been realized and achieved well. Everyone can go to his blog to see later versions of the pagination will be built on this expression tree as the basis this is the link of his article heavy pawnage elder brother's realization

(PS: This week just saw him himself, chatted very happily, happy. Hope to be able to get together more later)

But heavy pawnage old brother's expression tree construction I changed to their own implementation of the overall idea or the same narcissism said ~ I'm more readable haha because heavy pawnage has been described in detail can see my code and his article

Of course, we get the expression tree through Querymodel. Call the Where method directly through the extension method so that we can look at the business logic layer of our EF

 public class Studentservice:istudentservice {//<summary>//Search by condition///</summary> <param name= "Query" > Search conditions </param>//<param name= "PageIndex" > Current page (index starting from 1) </param&gt        ; <param name= "PageSize" > show number of bars per page </param>//<param name= "Total" > number of Bars </param>//&L T;param name= "ORDER By" > Sort field </param>//<param name= "Ascending" > whether ascending </param>//<re  Turns></returns> public ilist<student> Search (querymodel query, int pageIndex, int. pageSize, out int Total, string-by-clause, bool ascending) {ilist<student> stulist = Builder<student>. Createlistofsize (321). Thefirst (44). With (x = X.stuname = "hy"). and (x = X.nullint = 1). and (x = X.lovegril = "LILI"). and (x = X.createtime = new DateTime (2012, 02, 03)). and (x = X.birthday = new DateTime (2012, 09, 01)). and (x = X.stuclass = new STUCLass () {ClassId = "2", ClassName = "Class Two"}). Thenext (33). With (x = X.stuname = "Wlf"). and (x = X.nullint = 2). and (x = X.lovegril = "MM"). and (x = X.createtime = new DateTime (2012, 06, 06)). and (x = X.birthday = new DateTime (2012, 09, 010)). and (x = X.stuclass = new Stuclass () {ClassId = "1", ClassName = "One Shift"}). Thenext (244). and (x=>x. Stuclass=new Stuclass () {ClassId = "3", ClassName = "Three Shifts"}).            Build (); var dbcontext = stulist. AsQueryable (); The simulated EF context assumes that the original data in the database is 200 dbcontext = DbContext.            Where (query); Total = DbContext. Count ();//execute query number SQL DbContext = DbContext. (ascending). Skip (PageSize * (pageIndex-1)).            Take (pageSize); Return DbContext. ToList ();//Perform paged sort query SQL}}

Here is the next for debugging convenience without a real database with the test magic NBuilder to simulate and then converted into asqueryable to simulate EF's

Look at the above code can see without the various branches of the condition of the judgment and the judgment of the order after the more out of the query conditions do not need to modify the business logic Layer ~

2. Next, the idea of URL get submission

I asked my opinion. Pagination is best placed on partial pages rather than extended htmlhelper so my implementation is to pass the Build pager Page view class to a partial view to show some of the paging related logic encapsulation in pager

Here is the code for pager

   public class Pager {public Pager (int currentpageindex, int totalitemcount, int pagesize = 20) { This.            Totalitemcount = Totalitemcount; This.            PageSize = PageSize; This. CurrentPageIndex = currentpageindex > Totalpagecount?        1:currentpageindex;         }///<summary>///Current page///</summary> public int CurrentPageIndex {get; set;}        <summary>///page shows how many///</summary> public int PageSize {get; set;}        <summary>///Total number of records///</summary> public int Totalitemcount {get; set;} <summary>//Total number of pages///</summary> public int Totalpagecount {GE                t {double PageCount = (double) totalitemcount/(double) PageSize;                PageCount = math.ceiling (PageCount);            return (int) PageCount;     }        }   <summary>///whether show///</summary> public bool Isshow {get                {if (Totalpagecount > 0) {return true;                } else {return false; }}}///<summary> whether to display previous///</summary> public bool Haspr            Eviouspage {get {return (CurrentPageIndex > 1);        }}////<summary> whether to display the next page///</summary> public bool Hasnextpage            {get {return (CurrentPageIndex < totalpagecount); }        }    }

The partial view of pagination is only used to manage the display of this class.

Here are some tips to share

Technique one.

Because we want to achieve the following requirements

1. We want to save the previous URL information again inside can't let the previous disappear 2 need to support URL routing after paging such as Controller/method/page1 instead? Pageindex=1 3. Because a lot of pages need to be used so to decouple from the method name

The trick to achieving this is to use RouteValueDictionary

View Code

With the code above, we can save the previous parameters and then

dict["PageIndex"] = 1;//Set page number @html.routelink ("Home", dict);

This allows you to set paging and decoupling controllers and method names ~

If you set up a similar route

Routes. MapRoute ("page", "{Controller}/{action}/page{pageindex}", new {controller = "wlfquery", action = "Index", PageIndex = 1} );

So is the support ~

2. Skill Two

Because our paging support through the drop-down box to select page number after the automatic page jump a bit like WebForm's autopostback and we are get to submit version of the need to solve a problem after the selection of auto-skip page needs to take the previous query criteria can not skip the page after the query condition disappears

The method used here draws on the ideas of the Urlpager of the Aspnetpager author.

With a hidden a tag, this a-label generated connection saves the current query condition, and so on his number of pages is displayed as "*pageindex* when we select the dropdown box to skip the page and replace it with the selected value" *pageindex* then jumps to the current href solves the above problem
On the Code

<text> Jump to </text>
<select id= "Pageselect" onchange= "Selectchange ()" >
@for (int i = 1; i <[email protected]; i++)
{
var selected = "";
if (I==model.currentpageindex)
{
selected = "selected= ' selected '";
}
<option value= "@i" @selected >@i</option>
}
</select>

{
dict["PageIndex"] = "*pageindex*";
}
<a style= "Display:none" id= "Pagelink" href= "@Url. ROUTEURL (dict)" ></a>
//
<script type= "Text/javascript" >

function Selectchange () {
var pageselect = document.getElementById ("Pageselect");
var pageselectvalue = Pageselect.options[pageselect.selectedindex].value;

var linkdom= document.getelementbyid ("Pagelink");
var href = linkdom.href;
href = href.replace ("*pageindex*", Pageselectvalue);

window.location = href;

}
</script>

<text> page </text>

3. Click on search to get commit and go back to the first page

Specific code can download the source to see ~

Seven. URL Get submission Regret

The above basic functions have been implemented but it's a pity that you know that MVC has no viewstate mechanism and that our URL paging is WebForm.

Although we saved the search status through the URL but did not resolve the click Paging after the content of the search box is still there. So there's going to be a strange phenomenon. Search by condition, for example, search for the name Hy, then click on the contents of the page search box.

But the content after the page is still hy because we have search results in the URL, but reload the contents of the text box is not as good as it is harmless but still slightly uncomfortable if there is no way to solve it, I just don't think it's perfect.

Scenario 1.

ID (like operation): <input type= "text" name= "[contains]stuid"  value= "@Request. querystring[" [contains]stuid "]"/> <br/>

Because we have real search terms stored in our URL, we can get it straight through request.querystring["[contains]stuid"] but I think it's not good to see request.querystring in MVC.

Scenario 2.

ViewContext.Controller.ValueProvider.GetValue ("[Contains]stuid"). RawValue can also get a value by doing this

Scenario 3.

In the controller.

Viewbag.query = Querymodel; Record the search criteria and read them in the view.

   Querymodel query=viewbag.query as Querymodel;      String Stuid=query. Items.where (x = X.field = = "Stuid"). Select (X=>x.value). FirstOrDefault ()!=null?query. Items.where (x = X.field = = "Stuid"). Select (X=>x.value). FirstOrDefault (). ToString (): "";

The top three ways feel like there's something imperfect because what we're restoring is not just text, it could be a multi-box drop-down list, etc. so it's a little tricky to deal with, like,

String[] Newarrlovewgril = (string[]) Arrlovegril;          mmischeck= Newarrlovewgril. Contains ("MM")? "Checked= ' checked '": "";          Luciischeck = Newarrlovewgril. Contains ("LILI")? "Checked= ' checked '": "";          Gagaischeck = Newarrlovewgril. Contains ("GAGA")? "Checked= ' checked '": ""; Mm<input type= "checkbox" Name= "Lovegril" value= "MM" @MMischeck/>        lili<input type= "checkbox" Name= " Lovegril "value=" LILI "@LUCIischeck/>        gaga<input type=" checkbox "Name=" Lovegril "  value=" GAGA "@ Gagaischeck/>

I would like to restore these to be automated without any code for brainstorming I hope we can give some advice

Eight. Summary

The key to EF's dynamic query and sequencing is the construction of the expression tree. So learning and understanding the expression tree is critical, which is not useful here, and can be used instead of reflection to improve efficiency ~ But the generation of expression trees suggests a cache in the actual project.

The learning of expression trees can be seen in these few

Http://www.cnblogs.com/Terrylee/archive/2008/08/01/custom-linq-provider-part-1-expression-tree.html

Http://www.cnblogs.com/Ninputer/archive/2009/08/28/expression_tree1.html

Http://msdn.microsoft.com/en-us/library/bb397951.aspx

Short article spent half a day to finish writing ~ Hope to everyone has help or can give you some enlightenment have problems can message exchange welcome criticism and Suggestions thank reading ~

Dynamic multi-conditional query paging and sorting (i)--MVC and Entity Framework version URL page release

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.