Log in to like this post! Developing a RESTful CRUD grid with IgniteUI, WebAPI and Entity Framework Alexander Todorov / Thursday, May 23, 2013 In a previous blog post of mine I have presented an application which tackles a create/read/update/delete scenario in the context of the IgniteUI grid. I have used frameworks such as MongoDB, Node.JS and Mongoose. In this blog post, I would like to present an application which uses WebAPI and Enitity Framework to talk to an Ignite UI grid. You are probably aware that the Ignite UI grid supports HTTP REST CRUD operations, and any backend which implements the PUT/GET/POST/DELETE HTTP requests according to the RFC specs is compatible with the grid. WebAPI is a framework from Microsoft which makes it really straightforward to build REST services. We are going to use a MS-SQL database, have EntityFramework on top of that, in the context of an ASP.NET MVC application, and expose our CRUD operations via WebAPI. I will use two pages in my application – one which is just HTML + CSS + JavaScript, and one which uses Razor and also makes use of our MVC Grid wrapper. The idea is to show that our grid does not depend on ASP.NET MVC, when we would like to use WebAPI and perform CRUD operations. Before we start I will list the software packages that you will need: 1) Visual Studio 2012 (Express should do) 2) ASP.NET MVC 4.0 3) Entity Framework (it’s best to install the latest version) 4) Microsoft SQL Server Compact (4.0) – if you do not have it, you can download from http://www.microsoft.com/en-us/download/details.aspx?id=17876 The first three packages can be obtained from NuGet. I’ve actually created a dependencies folder in the project I have attached to this blog post, and all the necessary assemblies are there, so you shouldn’t need to download and install anything additional. Note: I have used 7zip in order to compress the sample, because default zip /RAR compression makes the file size too big. I have put an extra .zip extension that you’ll need to remove, because our blogging software doesn’t seem to accept the default .7z extension. You can obtain 7Zip from here: http://www.7-zip.org/ Other programs such as WinRAR will also be able to decompress it. Before I begin with the MVC project, I would like to summarize the different HTTP request types that correspond to each CRUD operation: Operation HTTP URL Format Example Response Code Create POST /api/products 201 Created ; Read GET /api/products 200 OK Update PUT /api/products/<id> 204 No Content Delete (remove) DELETE /api/products/?index=<id> 200 OK Any client & server implementing those correctly can communicate in a RESTful way. Let’s start by creating an ASP.NET MVC 4.0 project. We can add the js and css folders from the Ignite UI installation to the root of the project – later on, they’ll be referenced from there. Now add a new item to the project – SQL Server Compact database, and name it Products.sdf: Double-click the sdf file – it should be placed in the App_Data project folder, and add a couple of tables to the database (Company, Orders, and Products): The schema for the tables is as follows: I am not going to use all the tables immediately because I would like to demonstrate some specifics of the Entity Framework, and different ways you can serialize lists of objects and send them from the server to the client – either directly or by proxying to a custom simpler class. After you have the tables created, you can create an EF model, for now let’s create one for the Products: After you do this, you can use the EF model in the following way, in your controller code: var entities = new ProductsEntities(); // entities.Products will give you the list of all products, EF will create a “Product” type for you automatically Now, in the “Controllers” folder, create a “HomeController” (Empty MVC Controller), an mvcController (Empty MVC Controller). We will use the HomeController, which is the default one, to render a HTML view which will instantiate an ignite UI grid using only JavaScript. We will use the mvcController to render an IgniteUI grid that’s based on the MVC wrappers. public class HomeController : Controller { public ActionResult Index() { return View(); } } public class mvcController : Controller { // // GET: /mvc/ public ActionResult Index() { return View(); } } The key part of our sample is to expose our CRUD operations through WebAPI. We will create an additional MVC Controller that will handle the CRUD requests, set the appropriate response types, etc. There are two ways to do that – to use an API controller template, or to write it manually. Let’s see what happens if we use the API template – that will do most of the work for us: Let’s have a look at parts of the generated code: public class Default1Controller : ApiController { private ProductsEntities db = new ProductsEntities(); // GET api/Default1 public IEnumerable GetProducts() { var products = db.Products.Include("Company"); return products.AsEnumerable(); } You can see that, based on the Product class from EF, WebAPI’s controller template pretty much does all the CRUD-related work for you. It generates the correct HttpResponse codes for you, too. The only drawback of the default template is that it doesn’t generate code for batch operations. Later on in this blog post, we will create a WebAPI controller from scratch, which will tackle more cases than the default one. Now we need to focus on the Client-side part and write our View. Note that I am attaching a fully functional application, so don’t worry if something does not run correctly if you try to build the app from scratch, you can always have a look at the attached app. In the Views => Home folder, create an Index.cshtml View, with the following contents: <!DOCTYPE html> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <title>WebAPI CRUD</title> <script type="text/javascript" src="http://code.jquery.com/jquery-1.7.1.js"></script> <script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jqueryui/1.8.2/jquery-ui.js"></script> <script type="text/javascript" src="js/infragistics.loader.js"></script> <script type="text/javascript"> $.ig.loader({ scriptPath: 'js/', cssPath: 'css/', resources: 'igGrid.Updating', ready: function () { $("#grid1").igGrid({ dataSource: "/api/products", primaryKey: "ID", restSettings: { update: { url: "/api/products" }, remove: { url: "/api/products", batch: true }, create: { url: "/api/products", batch: true } }, autoGenerateColumns: false, height: "350px", width: "800px", columns: [ { headerText: "ID", key: "ID", dataType: "number" }, { headerText: "Name", key: "Name", dataType: "string"}, { headerText: "Price", key: "Price", dataType: "number"}, { headerText: "Quantity", key: "Quantity", dataType: "number"}, { headerText: "Release Date", key: "ReleaseDate", dataType: "date"} ], features: [ { name: "Updating", editMode: 'cell', columnSettings: [{ columnKey: 'ID', readOnly: true }] } ] }); $("#save1").on("click", function () { $("#grid1").igGrid("saveChanges"); }); } }); </script> </head> <body> <div class="panel"> <input id="save1" type="button" value="Save Changes to server" /> <br /> <table id="grid1"></table> </div> </body> </html> The most important section of this code is the part where we define our REST settings. Note that you don’t need any custom code at all, the grid manages all the RESTful CRUD ops for yourself. You can use any service URL which conforms to the RFC specs for GET,POST,PUT, etc. restSettings: { update: { url: "/api/products" }, remove: { url: "/api/products", batch: true }, create: { url: "/api/products", batch: true } }, Note that every type of operation, “update”, “create”, “remove” , can be batch or not batch. If it is set to batch:true, you can perform several ops in one single request, without having to send multiple requests. The URL in that case will have the following format: E.g. creating two products: /api/products/?ID=5&ID=6&ID=7 Also note how The WebAPI automatically intercepts the correct request and executes the corresponding method in the controller, based on the URL and the type of the request. So for example if we have the following request to create a product: The controller method that will be executed is the following: // POST api/products public HttpResponseMessage PostProduct(Product product) Note how WebAPI automatically performs serialization/deserialization of the objects to JSON. (Keep in mind if your controller name is Default1 (the default), the request will have the format: // POST api/Default1) In a similar way, we can create an MVC view that instantiates the Ignite UI grid using the MVC wrapper extensions, I will leave the details for you (please have a look at the attached sample -> Views/mvc/Index.cshtml). Now if we play a bit with our EF model, we add some more entities, such as Order and Company. We can also add associations between those entities, such that a Company can have many products (1:n), and a Product can have many Orders (1:n). Then our model will look like this: Note that when you are creating the associations, EF will ask you and create various properties in your Product classes, such as Orders (list), Company (of type Company), etc. And here comes the problem if we try to run the same CRUD grid, after we’ve added those extra parts in the model – we are inevitably going to get either Circular reference errors, or other issues with the serializations. The least we’ll get is a very bloated response JSON, that will try to serialize a lot of extra data for every product, if we blindly pass the list of Product objects in the response. In order to resolve this, we will create our own MyProduct class, with the following properties: namespace RESTWebAPI.Models { public class MyProduct { public int ID { get; set; } public string Name { get; set; } public double Price { get; set; } public int Quantity { get; set; } public DateTime ReleaseDate { get; set; } } Then we are also going to create our empty ProductsController, and write the CRUD methods ourselves, by returning instances of MyProduct, instead of “Product”, with all the extra data in the response. One very important thing to note is that the way we are going to implement our WebAPI controller is such that we won’t be converting from IQueryables to Lists. That is, if we have paging, and we have 5 entities per page, we won’t need to load all records from the DB in memory. We won’t be expanding the IQueryables and converting them to other types in foreach loops. We are going to use LINQ, and I hope will like the beauty of it :) public class ProductsController : ApiController { #region GET // READ // GET /api/products public IQueryable<MyProduct> Get() { var entities = new ProductsEntities(); return from product in entities.Products select new MyProduct { ID = product.ID, Name = product.Name, Price = product.Price, Quantity = product.Quantity, ReleaseDate = product.ReleaseDate }; } // GET /api/products/id public MyProduct Get(int id) { var entities = new ProductsEntities(); MyProduct product = (from p in entities.Products where p.ID == id select new MyProduct { ID = p.ID, Name = p.Name, Price = p.Price, Quantity = p.Quantity, ReleaseDate = p.ReleaseDate }).First(); if (product == null) throw new HttpResponseException(new HttpResponseMessage(HttpStatusCode.NotFound)); return product; } #endregion GET #region POST // CREATE // POST /api/products public HttpResponseMessage Post(IEnumerable<MyProduct> products) { var entities = new ProductsEntities(); if (ModelState.IsValid) { // detect conflicts foreach (MyProduct p in products) { if (entities.Products.Where(e => e.ID == p.ID).Count() > 0) throw new HttpResponseException(new HttpResponseMessage(HttpStatusCode.Conflict)); } //persist through EF foreach (MyProduct myProd in products) { Product p = new Product(); p.ID = myProd.ID; p.Name = myProd.Name; p.Price = myProd.Price; p.Quantity = myProd.Quantity; p.ReleaseDate = myProd.ReleaseDate; entities.Products.AddObject(p); } try { entities.SaveChanges(); } catch { throw new HttpResponseException(new HttpResponseMessage(HttpStatusCode.Conflict)); } // create response HttpResponseMessage response = Request.CreateResponse(HttpStatusCode.Created, products); // 201 Created should also return the location of the new resource. // As we are creating more than one we'll only add the location of the first. string uri = Url.Route(null, new { id = products.First<MyProduct>().ID }); response.Headers.Location = new Uri(Request.RequestUri, uri); return response; } throw new HttpResponseException(new HttpResponseMessage(HttpStatusCode.BadRequest)); } #endregion #region PUT // PUT /api/products/5 public HttpResponseMessage Put(int id, MyProduct updatedProduct) { var entities = new ProductsEntities(); if (ModelState.IsValid) { // find product Product p = entities.Products.Where(o => o.ID == updatedProduct.ID).First(); if (p != null) { p.ID = updatedProduct.ID; p.Name = updatedProduct.Name; p.Price = updatedProduct.Price; p.Quantity = updatedProduct.Quantity; p.ReleaseDate = updatedProduct.ReleaseDate; try { entities.SaveChanges(); /* Alternatively the response can be * 200 OK if update was enacted correctly (same as 204 No Content) * The response does not have to include a representation of the entity updated return new HttpResponseMessage(HttpStatusCode.OK); */ return new HttpResponseMessage(HttpStatusCode.NoContent); } catch { throw new HttpResponseException(new HttpResponseMessage(HttpStatusCode.Conflict)); } } // if resource is not found it should be created p = new Product(); p.ID = updatedProduct.ID; p.Name = updatedProduct.Name; p.Quantity = updatedProduct.Quantity; p.Price = updatedProduct.Price; p.ReleaseDate = updatedProduct.ReleaseDate; entities.Products.AddObject(p); // create response HttpResponseMessage response = Request.CreateResponse(HttpStatusCode.Created, updatedProduct); // 201 Created should also return the location of the new resource. string uri = Url.Route(null, new { id = updatedProduct.ID }); response.Headers.Location = new Uri(Request.RequestUri, uri); return response; } throw new HttpResponseException(new HttpResponseMessage(HttpStatusCode.BadRequest)); } // PUT /api/products/?ID=5&ID=6&ID=7 public HttpResponseMessage Put([FromUri]IEnumerable<int> index, IEnumerable<MyProduct> updatedProducts) { var entities = new ProductsEntities(); List<MyProduct> createProducts = new List<MyProduct>(); bool created = false; if (ModelState.IsValid) { foreach (MyProduct updatedProduct in updatedProducts) { Product p = entities.Products.Where(o => o.ID == updatedProduct.ID).First(); if (p != null) { p.ID = updatedProduct.ID; p.Name = updatedProduct.Name; p.Price = updatedProduct.Price; p.Quantity = updatedProduct.Quantity; p.ReleaseDate = updatedProduct.ReleaseDate; } else { created = true; // if resource is not found it should be created p = new Product(); p.ID = updatedProduct.ID; p.Name = updatedProduct.Name; p.Quantity = updatedProduct.Quantity; p.Price = updatedProduct.Price; p.ReleaseDate = updatedProduct.ReleaseDate; entities.Products.AddObject(p); createProducts.Add(updatedProduct); } } try { entities.SaveChanges(); /* Alternatively the response can be * 200 OK if update was enacted correctly (same as 204 No Content) * The response does not have to include a representation of the entity updated return new HttpResponseMessage(HttpStatusCode.OK); */ if (!created) { return new HttpResponseMessage(HttpStatusCode.NoContent); } } catch { throw new HttpResponseException(new HttpResponseMessage(HttpStatusCode.Conflict)); } // we had to create entities // create response HttpResponseMessage response = Request.CreateResponse(HttpStatusCode.Created, createProducts); // 201 Created should also return the location of the new resource. string uri = Url.Route(null, new { id = createProducts.First().ID }); response.Headers.Location = new Uri(Request.RequestUri, uri); return response; } throw new HttpResponseException(new HttpResponseMessage(HttpStatusCode.BadRequest)); } #endregion #region DELETE // DELETE /api/products/5 public HttpResponseMessage Delete(int id) { var entities = new ProductsEntities(); if (ModelState.IsValid) { MyProduct deletedProduct = new MyProduct(); Product entity = entities.Products.Where(p => p.ID == id).First(); deletedProduct.ID = entity.ID; deletedProduct.Name = entity.Name; deletedProduct.Price = entity.Price; deletedProduct.Quantity = entity.Quantity; deletedProduct.ReleaseDate = entity.ReleaseDate; try { entities.Products.DeleteObject(entity); entities.SaveChanges(); } catch { throw new HttpResponseException(new HttpResponseMessage(HttpStatusCode.Conflict)); } return Request.CreateResponse(HttpStatusCode.OK, deletedProduct); /* Alternatively Delete can also return * 202 Accepted if the delete action is accepted but not enacted * 204 No Content if the delete action has been enacted but the response does not include the deleted entity(s) return Request.CreateResponse(HttpStatusCode.NoContent); return Request.CreateResponse(HttpStatusCode.Accepted); */ } throw new HttpResponseException(new HttpResponseMessage(HttpStatusCode.BadRequest)); } // DELETE /api/products/?id=5&id=6&id=7 // FromUri attribute is mandatory for MVC4 RC because the parameter is a complex type // and by default is taken from the request body public HttpResponseMessage Delete([FromUri]IEnumerable<int> index) { var entities = new ProductsEntities(); List<MyProduct> deletedProducts = new List<MyProduct>(); if (ModelState.IsValid) { foreach (int i in index) { MyProduct deletedProduct = new MyProduct(); Product entity = entities.Products.Where(p => p.ID == i).First(); deletedProduct.ID = entity.ID; deletedProduct.Name = entity.Name; deletedProduct.Price = entity.Price; deletedProduct.Quantity = entity.Quantity; deletedProduct.ReleaseDate = entity.ReleaseDate; entities.Products.DeleteObject(entity); deletedProducts.Add(deletedProduct); } try { entities.SaveChanges(); } catch { throw new HttpResponseException(new HttpResponseMessage(HttpStatusCode.Conflict)); } return Request.CreateResponse(HttpStatusCode.OK, deletedProducts); /* Alternatively Delete can also return * 202 Accepted if the delete action is accepted but not enacted * 204 No Content if the delete action has been enacted but the response does not include the deleted entity(s) return Request.CreateResponse(HttpStatusCode.NoContent); return Request.CreateResponse(HttpStatusCode.Accepted); */ } throw new HttpResponseException(new HttpResponseMessage(HttpStatusCode.BadRequest)); } #endregion } Since our controller is named ProductsController, we don’t have to change anything in our grid javascript definition. Once we load it, we can use the Updating feature UI in order to perform the CRUD operations. Note that I’ve added a “Save Changes” button which invokes grid’s saveChanges() function once clicked. This persists all the changes to the server. If we have multiple CREATE requests and batch is true, this will result in one CREATE (POST) request. Same for all the rest. So if we have a mix of CREATE/UPDATE/DELETE and batch is true for all of them., this will result in a maximum of 3 requests. If you would like send the update request immediately to the server, you can also handle the rowAdded / rowDeleted / editCellEnded / editRowEnded, and invoke saveChanges() there. I hope you enjoyed this blog post and that you like the powerful functionality of our updating feature. Let me know if you have any questions about the content and about the attached project. Any suggestions for additional functionality are more than welcome! RESTWebAPI.7z.zip