Use Entity Framework and ASP. net mvc 3 for server Segmentation

Original

In my February data point column, I showed the jquery datatables plug-in and its ability to seamlessly process massive data on the client. This is ideal for Web applications that require slicing and chunking of large amounts of data. This month, I will focus on using queries that return small loads for different types of interaction with data. This is especially important if you are targeting a mobile application.

I will use the features introduced in ASP. NET MVC 3 and demonstrate how to combine them with the efficient server-side paging feature of Entity Framework. This task has two challenges. First, provide the correct paging parameters for the Entity Framework query. Second, we provide instructions to indicate that there are too many visual clues to be retrieved and the links that trigger the retrieval to simulate the client paging.

ASP. net mvc 3 has many new features, such as the razor view engine, verification improvement features, and many JavaScript features. The MVC startup page is located in asp.net/mvc. you can download ASP. Net MVC 3 from here and find a link to blog articles and training videos that can help you get started faster. One of the new features I will use is viewbag. If you used ASP. net mvc before, you will know that viewbag is an enhanced feature of the viewdata class, so that you can use dynamically created attributes.

Another new element of ASP. net mvc 3 is the special system. Web. helpers. webgrid. Although one of the functions of the grid is paging, I will use this new grid in this example, but will not use its paging function, because the page is client paging, in other words, it pagination the dataset provided to it, similar to the datatables plug-in. I want to use server-side paging.

For this small application, you need to use an object model. I use a model created from the Microsoft adventureworkslt sample database, but I only need to bring customer and salesorderheaders into the model. I have moved the attributes of customer rowguid, passwordhash, and passwordsalt to another entity so that I don't have to worry about them when editing. Except for this small change, I did not change the default value of the model.

I created a project using the default ASP. NET MVC 3 Project template. This will pre-fill many controllers and views, and I will make the default homecontroller present MERs.

I will use a simple dataaccess class to provide interaction with the model, context, and subsequent databases. In this class, my getpagedmers MERs method provides server-side paging. If the ASP. net mvc application is designed to allow users to interact with all customers, a single query returns and manages a large number of customers in the browser. Instead, the application will render 10 rows at a time, and getpagedcustomers will provide the filter. The final query I need to execute will be as follows:

  
  
 
   
 context.Customers.Where(c => 
 
   
c.SalesOrderHeaders.Any()).Skip(skip).Take(take).ToList()
 
   
 
 
  

The view will know the requested page and provide the information to the Controller. The Controller will be responsible for understanding how many lines are provided per page. The Controller calculates the "Skip" value based on the page number and number of lines per page. When the Controller calls the getpagedmers MERs method, it passes in the calculated skip value and the number of lines per page, that is, the "take" value. Therefore, if you display 10 lines on page 4th, Skip is 40, and take is 10.

Paging query first creates a filter to request only customers with any salesorder. Then, the data produced by using the LINQ skip and take methods will be a subset of these customers. Then, the complete query, including paging, will be executed in the database. The database returns only the number of rows specified by the take method.

This query is written in several parts, so that we can add some skills in the process. The getpagedmers MERs method called from homecontroller is as follows:

  
  
 
   
 public static List<Customer> GetPagedCustomers(int skip, int take)
 
   
 {
 
   
 using (var context = new AdventureWorksLTEntities())
 
   
 {
 
   
 var query = context.Customers.Include("SalesOrderHeaders")
 
   
 .Where(c => c.SalesOrderHeaders.Any())
 
   
 .OrderBy(c => c.CompanyName + c.LastName + c.FirstName);
 
   
 
 
   
 return query.Skip(skip).Take(take).ToList();
 
   
 }
 
   
 }
 
   
 
 
  

The Controller index method that calls this method will use a variable called pagesize to determine the number of rows to be returned. This variable will become the take value. The index method also specifies the starting position based on the page number that will be passed in as a parameter, as shown below:

  
  
 
   
 public ActionResult Index(int?
 
   
 page)
 
   
 {
 
   
 const int pageSize = 10;
 
   
 var customers=DataAccess.GetPagedCustomers((page ??
 
   
 0)*pageSize, pageSize);
 
   
 return View(customers);
 
   
 }
 
   
 
 
  

