Introduction
Entity Framework is the best Microsoft alternative for map a relational database to objects, and is already a very complete support in ASP.NET for populate the GridView control and enable the paging and sorting features directly by LinqDatasource object,but to use this alternative not have the chance to decouple the linq query in a business rules layer separated, which can be a problem if we have those requirements. The paging mut be done in the database for a more efficient query.
How do…
I made this example to show an alternative where enables the GridView control for sort and paging the query by means of a business rules layer and another utility layer in an n-layer and n-Tiers architecture (5 in this case).
I use the AdventureWorks database and only add the Products table to the Entity Framework diagram model for this example, and rename the properties Entity Set and Entity Name.
In then busiess rules layer, i have created the basic methods of “Get” they use for all query types.
The GetBy(Expression<Func<ProductInfo, bool>> predicate, ContextRequest contextRequest) method is extensible, because allow build the “where” using a predicate, and add more specifications how paging and sort by means of the ContextRequest object, thus, this method is useful for many varieties of petitions.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using AdventureWorksModel;
using System.Linq.Expressions;
using DemoDynLinqEFPagSort.Utils;
using LinqKit;
using DemoDynLinqEFPagSort.Utils.Linq;
namespace DemoDynLinqEFPagSort.BR
{
public class ProductsBR
{
AdventureWorksEntities context = null;
public ProductsBR()
{
context = new AdventureWorksEntities();
}
/// <summary>
/// Obtain a entity list apliying the predicate filter and pagging functions
/// </summary>
/// <param name="predicate">Filter of ProductInfo</param>
/// <param name="contextRequest">Extra params how pagging and sort</param>
/// <returns></returns>
public List<ProductInfo> GetBy(Expression<Func<ProductInfo, bool>> predicate, ContextRequest contextRequest)
{
List<ProductInfo> result = new List<ProductInfo>();
if (predicate == null) predicate = PredicateBuilder.True<ProductInfo>();
var query = context.ProductSet.Where(predicate);
query = ContextQueryBuilder<ProductInfo>.ApplyContextQuery(query, contextRequest);
return query.ToList<ProductInfo>();
}
/// <summary>
///
/// </summary>
/// <param name="predicate"></param>
/// <returns></returns>
public int TotalNumberOfProducts(Expression<Func<ProductInfo, bool>> predicate)
{
if (predicate == null) predicate = PredicateBuilder.True<ProductInfo>();
return context.ProductSet.Count(predicate);
}
}
}Notes: query = ContextQueryBuilder<ProductInfo>.ApplyContextQuery(query, contextRequest), where ContextQueryBuilder is a generic class that has some methods to help us to implement the required logic, to validate if the extensible parameter contextRequest contains paging and sorting options specified, and if so the add to the linq query.
// ---------------------------------------------------------------
// Emir Treviño http://emir.com.mx
// ---------------------------------------------------------------
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DemoDynLinqEFPagSort.Utils;
using System.Linq.Dynamic;
namespace DemoDynLinqEFPagSort.Utils.Linq
{
/// <summary>
/// Help to build the linq query
/// </summary>
/// <typeparam name="TEntity">Type of entity</typeparam>
public class ContextQueryBuilder<TEntity> where TEntity : class
{
/// <summary>
/// Aplica al query las opciones de paginación y ordenamiento que contiene el objeto contextRequest
/// </summary>
/// <param name="query">query inicializado previamente, de preferencia debe contener ya el Where</param>
/// <param name="contextRequest">Opciones de paginación y otras</param>
/// <returns></returns>
public static IQueryable<TEntity> ApplyContextQuery(IQueryable<TEntity> query, ContextRequest contextRequest)
{
if (contextRequest != null)
{
if (contextRequest.CustomQuery != null)
{
query = ApplyContextQuery(query, contextRequest.CustomQuery);
}
}
return query;
}
public static IQueryable<TEntity> ApplyContextQuery(IQueryable<TEntity> query, CustomQuery customQuery)
{
if (!string.IsNullOrEmpty(customQuery.OrderBy))
{
query = query.OrderBy(customQuery.OrderBy + " " + customQuery.SortDirection);
}
if (customQuery.Page != null && customQuery.PageSize != null)
{
int skip = 0;
if (string.IsNullOrEmpty(customQuery.OrderBy))
{
query = query.OrderBy(customQuery.DefaultOrderByColumn);
}
if ((int)customQuery.Page > 1)
{
skip = ((int)customQuery.PageSize * (int)customQuery.Page) - (int)customQuery.PageSize + 1;
}
query = query.Skip((int)skip).Take((int)customQuery.PageSize);
}
return query;
}
}
}In the previous class makes use of two extra utilities: LinqKit and Linq Dynamic (Included in the sample project) to make easier the use of predicates and simplifies the sorting and paging properties with strings.
Exist a MVC layer, but only has added the “Controller” object in this sample, to illustrate which is its role in relation to the layer of presentation and the rules of business: works as an intermediary, and is where it creates the extensible parameter ContextRequest.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DemoDynLinqEFPagSort.BR;
using AdventureWorksModel;
using DemoDynLinqEFPagSort.Utils;
namespace DemoDynLinqEFPagSort.MVC
{
public class ProductsController
{
private static ProductsBR _BR = null;
private static ProductsBR BR {
get {
if (_BR == null)
_BR = new ProductsBR();
return _BR;
}
}
public List<ProductInfo> GetAll(int maximumRows,
int startRowIndex, string SortExpression)
{
int page = startRowIndex /maximumRows;
if (page == 0 ) page=1;
return BR.GetBy(null, new ContextRequest()
{
CustomQuery = new CustomQuery()
{
DefaultOrderByColumn = "Name",
OrderBy = SortExpression,
PageSize = maximumRows,
Page = page
}
});
}
/// <summary>
///
/// </summary>
/// <param name="maximumRows"></param>
/// <param name="startRowIndex"></param>
/// <param name="SortExpression"></param>
/// <returns></returns>
public List<ProductInfo> GetAll(string SortExpression)
{
return BR.GetBy(null, new ContextRequest()
{
CustomQuery = new CustomQuery()
{
DefaultOrderByColumn = "Name",
OrderBy = SortExpression ,
PageSize = 10,
Page = 1
}
});
}
/// <summary>
///
/// </summary>
/// <returns></returns>
public int TotalNumberOfProducts() {
return BR.TotalNumberOfProducts(null);
}
}
}Finally, in the presentation layer notes that there is no code in the Default.aspx.cs file, only contains the declaration of controls in Default.aspx, the minimum necessary to ensure that the ObjectDatasource object is capable of sort and paging and integrate this feature to GridView with the business rules that is through the ProductsController object.
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="DemoDynLinqEFPagSort.Web._Default" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:ObjectDataSource ID="productsDataSource" runat="server"
SelectMethod="GetAll" TypeName="DemoDynLinqEFPagSort.MVC.ProductsController"
SortParameterName="SortExpression"
EnablePaging="True"
SelectCountMethod="TotalNumberOfProducts"
>
</asp:ObjectDataSource>
<asp:GridView ID="productsGridView" AllowPaging="True"
BorderColor="White" BorderStyle="Ridge"
CellSpacing="1" CellPadding="3" GridLines="None"
BackColor="White" BorderWidth="2px"
AutoGenerateColumns="False"
DataSourceID="productsDataSource"
Runat="server" AllowSorting="True">
<FooterStyle ForeColor="Black" BackColor="#C6C3C6"></FooterStyle>
<Columns>
<asp:BoundField DataField="ProductID" HeaderText="ID"
SortExpression="ProductID" />
<asp:BoundField DataField="Name" HeaderText="Name" SortExpression="Name" />
<asp:BoundField DataField="ProductNumber" HeaderText="Number"
SortExpression="ProductNumber">
<ItemStyle Wrap="False" />
</asp:BoundField>
</Columns>
<PagerStyle ForeColor="Black" HorizontalAlign="Left"
BackColor="#C6C3C6"></PagerStyle>
<HeaderStyle ForeColor="#E7E7FF" Font-Bold="True"
BackColor="#4A3C8C" Wrap="False"></HeaderStyle>
<SelectedRowStyle ForeColor="White"
Font-Bold="True"
BackColor="#9471DE"></SelectedRowStyle>
<RowStyle ForeColor="Black" BackColor="#DEDFDE"></RowStyle>
</asp:GridView>
<i>You are viewing page
<%=productsGridView.PageIndex + 1%>
of
<%=productsGridView.PageCount%>
</i>
</div>
</form>
</body>
</html>Conclusion
In “n” layer architecture Entity Framework allows be implemented without affecting the others layers or compromising the layer so dependent. Entity Framework guarantees in the future able to resolve the portability of databases types, writing little code in the presentation layer without having to resolve the access to data in the same, and most important is that the paging is carried out on the server of database.
Download sample here!
E-mail |
Permalink |
1 Comentarios|
Trackback