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.

Step 1

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.

Step 2

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).ConfigureAwait(false);
                await this.dataModel.Products.Lock.EnterReadLockAsync(this.lockTimeout).ConfigureAwait(false);
                await this.dataModel.Orders.ProductOrderKey.Lock.EnterReadLockAsync(this.lockTimeout).ConfigureAwait(false);
                HashSet<Product> products = new HashSet<Product>();
                foreach (Order order in this.dataModel.Orders)
                {
                    await order.Lock.EnterReadLockAsync(this.lockTimeout).ConfigureAwait(false);
                    Product product = order.Product;
                    if (products.Contains(product))
                    {
                        await product.Lock.EnterReadLockAsync(this.lockTimeout).ConfigureAwait(false);
                        products.Add(product);
                    }

                    orders.Add(new { order.Quantity, product.Name, product.Price, Total = order.Quantity * (decimal)product.Price });
                }

                return this.Ok(orders);
            }
        }
    }
}

Step 3

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.

What's Going On Here

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).