This gives us a good part. The server page is ready. Using the webgrid marked in the index view, we can display the customers returned from the getpagedcustomers method. In the tag, you need to declare and instantiate the grid and input the model, which represents the list <customer> provided by the Controller when creating the view. Then, using the webgrid gethtml method, you can set the network format and specify the columns to display. I will display only three customer attributes: companyName, firstname, and lastname. You will be happy to find that you will be fully supported by intelliisense when you type the tag, whether you use the syntax associated with the aspx view, or use the new MVC 3 razor view engine syntax (as shown in the following example ). In the first column, I will provide an edit actionlink so that you can edit any customer displayed:

  
  
 
   
 @{
 
   
 var grid = new WebGrid(Model); 
 
   
}
 
   
<div id="customergrid">
 
   
 @grid.GetHtml(columns: grid.Columns(
 
   
 grid.Column(format: (item) => Html.ActionLink
 
   
 ("Edit", "Edit", new { customerId = item.CustomerID })),
 
   
 grid.Column("CompanyName", "Company"), 
 
   
 grid.Column("FirstName", "First Name"),
 
   
 grid.Column("LastName", "Last Name")
 
   
 ))
 
   
</div>
 
   
 
 
  

The result is as follows:Figure 1.

Figure 1Provide edit actionlink in webgrid

So far, everything went well. However, this does not provide users with a way to navigate to another page. There are multiple ways to achieve this. One way is to specify the page number in the URI, for example, http://adventureworksmvc.com/page/3. You certainly won't ask the end user to do this. The mechanism that is easier to find is to use paging controls, such as the page number link "1 2 3 4 5 ..." Or a forward or backward link, for example, <> ".

The current obstacle to enabling paging links is that the index view page does not know that too many customers will be available. It only knows that the range of customers to be displayed is 10. You can solve this problem by adding additional logic to the data access layer and passing it down to the view through the Controller. Let's start with the data access logic.

To know whether there are any records other than the current customer set, you will need to count all possible customers that will be returned by the query before 10 are a group of pages. This is where the query function is written in getpagedcustomers. Note that the first query returns to _ mermerquery, which is a variable declared at the class level, as shown below:

  
  
 
   
 _customerQuery = context.Customers.Where(c => c.SalesOrderHeaders.Any());
 
   
 
 
  

You can append the count method to the end of the query to obtain the customer count matching the query before the application page. The count method enforces immediate execution of a fairly simple query. The following is the query executed in SQL Server. A value is returned as a response:

  
  
 
   
 SELECT 
 
   
[GroupBy1].[A1] AS [C1]
 
   
FROM ( SELECT 
 
   
 COUNT(1) AS [A1]
 
   
 FROM [SalesLT].[Customer] AS [Extent1]
 
   
 WHERE EXISTS (SELECT 
 
   
 1 AS [C1]
 
   
 FROM [SalesLT].[SalesOrderHeader] AS [Extent2]
 
   
 WHERE [Extent1].[CustomerID] = [Extent2].[CustomerID]
 
   
 )
 
   
) AS [GroupBy1]
 
   
 
 
  

After counting, you can determine whether the current customer page is the first page, the last page, or the middle page. Then, you can use this logic to determine which links to display. For example, if you are a customer on the first page, you must display a link to access the previous customer data page, which is the previous page, for example. "<".

We can calculate the value to represent this logic in the Data Handler class, and then publish it together with the customer in the packaging class. The new classes we will use are as follows:

  
  
 
   
 public class PagedList<T>
 
   
 {
 
   
 public bool HasNext { get; set; }
 
   
 public bool HasPrevious { get; set; }
 
   
 public List<T> Entities { get; set; }
 
   
 }
 
   
 
 
  

The getpagedcustomers method now returns the pagedlist class instead of the List class.Figure 2The new getpagedmers MERs version is displayed.

