本文转自：http://www.tuicool.com/articles/BBVr6z
Thanks to everyone for allowing us to give back to the .NET community, we released v1.0 of the Generic Unit of Work and Repository Framework for four weeks and received 655 downloads and 4121 views. This post will also serve as the documentation for release v2.0. Thanks to Ivan ( @ifarkas ) for helping out on the Async development and Ken for life the Unit of Work life cycle management and scaling the framework to handle Bounded DbContexts.
This will be part five of a five part series of blog posts.
- Generically Implementing the Unit of Work & Repository Pattern with Entity Framework in MVC & Simplifying Entity Graphs
- MVC 4, Kendo UI, SPA with Layout, View, Router & MVVM
- MVC 4, Web API, OData, EF, Kendo UI, Grid, Datasource (CRUD) with MVVM
- MVC 4, Web API, OData, EF, Kendo UI, Binding a Form to Datasource (CRUD) with MVVM
- Upgrading to Async with Entity Framework, MVC, OData AsyncEntitySetController, Kendo UI, Glimpse & Generic Unit of Work Repository Framework v2.0
We’ll continue on from the most recent post in this series, you can do a quick review of it herehttp://blog.longle.net/2013/06/19/mvc-4-web-api-odata-ef-kendo-ui-binding-a-form-to-datasource-crud-with-mvvm-part . Now let’s get right into it, by first taking a look at what was all involved on the server side.
First off let’s take a quick look and the changes we made to our DbContextBase to support Async.
Repository.DbContextBase.cs
Before
public class DbContextBase : DbContext, IDbContext
{
private readonly Guid _instanceId; public DbContextBase(string nameOrConnectionString) : base(nameOrConnectionString)
{
_instanceId = Guid.NewGuid();
} public Guid InstanceId
{
get { return _instanceId; }
} public void ApplyStateChanges()
{
foreach (var dbEntityEntry in ChangeTracker.Entries())
{
var entityState = dbEntityEntry.Entity as IObjectState;
if (entityState == null)
throw new InvalidCastException("All entites must implement the IObjectState interface, " +
"this interface must be implemented so each entites state can explicitely determined when updating graphs."); dbEntityEntry.State = StateHelper.ConvertState(entityState.State);
}
} public new IDbSet<T> Set<T>() where T : class
{
return base.Set<T>();
} protected override void OnModelCreating(DbModelBuilder builder)
{
builder.Conventions.Remove<PluralizingTableNameConvention>();
base.OnModelCreating(builder);
} public override int SaveChanges()
{
ApplyStateChanges();
return base.SaveChanges();
}
}
After:
public class DbContextBase : DbContext, IDbContext
{
private readonly Guid _instanceId; public DbContextBase(string nameOrConnectionString) : base(nameOrConnectionString)
{
_instanceId = Guid.NewGuid();
} public Guid InstanceId
{
get { return _instanceId; }
} public void ApplyStateChanges()
{
foreach (DbEntityEntry dbEntityEntry in ChangeTracker.Entries())
{
var entityState = dbEntityEntry.Entity as IObjectState;
if (entityState == null)
throw new InvalidCastException("All entites must implement the IObjectState interface, " +
"this interface must be implemented so each entites state can explicitely determined when updating graphs."); dbEntityEntry.State = StateHelper.ConvertState(entityState.State);
}
} public new IDbSet<T> Set<T>() where T : class
{
return base.Set<T>();
} public override int SaveChanges()
{
ApplyStateChanges();
return base.SaveChanges();
} public override Task<int> SaveChangesAsync()
{
ApplyStateChanges();
return base.SaveChangesAsync();
} public override Task<int> SaveChangesAsync(CancellationToken cancellationToken)
{
ApplyStateChanges();
return base.SaveChangesAsync(cancellationToken);
} protected override void OnModelCreating(DbModelBuilder builder)
{
builder.Conventions.Remove<PluralizingTableNameConvention>();
base.OnModelCreating(builder);
}
}
All that was needed here was to expose all the DbContext Async save operations so that we could use with our IUnitOfWork implementation, and also not forgetting to invoke our ApplyStateChanges so that we are managing the different states each entity could have when dealing with graphs.
Next up, are the enhancements made to our Repository.cs, so that our generic repositories can leverage the Async goodness as well.
Repostiory.Repository.cs
Before:
public class Repository<TEntity> : IRepository<TEntity> where TEntity : class
{
private readonly Guid _instanceId;
internal IDbContext Context;
internal IDbSet<TEntity> DbSet; public Repository(IDbContext context)
{
Context = context;
DbSet = context.Set<TEntity>();
_instanceId = Guid.NewGuid();
} public Guid InstanceId
{
get { return _instanceId; }
} public virtual TEntity FindById(object id)
{
return DbSet.Find(id);
} public virtual void InsertGraph(TEntity entity)
{
DbSet.Add(entity);
} public virtual void Update(TEntity entity)
{
DbSet.Attach(entity);
} public virtual void Delete(object id)
{
var entity = DbSet.Find(id);
((IObjectState) entity).State = ObjectState.Deleted;
Delete(entity);
} public virtual void Delete(TEntity entity)
{
DbSet.Attach(entity);
DbSet.Remove(entity);
} public virtual void Insert(TEntity entity)
{
DbSet.Attach(entity);
} public virtual IRepositoryQuery<TEntity> Query()
{
var repositoryGetFluentHelper = new RepositoryQuery<TEntity>(this);
return repositoryGetFluentHelper;
} internal IQueryable<TEntity> Get(
Expression<Func<TEntity, bool>> filter = null,
Func<IQueryable<TEntity>, IOrderedQueryable<TEntity>> orderBy = null,
List<Expression<Func<TEntity, object>>> includeProperties = null,
int? page = null,
int? pageSize = null)
{
IQueryable<TEntity> query = DbSet; if (includeProperties != null)
includeProperties.ForEach(i => query = query.Include(i)); if (filter != null)
query = query.Where(filter); if (orderBy != null)
query = orderBy(query); if (page != null && pageSize != null)
query = query
.Skip((page.Value - 1)*pageSize.Value)
.Take(pageSize.Value); var results = query; return results;
}
}
After:
public class Repository<TEntity> : IRepository<TEntity> where TEntity : class
{
private readonly Guid _instanceId;
private readonly DbSet<TEntity> _dbSet; public Repository(IDbContext context)
{
_dbSet = context.Set<TEntity>();
_instanceId = Guid.NewGuid();
} public Guid InstanceId
{
get { return _instanceId; }
} public virtual TEntity Find(params object[] keyValues)
{
return _dbSet.Find(keyValues);
} public virtual async Task<TEntity> FindAsync(params object[] keyValues)
{
return await _dbSet.FindAsync(keyValues);
} public virtual async Task<TEntity> FindAsync(CancellationToken cancellationToken, params object[] keyValues)
{
return await _dbSet.FindAsync(cancellationToken, keyValues);
} public virtual IQueryable<TEntity> SqlQuery(string query, params object[] parameters)
{
return _dbSet.SqlQuery(query, parameters).AsQueryable();
} public virtual void InsertGraph(TEntity entity)
{
_dbSet.Add(entity);
} public virtual void Update(TEntity entity)
{
_dbSet.Attach(entity);
((IObjectState)entity).State = ObjectState.Modified;
} public virtual void Delete(object id)
{
var entity = _dbSet.Find(id);
Delete(entity);
} public virtual void Delete(TEntity entity)
{
_dbSet.Attach(entity);
((IObjectState)entity).State = ObjectState.Deleted;
_dbSet.Remove(entity);
} public virtual void Insert(TEntity entity)
{
_dbSet.Attach(entity);
((IObjectState)entity).State = ObjectState.Added;
} public virtual IRepositoryQuery<TEntity> Query()
{
var repositoryGetFluentHelper = new RepositoryQuery<TEntity>(this);
return repositoryGetFluentHelper;
} internal IQueryable<TEntity> Get(
Expression<Func<TEntity, bool>> filter = null,
Func<IQueryable<TEntity>, IOrderedQueryable<TEntity>> orderBy = null,
List<Expression<Func<TEntity, object>>> includeProperties = null,
int? page = null,
int? pageSize = null)
{
IQueryable<TEntity> query = _dbSet; if (includeProperties != null)
{
includeProperties.ForEach(i => query = query.Include(i));
} if (filter != null)
{
query = query.Where(filter);
} if (orderBy != null)
{
query = orderBy(query);
} if (page != null && pageSize != null)
{
query = query
.Skip((page.Value - 1)*pageSize.Value)
.Take(pageSize.Value);
}
return query;
} internal async Task<IEnumerable<TEntity>> GetAsync(
Expression<Func<TEntity, bool>> filter = null,
Func<IQueryable<TEntity>, IOrderedQueryable<TEntity>> orderBy = null,
List<Expression<Func<TEntity, object>>> includeProperties = null,
int? page = null,
int? pageSize = null)
{
return Get(filter, orderBy, includeProperties, page, pageSize).AsEnumerable();
}
}
Here we’ve exposed the FindAsync methods from DbSet, so our Repositories can make use of them, and we’ve also wrapped implemented an Async implementation of our Get() method so that we can use it in our new Web Api ProductController.cs later.
Important note: here is that although our method is named GetAsync, it is not truly performing an Async interaction, this is due to the fact that if we were to use ToListAsync(), we would already executed the the query prior to OData applying it’s criteria to the execution plan e.g. if the OData query was requesting 10 records for page 2 of a grid from a Products table that had 1000 rows in it, ToListAsync() would have actually pulled a 1000 records from SQL to the web server and at that time do a skip 10 and take 20 from the collection of Products with 1000 objects. What we want is for this to happen on the SQL Server, meaning, SQL query the Products table, skip the first 10, and take next 10 records and only send those 10 records over to the web server, which will eventually surface into the Grid in the user’s browsers. Hence we are favoring payload size (true SQL Server side paging) going over the wire, vs. a true Async call to SQL.
Northwind.Web.Areas.Spa.Api.ProductController.cs
Before:
public class ProductController : EntitySetController<Product, int>
{
private readonly IUnitOfWork _unitOfWork; public ProductController(IUnitOfWork unitOfWork)
{
_unitOfWork = unitOfWork;
} public override IQueryable<Product> Get()
{
return _unitOfWork.Repository<Product>().Query().Get();
} protected override Product GetEntityByKey(int key)
{
return _unitOfWork.Repository<Product>().FindById(key);
} protected override Product UpdateEntity(int key, Product update)
{
update.State = ObjectState.Modified;
_unitOfWork.Repository<Product>().Update(update);
_unitOfWork.Save(); return update;
} public override void Delete([FromODataUri] int key)
{
_unitOfWork.Repository<Product>().Delete(key);
_unitOfWork.Save();
} protected override void Dispose(bool disposing)
{
_unitOfWork.Dispose();
base.Dispose(disposing);
}
}
After:
[ODataNullValue]
public class ProductController : AsyncEntitySetController<Product, int>
{
private readonly IUnitOfWork _unitOfWork; public ProductController(IUnitOfWork unitOfWork)
{
_unitOfWork = unitOfWork;
} protected override void Dispose(bool disposing)
{
_unitOfWork.Dispose();
base.Dispose(disposing);
} protected override int GetKey(Product entity)
{
return entity.ProductID;
} [Queryable]
public override async Task<IEnumerable<Product>> Get()
{
return await _unitOfWork.Repository<Product>().Query().GetAsync();
} [Queryable]
public override async Task<HttpResponseMessage> Get([FromODataUri] int key)
{
var query = _unitOfWork.Repository<Product>().Query().Filter(x => x.ProductID == key).Get();
return Request.CreateResponse(HttpStatusCode.OK, query);
} ///// <summary>
///// Retrieve an entity by key from the entity set.
///// </summary>
///// <param name="key">The entity key of the entity to retrieve.</param>
///// <returns>A Task that contains the retrieved entity when it completes, or null if an entity with the specified entity key cannot be found in the entity set.</returns>
[Queryable]
protected override async Task<Product> GetEntityByKeyAsync(int key)
{
return await _unitOfWork.Repository<Product>().FindAsync(key);
} protected override async Task<Product> CreateEntityAsync(Product entity)
{
if (entity == null)
throw new HttpResponseException(HttpStatusCode.BadRequest); _unitOfWork.Repository<Product>().Insert(entity);
await _unitOfWork.SaveAsync();
return entity;
} protected override async Task<Product> UpdateEntityAsync(int key, Product update)
{
if (update == null)
throw new HttpResponseException(HttpStatusCode.BadRequest); if (key != update.ProductID)
throw new HttpResponseException(Request.CreateODataErrorResponse(HttpStatusCode.BadRequest, new ODataError { Message = "The supplied key and the Product being updated do not match." })); try
{
update.State = ObjectState.Modified;
_unitOfWork.Repository<Product>().Update(update);
var x = await _unitOfWork.SaveAsync();
}
catch (DbUpdateConcurrencyException)
{
throw new HttpResponseException(HttpStatusCode.BadRequest);
}
return update;
} // PATCH <controller>(key)
/// <summary>
/// Apply a partial update to an existing entity in the entity set.
/// </summary>
/// <param name="key">The entity key of the entity to update.</param>
/// <param name="patch">The patch representing the partial update.</param>
/// <returns>A Task that contains the updated entity when it completes.</returns>
protected override async Task<Product> PatchEntityAsync(int key, Delta<Product> patch)
{
if (patch == null)
throw new HttpResponseException(HttpStatusCode.BadRequest); if (key != patch.GetEntity().ProductID)
throw Request.EntityNotFound(); var entity = await _unitOfWork.Repository<Product>().FindAsync(key); if (entity == null)
throw Request.EntityNotFound(); try
{
patch.Patch(entity);
await _unitOfWork.SaveAsync();
}
catch (DbUpdateConcurrencyException)
{
throw new HttpResponseException(HttpStatusCode.Conflict);
}
return entity;
} public override async Task Delete([FromODataUri] int key)
{
var entity = await _unitOfWork.Repository<Product>().FindAsync(key); if (entity == null)
throw Request.EntityNotFound(); _unitOfWork.Repository<Product>().Delete(entity); try
{
await _unitOfWork.SaveAsync();
}
catch (Exception e)
{
throw new HttpResponseException(
new HttpResponseMessage(HttpStatusCode.Conflict)
{
StatusCode = HttpStatusCode.Conflict,
Content = new StringContent(e.Message),
ReasonPhrase = e.InnerException.InnerException.Message
});
}
} #region Links
// Create a relation from Product to Category or Supplier, by creating a $link entity.
// POST <controller>(key)/$links/Category
// POST <controller>(key)/$links/Supplier
/// <summary>
/// Handle POST and PUT requests that attempt to create a link between two entities.
/// </summary>
/// <param name="key">The key of the entity with the navigation property.</param>
/// <param name="navigationProperty">The name of the navigation property.</param>
/// <param name="link">The URI of the entity to link.</param>
/// <returns>A Task that completes when the link has been successfully created.</returns>
[AcceptVerbs("POST", "PUT")]
public override async Task CreateLink([FromODataUri] int key, string navigationProperty, [FromBody] Uri link)
{
var entity = await _unitOfWork.Repository<Product>().FindAsync(key); if (entity == null)
throw Request.EntityNotFound(); switch (navigationProperty)
{
case "Category":
var categoryKey = Request.GetKeyValue<int>(link);
var category = await _unitOfWork.Repository<Category>().FindAsync(categoryKey); if (category == null)
throw Request.EntityNotFound(); entity.Category = category;
break; case "Supplier":
var supplierKey = Request.GetKeyValue<int>(link);
var supplier = await _unitOfWork.Repository<Supplier>().FindAsync(supplierKey); if (supplier == null)
throw Request.EntityNotFound(); entity.Supplier = supplier;
break; default:
await base.CreateLink(key, navigationProperty, link);
break;
}
await _unitOfWork.SaveAsync();
} // Remove a relation, by deleting a $link entity
// DELETE <controller>(key)/$links/Category
// DELETE <controller>(key)/$links/Supplier
/// <summary>
/// Handle DELETE requests that attempt to break a relationship between two entities.
/// </summary>
/// <param name="key">The key of the entity with the navigation property.</param>
/// <param name="relatedKey">The key of the related entity.</param>
/// <param name="navigationProperty">The name of the navigation property.</param>
/// <returns>Task.</returns>
public override async Task DeleteLink([FromODataUri] int key, string relatedKey, string navigationProperty)
{
var entity = await _unitOfWork.Repository<Product>().FindAsync(key); if (entity == null)
throw Request.EntityNotFound(); switch (navigationProperty)
{
case "Category":
entity.Category = null;
break; case "Supplier":
entity.Supplier = null;
break; default:
await base.DeleteLink(key, relatedKey, navigationProperty);
break;
} await _unitOfWork.SaveAsync();
} // Remove a relation, by deleting a $link entity
// DELETE <controller>(key)/$links/Category
// DELETE <controller>(key)/$links/Supplier
/// <summary>
/// Handle DELETE requests that attempt to break a relationship between two entities.
/// </summary>
/// <param name="key">The key of the entity with the navigation property.</param>
/// <param name="navigationProperty">The name of the navigation property.</param>
/// <param name="link">The URI of the entity to remove from the navigation property.</param>
/// <returns>Task.</returns>
public override async Task DeleteLink([FromODataUri] int key, string navigationProperty, [FromBody] Uri link)
{
var entity = await _unitOfWork.Repository<Product>().FindAsync(key); if (entity == null)
throw Request.EntityNotFound(); switch (navigationProperty)
{
case "Category":
entity.Category = null;
break; case "Supplier":
entity.Supplier = null;
break; default:
await base.DeleteLink(key, navigationProperty, link);
break;
} await _unitOfWork.SaveAsync();
}
#endregion Links public override async Task<HttpResponseMessage> HandleUnmappedRequest(ODataPath odataPath)
{
//TODO: add logic and proper return values
return Request.CreateResponse(HttpStatusCode.NoContent, odataPath);
} #region Navigation Properties
public async Task<Category> GetCategory(int key)
{
var entity = await _unitOfWork.Repository<Product>().FindAsync(key); if (entity == null)
throw Request.EntityNotFound(); return entity.Category;
} public async Task<Supplier> GetSupplier(int key)
{
var entity = await _unitOfWork.Repository<Product>().FindAsync(key); if (entity == null)
throw Request.EntityNotFound(); return entity.Supplier;
}
#endregion Navigation Properties
}
Quickly looking at this, one can realize there is a lot more code than our pre-Async implementation. Well don’t be alarmed, there’s a lot of code here that wasn’t required to support our use case in the live demo ( http://longle.azurewebsites.net/Spa/Product#/list ), however we wanted to take the extra step so that we can really grasp on how to work with entity graphs with OData by leveraging the ?$expand query string parameter. The only methods that are needed for our use case are as follows:
- Task<IEnumerable> Get()
- Task Get([FromODataUri] int key)
- Task UpdateEntityAsync(int key, Product update)
- Task Delete([FromODataUri] int key)
We’ll leave all the other Actions as is, so you can see how to deep load your entity graph with OData and Web Api. We’ve included some pre-baked clickable OData URL’s (queries) on the View so that you can actually click and see the response payload in your browser (you’ll have to use Chrome or Firefox, IE has some catching up to do here).
*Click on image
Now let’s do a deep dive on the our Async Get() Action in our Controller.
[Queryable] public override async Task<IEnumerable<Product>> Get()
{
return await _unitOfWork.Repository<Product>().Query().GetAsync();
}
My initial thought when seeing this this Action (signature) is that it’s not IQueryable?! Which means that the SQL plan from EF has already been executed before OData has an opportunity to apply it’s criteria to the query plan! Well that’s not the case, we outfitted the Project with Glimpse and Glimpse EF6 to actually see what SQL queries were being sent over the wire.
So let’s take a look at the loading up our Kendo UI Grid with the awesomeness of Glimpse running. Since our View is built with Kendo UI, and we know it’s invoking Ajax calls to request data, we’ll click on the Ajax panel on the Glimpse HUD.
*Click on image
Now with the HUD automatically switching to standard view we can see all the Ajax requests that our View made, we are interested in the OData request that was made to hydrate our Kendo Grid.
*Click on image
After clicking on Inspect for the Ajax OData request, we see that menu buttons buttons that have tracing data for that request start to actual blink…! One of them being SQL, so let’s click on it.
*Click on image
Ladies and gentlemen, I kid you not, behold this is the actual SQL query that was from our Unit Of Work -> Repostiory -> Entity Framework 6 -> T-SQL, that was actually sent to SQL Server (actually in our case SQL Server CE, so that the live demo can be complete free with Azure Website without the need to pay for SQL Azure). BTW, we just scratching the surface of what Glimpse can do, the list is pretty much endless e.g. displays MVC Routes, Actions, Tracing, Environment Variables, MVC Views, and performance metrics for pretty much all of them, etc.
Now back to the topic at hand, we can definitively see that although our Action and our Repository are returning IEnumerable:
Get Action the Kendo UI Datasource is calling, which returns IEnumerable.
[Queryable] public override async Task<IEnumerable<Product>> Get()
{
return await _unitOfWork.Repository<Product>().Query().GetAsync();
}
Repository method the Action is calling, which also returns IEnumerable.
internal async Task<IEnumerable<TEntity>> GetAsync(
Expression<Func<TEntity, bool>> filter = null,
Func<IQueryable<TEntity>, IOrderedQueryable<TEntity>> orderBy = null,
List<Expression<Func<TEntity, object>>> includeProperties = null,
int? page = null,
int? pageSize = null)
{
return Get(filter, orderBy, includeProperties, page, pageSize).AsEnumerable();
}
The query plan is still valid, meaning it’s selecting only the rows (10 records to be exact) that the Grid is requesting for page one (1) of the Grid. So how is this happening? Well we’ve decorated our action with the [Queryable] attribute, so OData and Web Api is able to perform it’s magic together during run-time in the ASP.NET HTTP pipeline.
T-SQL That’s Being Sent Over the Wire, courtesy of Glimpse EF6
SELECT TOP (10 /* @p__linq__0 */)
[Extent1].[Product ID] AS [Product ID],
[Extent1].[Product Name] AS [Product Name],
[Extent1].[Supplier ID] AS [Supplier ID],
[Extent1].[Category ID] AS [Category ID],
[Extent1].[Quantity Per Unit] AS [Quantity Per Unit],
[Extent1].[Unit Price] AS [Unit Price],
[Extent1].[Units In Stock] AS [Units In Stock],
[Extent1].[Units On Order] AS [Units On Order],
[Extent1].[Reorder Level] AS [Reorder Level],
[Extent1].[Discontinued] AS [Discontinued]
FROM [Products] AS [Extent1]
ORDER BY [Extent1].[Product ID] ASC
Now, let’s cover at a high-level on all the Actions that aren’t required for our live demo use case, which are mostly to support Navigation Properties e.g. Product.Supplier, Product.Category, etc.
The $expand query string parameter allows us to hydrate complex navigation property types. For example in our case when we query for a Product, and Product has a property of Category and we the Category to be hydrated with its data we would leverage the $expand querystring parameter to do this, click this Url : http://longle.azurewebsites.net/odata/Product/?$inlinecount=allpages&$orderby=ProductName&$skip=1&$top=2&$expand=Category&$select=ProductID,ProductName,Category/CategoryID,Category/CategoryName to see the $expand in action.
SQL Being Sent Over the Wire, again courtesy of Glimpse EF6
SELECT TOP (2 /* @p__linq__1 */)
[top].[Product ID] AS [Product ID],
[top].[C1] AS [C1],
[top].[C2] AS [C2],
[top].[Product Name] AS [Product Name],
[top].[C3] AS [C3],
[top].[C4] AS [C4],
[top].[C5] AS [C5],
[top].[C6] AS [C6],
[top].[Category Name] AS [Category Name],
[top].[C7] AS [C7],
[top].[Category ID] AS [Category ID],
[top].[C8] AS [C8]
FROM ( SELECT [Project1].[Product ID] AS [Product ID], [Project1].[Product Name] AS [Product Name], [Project1].[Category ID] AS [Category ID], [Project1].[Category Name] AS [Category Name], [Project1].[C1] AS [C1], [Project1].[C2] AS [C2], [Project1].[C3] AS [C3], [Project1].[C4] AS [C4], [Project1].[C5] AS [C5], [Project1].[C6] AS [C6], [Project1].[C7] AS [C7], [Project1].[C8] AS [C8]
FROM ( SELECT
[Extent1].[Product ID] AS [Product ID],
[Extent1].[Product Name] AS [Product Name],
[Extent1].[Category ID] AS [Category ID],
[Extent2].[Category Name] AS [Category Name],
N'ace5ad31-e3e9-4cde-9bb8-d75fced846fa' AS [C1],
N'ProductName' AS [C2],
N'ProductID' AS [C3],
N'Category' AS [C4],
N'ace5ad31-e3e9-4cde-9bb8-d75fced846fa' AS [C5],
N'CategoryName' AS [C6],
N'CategoryID' AS [C7],
CASE WHEN ([Extent1].[Category ID] IS NULL) THEN cast(1 as bit) ELSE cast(0 as bit) END AS [C8]
FROM [Products] AS [Extent1]
LEFT OUTER JOIN [Categories] AS [Extent2] ON [Extent1].[Category ID] = [Extent2].[Category ID]
) AS [Project1]
ORDER BY [Project1].[Product Name] ASC, [Project1].[Product ID] ASC
OFFSET 1 /* @p__linq__0 */ ROWS
) AS [top]
Payload Results
{
"odata.metadata":"http://longle.azurewebsites.net/odata/$metadata#Product&$select=ProductID,ProductName,Category/CategoryID,Category/CategoryName","odata.count":"77","value":[
{
"Category":{
"CategoryID":2,"CategoryName":"Condiments"
},"ProductID":3,"ProductName":"Aniseed Syrup"
},{
"Category":{
"CategoryID":8,"CategoryName":"Seafood"
},"ProductID":40,"ProductName":"Boston Crab Meat"
}
]
}
We can really see the power of Web Api and OData now, we’re actually able to query for Products (skip the first and take the next two) and request that Category be hydrated but specifically only the CategoryId and Name and none of the other fields.
We’ve polished the UI/UX a bit, relocated Edit, Edit Details, and Delete buttons out of the rows into the Grid Toolbar (header) to make better use of the Grid real estate, using Kendo’s Template Framework, which illustrates how flexible Kendo UI can be. The app has been upgraded to, Twitter Bootstrap as by leveraging the new out of the box MVC Project Templates in Visual Studio 2013 (Preview) and changing the Kendo UI theme to Bootstrap to match.
All Kendo Views which are remotely loaded on demand into the SPA are now actually MVC Razor Views, the Kendo Router remotely loads views by traditional MVC routes e.g.
{controller}/{action}/{id} vs. what was in the previous post (http://blog.longle.net/2013/06/17/mvc-4-kendo-ui-spa-with-layout-router-mvvm/ ) which was just serving up raw *.html pages. This has been a request for devs that are making the transition from server side MVC development into the SPA realm, and had .NET libraries they still wanted to make use of and leverage in their their Razor Views for SPA’s. Obviously, all Views and ViewModel binding are done with with Kendo’s MVVM Framework.
Northwind.Web/Areas/Spa/Content/Views/products.html
Before(non Razor, just plain *.html pages were used for SPA):
<script type="text/x-kendo-template" id="products">
<section class="content-wrapper main-content clear-fix">
<h3>Technlogy Stack</h3>
<ol class="round">
<li class="one">
<h5>.NET</h5>
ASP.NET MVC 4, Web API, OData, Entity Framework
</li>
<li class="two">
<h5>Kendo UI Web Framework</h5>
MVVM, SPA, Grid, DataSource
</li>
<li class="three">
<h5>Patterns</h5>
Unit of Work, Repository, MVVM
</li>
</ol>
<h3>View Products</h3><br/>
<div class="k-content" style="width:100%">
<div id="productsForm">
<div id="productGrid"
data-role="grid"
data-sortable="true"
data-pageable="true"
data-filterable="true"
data-bind="source: dataSource, events:{dataBound: dataBound, change: onChange}"
data-editable = "inline"
data-selectable="true"
data-columns='[
{ field: "ProductID", title: "Id", width: "50px" },
{ field: "ProductName", title: "Name", width: "300px" },
{ field: "UnitPrice", title: "Price", format: "{0:c}", width: "100px" },
{ field: "Discontinued", width: "150px" },
{ command : [ "edit", "destroy", { text: "Edit Details", click: editProduct } ], title: "Action", } ]'>
</div>
</div>
</div>
</section>
</script> <script>
function editProduct(e) {
e.preventDefault();
var tr = $(e.currentTarget).closest("tr");
var dataItem = $("#productGrid").data("kendoGrid").dataItem(tr);
window.location.href = '#/productEdit/' + dataItem.ProductID;
} var lastSelectedProductId; var crudServiceBaseUrl = "/odata/Product";
var productsModel = kendo.observable({
dataSource: dataSource = new kendo.data.DataSource({
type: "odata",
transport: {
read: {
url: crudServiceBaseUrl,
dataType: "json"
},
update: {
url: function (data) {
return crudServiceBaseUrl + "(" + data.ProductID + ")";
},
dataType: "json"
},
destroy: {
url: function (data) {
return crudServiceBaseUrl + "(" + data.ProductID + ")";
},
dataType: "json"
}
},
batch: false,
serverPaging: true,
serverSorting: true,
serverFiltering: true,
pageSize: 10,
schema: {
data: function (data) {
return data.value;
},
total: function (data) {
return data["odata.count"];
},
errors: function (data) {
},
model: {
id: "ProductID",
fields: {
ProductID: { type: "number", editable: false, nullable: true },
ProductName: { type: "string", validation: { required: true } },
UnitPrice: { type: "number", validation: { required: true, min: 1 } },
Discontinued: { type: "boolean" },
UnitsInStock: { type: "number", validation: { min: 0, required: true } }
}
}
},
error: function (e) {
var message = e.xhr.responseJSON["odata.error"].message.value;
var innerMessage = e.xhr.responseJSON["odata.error"].innererror.message;
alert(message + "\n\n" + innerMessage);
}
}),
dataBound: function (arg) {
if (lastSelectedProductId == null) return; // check if there was a row that was selected
var view = this.dataSource.view(); // get all the rows
for (var i = 0; i < view.length; i++) { // iterate through rows
if (view[i].ProductID == lastSelectedProductId) { // find row with the lastSelectedProductd
var grid = arg.sender; // get the grid
grid.select(grid.table.find("tr[data-uid='" + view[i].uid + "']")); // set the selected row
break;
}
}
},
onChange: function (arg) {
var grid = arg.sender;
var dataItem = grid.dataItem(grid.select());
lastSelectedProductId = dataItem.ProductID;
}
}); $(document).bind("viewSwtichedEvent", function (e, args) { // subscribe to the viewSwitchedEvent
if (args.name == "products") { // check if this view was switched too
if (args.isRemotelyLoaded) { // check if this view was remotely loaded from server
kendo.bind($("#productsForm"), productsModel); // bind the view to the model
} else {// view already been loaded in cache
productsModel.dataSource.fetch(function() {}); // refresh grid
}
}
}); </script>
Northwind/Areas/Spa/Views/Product/List.cshtml
After (Razor Views used for Kendo SPA Views):
@{
ViewBag.Title = "Products";
Layout = "";
}
<div class="row">
<div class="span5">
<h2>Technlogy Stack</h2>
<h3><a href="http://blog.longle.net">blog.longle.net</a></h3>
<p>ASP.NET MVC 4, Web API, OData, Entity Framework 6 CTP, EntityFramework CE 6 RC1, Visual Studio 2013 Preview, Sql Server CE, Twitter Bootstrap, Kendo UI Web, Azure Website PaaS (<a href="http://www.windowsazure.com/en-us/develop/net/aspnet/" target="blank">free!</a>)</p>
<br />
<p><a class="btn" href="http://go.microsoft.com/fwlink/?LinkId=301865">Learn more »</a></p>
</div>
</div>
<br /><br />
<div class="k-content" style="width: 100%">
<div id="view">
<div id="productGrid"
data-role="grid"
data-sortable="true"
data-pageable="true"
data-filterable="true"
data-bind="source: dataSource, events: { dataBound: dataBound, change: onChange }"
data-editable="inline"
data-selectable="true"
data-toolbar='[ { template: $("#template").html() } ]'
data-columns='[
{ field: "ProductID", title: "ID", width: "50px" },
{ field: "ProductName", title: "Name"},
{ field: "QuantityPerUnit", title: "Quantity", width: "200px" },
{ field: "UnitsInStock", title: "Stock", width: "90px" },
{ field: "UnitPrice", title: "Price", format: "{0:c}", width: "100px" },
{ field: "Discontinued", width: "150px" } ]'>
</div>
</div>
</div>
<script type="text/x-kendo-template" id="template">
<div class="toolbar">
<a class="k-button" onclick="edit(event);"><span class="k-icon k-i-tick"></span>Edit</a>
<a class="k-button" onclick="destroy(event);"><span class="k-icon k-i-tick"></span>Delete</a>
<a class="k-button" onclick="details(event);"><span class="k-icon k-i-tick"></span>Edit Details</a>
</div>
<div class="toolbar" style="display:none">
<a class="k-button" onclick="save(event);"><span class="k-icon k-i-tick"></span>Save</a>
<a class="k-button" onclick="cancel(event);"><span class="k-icon k-i-tick"></span>Cancel</a>
</div>
</script>
<script>
var lastSelectedDataItem;
var save = function (event) {
getSelectedRowDoAction(event, function (grid) {
grid.saveRow();
$(".toolbar").toggle();
});
};
var cancel = function (event) {
getSelectedRowDoAction(event, function (grid) {
grid.cancelRow();
$(".toolbar").toggle();
});
};
var details = function (event) {
getSelectedRowDoAction(event, function (grid, row, dataItem) {
window.location.href = '#/edit/' + dataItem.ProductID;
});
};
var edit = function (event) {
getSelectedRowDoAction(event, function (grid, row) {
grid.editRow(row);
$(".toolbar").toggle();
});
};
var destroy = function (event) {
getSelectedRowDoAction(event, function (grid, row, dataItem) {
grid.dataSource.remove(dataItem);
grid.dataSource.sync();
});
};
var getSelectedRowDoAction = function (event, action) {
event.preventDefault();
var grid = $("#productGrid").data("kendoGrid");
var selectedRow = grid.select();
var dataItem = grid.dataItem(selectedRow);
if (selectedRow.length > 0)
action(grid, selectedRow, dataItem);
else
alert("Please select a row.");
};
var Product = kendo.data.Model.define({
id: "ProductID",
fields: {
ProductID: { type: "number", editable: false, nullable: true },
ProductName: { type: "string", validation: { required: true } },
QuantityPerUnit: { type: "string", validation: { required: true } },
UnitsInStock: { type: "number", validation: { required: true } },
UnitPrice: { type: "number", validation: { required: true, min: 1 } },
Discontinued: { type: "boolean" }
}
});
var baseUrl = "/odata/Product";
var dataSource = new kendo.data.DataSource({
type: "odata",
transport: {
read: {
url: baseUrl,
dataType: "json"
},
update: {
url: function (data) {
return baseUrl + "(" + data.ProductID + ")";
},
dataType: "json"
},
destroy: {
url: function (data) {
return baseUrl + "(" + data.ProductID + ")";
},
dataType: "json"
}
},
batch: false,
serverPaging: true,
serverSorting: true,
serverFiltering: true,
pageSize: 10,
schema: {
data: function (data) {
return data.value;
},
total: function (data) {
return data["odata.count"];
},
errors: function (e) {
return e.errors;
},
model: Product
},
error: function (e) {
var responseJson = e.xhr.responseJSON;
if (responseJson != undefined) {
if (responseJson["odata.error"] != undefined) {
var error = responseJson["odata.error"];
var message = error.message.value + '\n\n' + error.innererror.message;
alert(message);
}
} else {
alert(e.xhr.status + "\n\n" + e.xhr.responseText + "\n\n" + e.xhr.statusText);
}
this.read();
}
});
var viewModel = kendo.observable({
dataSource: dataSource,
dataBound: function (arg) {
if (lastSelectedDataItem == null) return; // check if there was a row that was selected
var view = this.dataSource.view(); // get all the rows
for (var i = 0; i < view.length; i++) { // iterate through rows
if (view[i].ProductID == lastSelectedDataItem.ProductID) { // find row with the lastSelectedProductd
var grid = arg.sender; // get the grid
grid.select(grid.table.find("tr[data-uid='" + view[i].uid + "']")); // set the selected row
break;
}
}
},
onChange: function (arg) {
var grid = arg.sender;
lastSelectedDataItem = grid.dataItem(grid.select());
},
});
$(document).bind("viewSwtichedEvent", function (e, args) { // subscribe to the viewSwitchedEvent
if (args.name == "list") { // check if this view was switched too
if (args.isRemotelyLoaded) { // check if this view was loaded for the first time (remotely from server)
kendo.bind($("#view"), viewModel); // bind the view to the model
} else {// view already been loaded in cache
viewModel.dataSource.read(); // refresh grid
}
}
});
</script>
<style scoped>
#productGrid .k-toolbar {
padding: .7em;
}
.toolbar {
float: right;
}
</style>
Happy Coding…!
Live Demo: http://longle.azurewebsites.net/Spa/Product#/list
Download: https://genericunitofworkandrepositories.codeplex.com/