Lesson 3: Pulling It All Together in a Query
We're going to build on our example from Lesson 2 and construct a query from the tables we've created and the data we've imported. If you haven't already, follow the steps in Lesson 2.
You may have noticed the verbs attribute on the tables in our schema.
<xs:element name="Product" gfdata:verbs="Delete,Get,Post,Put">
<xs:complexType>
<xs:sequence>
<xs:element name="Mnemonic" type="xs:string"/>
<xs:element name="Name" type="xs:string"/>
<xs:element name="Price" type="xs:double" />
<xs:element name="ProductId" gfdata:AutoIncrement="true" type="xs:int" />
</xs:sequence>
</xs:complexType>
</xs:element>This attribute is an instruction telling the generator what RESTful verb handlers to generate on the controller. The generated handler for the Get verb will give a simple query of the tables values. For this lesson, we're going to create a more useful view.
In the Solution Explorer right-click on the Data Model.xsd file under the Controllers sub-directory. Select Open With... and select XML (Text) Editor (if this is already your default, you can just open the file for editing).
Find the description of the Orders table and remove Get verb. It should look like this when you're through.
<xs:element name="Order" gfdata:verbs="Delete,Post,Put">
<xs:complexType>
<xs:sequence>
<xs:element name="CustomerId" type="xs:int" />
<xs:element name="OrderId" gfdata:AutoIncrement="true" type="xs:int" />
<xs:element name="ProductId" type="xs:int" />
<xs:element name="Quantity" type="xs:decimal" />
</xs:sequence>
</xs:complexType>
</xs:element>Save the file with Ctrl+S. This will generate the RESTful API without the Get verb for Orders.
If you click on the generated Data Model.Designer.cs file, you can examine the generated code. Search for the OrdersController. Notice that all the controllers are partial classes. This means we can augment any controller with customized verbs if we don't like the default handling. We're going to supply a custom handler for the Get verb that will return a more useful view of the data.
Right Click on the Controllers sub-directory in the Solution Explorer. Select Add > Class....
Enter OrdersController for the name and press Save.
Copy this text and paste it into the module you just created.
namespace WebApplication2.Controllers
{
using System.Collections.Generic;
using System.Threading.Tasks;
using System.Transactions;
using Microsoft.AspNetCore.Mvc;
/// <summary>
/// Controller for <see cref="Order"/> records.
/// </summary>
public partial class OrdersController : ControllerBase
{
/// <summary>
/// Gets a list of <see cref="Order"/> records.
/// </summary>
/// <returns>A list of <see cref="Order"/> records.</returns>
[HttpGet]
public async Task<IActionResult> GetOrders()
{
if (!this.ModelState.IsValid)
{
return this.BadRequest(this.ModelState);
}
List<object> orders = new List<object>();
using (TransactionScope transactionScope = new TransactionScope(TransactionScopeAsyncFlowOption.Enabled))
{
await this.dataModel.Orders.Lock.EnterReadLockAsync(this.lockTimeout);
await this.dataModel.Products.Lock.EnterReadLockAsync(this.lockTimeout);
await this.dataModel.Orders.ProductOrderKey.Lock.EnterReadLockAsync(this.lockTimeout);
HashSet<Product> products = new HashSet<Product>();
foreach (Order order in this.dataModel.Orders)
{
await order.Lock.EnterReadLockAsync(this.lockTimeout);
Product product = order.Product;
if (products.Contains(product))
{
await product.Lock.EnterReadLockAsync(this.lockTimeout);
products.Add(product);
}
orders.Add(new { order.Quantity, product.Name, product.Price, Total = order.Quantity * (decimal)product.Price });
}
return this.Ok(orders);
}
}
}
}Build the solution and run it with F5. In the address box of your browser, type in the URL for the Orders API: https://localhost:PORT#/api/orders. Hit enter. You should get the new view of the Orders joined to the Products.
Lets go back to the code we pasted into the OrdersController.
using (TransactionScope transactionScope = new TransactionScope(TransactionScopeAsyncFlowOption.Enabled))
{All actions inside the ServerDataModel are transactions. This ensures that data that is committed to the in-memory database is also committed to the relational database (or it is completely rolled back).
await this.dataModel.Orders.Lock.EnterReadLockAsync(this.lockTimeout);
await this.dataModel.Products.Lock.EnterReadLockAsync(this.lockTimeout);
await this.dataModel.Orders.ProductOrderKey.Lock.EnterReadLockAsync(this.lockTimeout);All objects in the in-memory database have Read/Write locks on them. You must enter a Read lock before you can read anything from a table, index, foreign index or row. Failure to enter the lock before reading can lead to corrupted results as another thread may be writing while you're reading.
The parameter to this call is the maximum amount of time you're willing to wait (-1 means wait forever). Note that we're locking the foreign index from the Products into the Orders tables. We read this index to find the parent record, so it needs a lock on it as well.
HashSet<Product> products = new HashSet<Product>();
foreach (Order order in this.dataModel.Orders)
{
await order.Lock.EnterReadLockAsync(this.lockTimeout);
Product product = order.Product;
if (products.Contains(product))
{
await product.Lock.EnterReadLockAsync(this.lockTimeout);
products.Add(product);
}This is where we lock the individual records in our query. Recursive locking is not supported, so we need a mechanism to make sure we don't lock the parent record more than once. The HashSet is how we keep track of what has been locked already. Then we iterate through all of the Orders locking the parent Products records as we go. This may seem like a lot of work just to iterate through a table, but this is a multi-threaded database and we need to explicitly protect the information and just the information we need to satisfy a query. You'll see the payoff in the next step.
orders.Add(new { order.Quantity, product.Name, product.Price, Total = order.Quantity * (decimal)product.Price });Once the resources have been locked for reading, we can join the Orders to the Products to create the business view of our data.
With the browser still showing our orders, let's open Postman back up and change the price of a product. Select a new tab in Postman and select Put as the verb as we're going to be updating an existing resource this time. Use the following URL for the Put operation: https://localhost:PORT#/api/products/productExternalKey/LBULB40W. In the context of a REST Resource Address, this URL specifies the unique index and the unique key value where the resource can be found.
As with the other lessons, we need to select Body Raw JSON(application/JSON) to specify the format of the body. Now copy and paste this record into the body of the request:
{
"mnemonic": "LBULB40W",
"name": "40 W Light Bulb",
"price": 0.99
}Your Postman form should look like this:
How hit the Send button. You should get back a status of 200 OK. Switch back to the browser started by the IDE when you debugged your application. Hit the F5 key or refresh button and you'll see the change propagate through the report.
Examine the Output pane in your IDE. When you execute some operation on the relational database you'll see the query in this window. Clear out the Output window and perform this exercise again, changing the price in the Postman form and hit Send. Before refreshing your browser, clear the Output window in the IDE. After you've refreshed the browser, go back and look at the Output window again: no SQL was executed when servicing this query!
This is the real power of the Data Model Generator tool and what separates it from ordinary ORMs. The new price was written through the cache and the query was served directly from memory.