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 Step 5.
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 . 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
}