Figure 2New getpagedmers MERs

  
  
 
   
 public static PagedList<Customer> GetPagedCustomers(int skip, int take)
 
   
 {
 
   
 using (var context = new AdventureWorksLTEntities())
 
   
 {
 
   
 var query = context.Customers.Include("SalesOrderHeaders")
 
   
 .Where(c => c.SalesOrderHeaders.Any())
 
   
 .OrderBy(c => c.CompanyName + c.LastName + c.FirstName);
 
   
 
 
   
 var customerCount = query.Count();
 
   
 
 
   
 var customers = query.Skip(skip).Take(take).ToList();
 
   
 
 
   
 return new PagedList<Customer>
 
   
 {
 
   
 Entities = customers,
 
   
 HasNext = (skip + 10 < customerCount),
 
   
 HasPrevious = (skip > 0)
 
   
 };
 
   
 }
 
   
 }
 
   
 
 
  

After filling in the new variables, let's take a look at how the index method in homecontroller can push them back to the view. You can use viewbag here. We will still return the customer query results in the view, but you can add some additional values to help determine the tag of the next and previous pages in the viewbag. They can then be used for this view at runtime:

  
  
 
   
 public ActionResult Index(int?
 
   
 page)
 
   
 {
 
   
 const int pageSize = 10;
 
   
 var customers=DataAccess.GetPagedCustomers((page ??
 
   
 0)*pageSize, pageSize);
 
   
 ViewBag.HasPrevious = DataAccess.HasPreviousCustomers;
 
   
 ViewBag.HasMore = DataAccess.HasMoreCustomers;
 
   
 ViewBag.CurrentPage = (page ??
 
   
 0);
 
   
 return View(customers);
 
   
 }
 
   
 
 
  

It is important to understand that viewbag is dynamic, not strongly typed. Viewbag does not really contain hasprevious and hasmore. I just created them when I typed the code. So don't be surprised that intelliisense didn't provide you with a prompt. You can create any dynamic attributes you like.

If you are already using the viewpage. viewdata dictionary and wonder what the difference is, you will find that viewbagActualExecute the same job. In addition to making the code beautiful, you also type attributes. For example, hasnext is dynamic {bool}, and currentpage is dynamic {int }. You do not need to convert these values in the future.

In the tag, I still have a customer list in the model variable, but there is also a viewbag variable. You must enter dynamic attributes in the tag. The tooltip reminds you that the attributes are dynamic, as shown in figureFigure 3.

Figure 3Viewbag attributes are not provided through intelliisense because they are dynamic attributes.

Use the viewbag variable to determine whether to display the tag of the navigation link:

  
  
 
   
 @{ if (ViewBag.HasPrevious)
 
   
 {
 
   
 @Html.ActionLink("<<", "Index", new { page = (ViewBag.CurrentPage - 1) })
 
   
 }
 
   
}
 
   
 
 
   
@{ if (ViewBag.HasMore)
 
   
 { @Html.ActionLink(">>", "Index", new { page = (ViewBag.CurrentPage + 1) }) 
 
   
 }
 
   
}
 
   
 
 
  

This logic is a change marked in the nerddinner application tutorial, which is located on nerddinnerbook.s3.amazonaws.com/intro.htm.

Now, when I run an application, I can export it from one customer to the next customer.

If I have a link on the first page that can navigate to the next page, but the link on the previous page is not, because there is no link on the previous page (seeFigure 4).

Figure 4The customer on the first page only has one link to the next page.

When I click the link and navigate to the next page, you can see the links to the previous page and the next page respectively (seeFigure 5).

Figure 5A single customer page with a navigation link to the previous or next page

Of course, the next step is to use the designer to make this page more attractive.

An indispensable part of the toolbox

All in all, although there are a variety of tools to simplify client paging, such as jquery datatables extension and the new ASP. net mvc 3 webgrid, your application needs may not benefit from bringing back a lot of data. Effective server-side paging is an indispensable part of the Toolbox. The collaboration between Entity Framework and ASP. net mvc can provide excellent user experience, simplify your development tasks, and complete tasks successfully.