Saturday, February 22, 2020

.NET Core 3.1, Azure oAuth, Swagger, and ReDoc

This "monster" blog post is about a tiny little GitHub repo I published called (click here -> AzureOAuthSwaggerReDoc) that I created that takes the boiler plate .NET Web API (NOT an ASP.NET Web Project) and adds Swagger, Azure oAuth, and ReDoc all together in a very simple straightforward example you can run for yourself. I kept the Visual Studio "Weather Service" controller as our example.

To clone this repository simply run the command "git clone https://github.com/MikeGriffinReborn/AzureOAuthSwaggerReDoc.git"

The Visual Studio Project


Notice how simple the Visual Studio project is. We have our Controller, the Model class returned by the Controller, the Documentation folder used for ReDoc, and the Startup.cs class where most of the real work is done.




Lets start with Swagger


Swagger is pretty easy to plug in. Getting it to authenticate with oAuth is a different story however and can be frustrating. However, I show you later on in this blog post how to do exactly that. Setting up swagger requires adding some NuGet Packages. Since this example uses .NET Core 3.1 I used the latest release candidates of Swashbuckle, the 5.0.0-rc5 versions.
<PackageReference Include="Swashbuckle.AspNetCore.Swagger" Version="5.0.0-rc5" />
<PackageReference Include="Swashbuckle.AspNetCore.SwaggerGen" Version="5.0.0-rc5" />
<PackageReference Include="Swashbuckle.AspNetCore.SwaggerUI" Version="5.0.0-rc5" />
The Startup.cs code required for merely getting swagger to display and function is pretty simple ...
public void ConfigureServices(IServiceCollection services)
{
    services.AddSwaggerGen(c =>
    {
        c.SwaggerDoc("v1", new OpenApiInfo
        {
            Title = "Azure/oAuth/Swagger/ReDoc/.NET Core 3.1 Api v1",
            Version = "v1",
        });
    }
}      

public void Configure(IApplicationBuilder app, IWebHostEnvironment env)
{
    app.UseSwagger();
    app.UseSwaggerUI(c =>
    {
        c.SwaggerEndpoint("/swagger/v1/swagger.json",
            "Azure/oAuth/Swagger/ReDoc/.NET Core 3.1 Api v1");
    });
}
That gives us a fully working swagger without oAuth support. To see our Swagger API we hit the URL https://localhost:44326/swagger/index.html



Let's add Azure oAuth Support to our Swagger API

Below contains the additional code added to support oAuth in Swagger. In order to run this you will need to add your Azure Tentant ID and an Azure App Registration's Client ID to the appsettings.json file. There are plenty of articles describing how to setup and obtain this information. The important thing here is that you will need to add "Redirect URI" in your Azure App Registration's "Authentication" blade to make this work. The value I added was https://localhost:44326/swagger/oauth2-redirect.html but if your "port" is different for some reason be sure to use your solutions port.
public void ConfigureServices(IServiceCollection services)
{
    services.AddAuthentication(options =>
    {
        options.DefaultScheme = JwtBearerDefaults.AuthenticationScheme;
        options.DefaultAuthenticateScheme = JwtBearerDefaults.AuthenticationScheme;
        options.DefaultChallengeScheme = JwtBearerDefaults.AuthenticationScheme;
    });

    services.AddAuthentication(JwtBearerDefaults.AuthenticationScheme)
        .AddJwtBearer(options =>
        {
            options.Audience = oAuthAudience;
            options.Authority = $"https://sts.windows.net/{AzureTenant}/";
            //options.RequireHttpsMetadata = true;
        })
        .AddJwtBearer("oauth2", options =>
        {
            options.Audience = oAuthAudience;
            options.Authority = $"https://sts.windows.net/{AzureTenant}/";
            //options.RequireHttpsMetadata = true;
        });
           
    services.AddSwaggerGen(c =>
    {
        c.AddSecurityDefinition("oauth2", new OpenApiSecurityScheme
        {
            Type = SecuritySchemeType.OAuth2,
            In = ParameterLocation.Header,
            Scheme = "bearer",
            Flows = new OpenApiOAuthFlows
            {
                Implicit = new OpenApiOAuthFlow
                {
                    AuthorizationUrl = 
                        new Uri($"https://login.microsoftonline.com/{AzureTenant}/oauth2/authorize"),
                    TokenUrl = 
                        new Uri($"https://login.microsoftonline.com/{AzureTenant}/oauth2/token"),
                    Scopes = new Dictionary
                    {
                        { "readAccess", "Access read operations" },
                        { "writeAccess", "Access write operations" }
                    }
                }
            }
        });

        c.AddSecurityRequirement(new OpenApiSecurityRequirement {
        {
            new OpenApiSecurityScheme {
                Reference = new OpenApiReference {
                    Id = "oauth2", //The name of the previously defined security scheme.
                    Type = ReferenceType.SecurityScheme
                }
            },
            new List()
            }
        });
    });
}

public void Configure(IApplicationBuilder app, IWebHostEnvironment env)
{
    app.UseSwaggerUI(c =>
    {
        c.SwaggerEndpoint("/swagger/v1/swagger.json", "Azure/oAuth/Swagger/ReDoc/.NET Core 3.1 Api v1");
        // The two lines below were added ...
        c.OAuthClientId(oAuthAudience);
        c.OAuthAdditionalQueryStringParams(new Dictionary() { { "resource", oAuthAudience } });
    });
}

Notice the "Authorize" Button is now displayed. The authorize button will popup and ask you to login with your Azure Login and return a valid oAuth token to the Swagger API. If you look at the code above I have set it up so that the JWT token is passed in the Authorize HTML Header as a Bearer Token.



Let's round this out by adding the [Authorize] attribute on our controller method.

[Authorize]
public IEnumerable Get()
{
    var rng = new Random();
    return Enumerable.Range(1, 5).Select(index => new WeatherForecast
    {
        Date = DateTime.Now.AddDays(index),
        TemperatureC = rng.Next(-20, 55),
        Summary = Summaries[rng.Next(Summaries.Length)]
    })
    .ToArray();
}

Our REST API is now secured via an oAuth token that can only come from our Azure tenant. At this point you can use a "Machine-to-Machine" clientid/clientsecret to give 3rd parties access or run internally using a Managed Service Identity to authenticate and obtain the token eliminating the need for clientid/clientsecret. It's best to hide any client secrets in the Azure Key Vault and never check them into source code, including the appsettings.json file.

Fully Documented Swagger

Finally, let's decorate out Swagger so that is fully documented for the consumer. We want all of our methods and properties to have descriptions that will convey important information to those consuming our REST API. You can annotate your methods and properties in the usual way shown as below and have those comments carry over into Swagger.
public class WeatherForecast
{
    /// <summary>
    /// Date of Forecast
    /// </summary>
    public DateTime Date { get; set; }

    /// <summary>
    /// Temperature in Celsius
    /// </summary>
    public int TemperatureC { get; set; }

    /// <summary>
    /// Temperature in Fahrenheit
    /// </summary>
    public int TemperatureF => 32 + (int)(TemperatureC / 0.5556);

    /// <summary>
    /// The Forecast
    /// </summary>
    public string Summary { get; set; }
}

And you can decorate your controller methods with Swagger[] attributes as well ...
/// <summary>
/// Gets the Current Weather
/// </summary>
[HttpGet()]
[Route("GetForecast")]
[Produces("application/json")]
[SwaggerOperation("Gets the Current Weather")]
[SwaggerResponse((int)HttpStatusCode.OK, Type = typeof(WeatherForecast[]))]
[Authorize]
public IEnumerable<WeatherForecast> Get()
{
     // logic here
}

To make this work you need to turn on Visual Studio's ability to generate an XML Documentation file. NOTE: You need to do this for both Release and Debug mode. I used my assembly name as the XML file name and that works nicely when combined with the code below..



Finally, add this logic to your ConfigureServices method within the AddSwaggerGen() call to let Swagger know to use your documentation file.

public void ConfigureServices(IServiceCollection services)
{
    services.AddSwaggerGen(c =>
    {
        // Set the comments path for the Swagger JSON and UI.
        var xmlFile = $"{Assembly.GetExecutingAssembly().GetName().Name}.xml";
        var xmlPath = Path.Combine(AppContext.BaseDirectory, xmlFile);
        c.IncludeXmlComments(xmlPath);
    });
}

ReDoc Support

ReDoc is great for OpenAPI/Swagger-generated API Reference Documentation and terribly simple to add to your project. All it takes is adding a very simple HTML file (or tag) to your solution. Notice how I use a relative path to my projects swagger.json file in the "redoc" html tag. This HTML file barely scratches the surface. The idea is you build out a fully documented site describing your REST API. You could include things like field lengths, validation rules, what's required on a POST to create an entity vs the fields required on a PUT to update an object and so on.

<!DOCTYPE html>
<html>
<head>
    <title>ReDoc</title>
    <!-- needed for adaptive design -->
    <meta charset="utf-8" />
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <link href="https://fonts.googleapis.com/css?family=Montserrat:300,400,700|Roboto:300,400,700"
          rel="stylesheet">
    <style>
        body {
            margin: 0;
            padding: 0;
        }
    </style>
</head>
<body>
    <redoc spec-url='/swagger/v1/swagger.json'></redoc>
    <script src="https://cdn.jsdelivr.net/npm/redoc@next/bundles/redoc.standalone.js"></script>
</body>
</html>

But how do we serve up the HTML files? Remember, this is a Web API project not an ASP.NET Web App solution and is not made for serving up static html files. For this we will map our physical "Documentation" folder (see the first image in this blog post) in our Visual Studio solution to the logic path "\Documentation". Note that our physical folder could have been named anything. WE could have stated to use "index.html" as the default file in the call to app.UseDefaultFiles() but that is already the default. Our ReDoc tag is in our Index.html file.
public void Configure(IApplicationBuilder app, IWebHostEnvironment env)
{
    //------------------------------------------------------
    // BEGIN : Serve up Files for ReDoc
    //------------------------------------------------------
    PhysicalFileProvider fileprovider = 
       new PhysicalFileProvider(Path.Combine(env.ContentRootPath, "Documentation"));

    app.UseDefaultFiles(new DefaultFilesOptions
    {
        FileProvider = fileprovider,
        RequestPath = new PathString("/Documentation"),
    });

    app.UseStaticFiles(new StaticFileOptions
    {
        FileProvider = fileprovider,
        RequestPath = new PathString("/Documentation"),
    });

    app.UseFileServer(new FileServerOptions()
    {
        FileProvider = fileprovider,
        RequestPath = new PathString("/Documentation"),
        EnableDirectoryBrowsing = false
    });
    //------------------------------------------------------
    // END : Serve up Files for ReDoc
    //------------------------------------------------------
}

So now we can hit our logical documentation path https://localhost:44326/documentation/ and display our ReDoc UI. Notice that we don't have to include index.html in our path because it looks for that file name by default.



The ReDoc UI provides a much more sophisticated UI than does Swagger. However, ReDoc currently doesn't allow you to execute the API. That's fine however, because the two aren't really competing technologies. You want both Swagger and Redoc. If you look closely at the image above you'll notice I included an image from this blog as the Logo for our ReDoc site. How this this done? Keep reading ...

Adding a Logo to your ReDoc page

The code below shows how we use the Extensions feature to indicate we want to use a custom logo for ReDoc. Notice that we use a relative path to the saltycode.png file contained in our solution (first image in this post).

public void ConfigureServices(IServiceCollection services)
{
    services.AddSwaggerGen(c =>
    {
        c.SwaggerDoc("v1", new OpenApiInfo
        {
            Title = "Azure/oAuth/Swagger/ReDoc/.NET Core 3.1 Api v1",
            Version = "v1",

            // This adds the logo for ReDoc
            Extensions = new Dictionary<string, IOpenApiExtension>
            {
                {
                    "x-logo", new OpenApiObject
                    {
                        {"url", new OpenApiString("/documentation/saltycode.png")},
                        {"altText", new OpenApiString("The Logo")}
                    }
                }
            }
        });
    });
}


That's was a lot of information but the code is really quite simple. I hope this saves you some time ...


Thursday, February 13, 2020

Poor Man's Polly - Retry Logic

When consuming a REST API sometimes there's a glitch and your call to the REST endpoint fails. For example, the REST endpoint might be undergoing a quick restart, or your oAuth token might have just expired, or you might get timeout's during high peak times, and sometimes the endpoint is just ... well, a little buggy.

How can you make your code more resilient so as to provide a better experience for your customers? One way is to retry when things fail. This can end up in cumbersome code if you are not careful. Let's take a look at how to write simple retry logic that's understandable.

However, before we do there is very sophisticated NuGet package out there called Polly. If, after reading this, you decide you need more functionality than this little sample provides then definitely take a look at Polly. This little sample barely scratches the surface of what Polly provides. Still, this is an interesting exercise if you have never written anything like this before.

A Simple Example

The code below is using Flurl to access the free "World Clock API" to get the current UTC time. Notce the way the Retry.Execute() call takes a function as it's parameter. The Retry.Execute() function will, upon failure, retry this call in 1, 3 and 10 seconds respectively and throw an exception if no retries succeed.

static async Task<string> GetUtcFromWorlClock()
{
    return await Retry.Execute<string>(async () =>
    {
        return await "http://worldclockapi.com/api/json/utc/now".GetStringAsync();
    });
}

A More Advanced Example

This example supplies an error handler function and overrides the default (1, 3 and 10) second retry intervals.
static async Task<string> GetUtcFromWorlClockAdvanced()
{
    return await Retry.Execute<string>(
        async () =>
        {
            return await "http://worldclockapi.com/api/json/utc/now".GetStringAsync();
        },
        async (FlurlHttpException ex) =>
        {
            // Called if all retries fail
            Console.WriteLine(ex.Message);
            throw ex;
        },
        // Retry in 1, 5, 10, and 30 seconds
        new int[] { 1000, 5000, 10000, 30000 }
    );
}

The Retry Class

The Retry class is itself surprisingly very simple. If you need a starting point feel free to use this as a reference. However, don't rewrite what is available in Polly. If Polly is overkill, and sometimes it is, and all you need is simple retry logic like this, you might give it a try.
using Flurl.Http;
using System.Threading.Tasks;

namespace PoorMansPolly
{
  public delegate Task<T> MyFunction<T>();
  public delegate Task MyError(FlurlHttpException exception);

  public static class Retry
  {
      private static readonly int[] retries = new[] { 1000, 3000, 10000 };

      public static async Task<T> Execute<T>(MyFunction<T> myFunc, MyError myError = null, int[] delays = null)
      {
          int[] retryDelays = delays ?? retries;
          int tryCount = 0;
          int maxTryCount = retryDelays.Length;

          T response = default(T);

          while (true)
          {
              try
              {
                  response = await myFunc();
                  break;
              }
              catch (FlurlHttpException ex)
              {
                  if (tryCount == maxTryCount)
                  {
                      if (myError != null)
                      {
                          await myError(ex);
                          break;
                      }
                      else
                      {
                          throw;
                      }
                  }

                  await Task.Delay(retryDelays[tryCount++]);
              }
          }

          return response;
      }
  }
}

So that's it, Poor Man's Polly ....

Thursday, February 6, 2020

Async Database Access ?

ADO.NET recently added asynchronous programming methods and these were added in the .NET Framework 4.5. It's a fundamental programming change that EntitySpaces will probably adopt in the future. However, C# makes this still all very easy to do right now with EntitySpaces.

The Problem

Because we have thousands of employees we need to offer an API that allows paging. We want to return our JSON as follows (see below) so the consumer of our REST API has the proper information to display to the user, including whether or not they need to disable the "Next Page" button.

We want to get the total count in one query and the actual data in another query but not synchronously. For this we are going to use the Parallel.Invoke() method from System.Threading.Tasks. Parallel.Invoke() allows you to make any number of calls at the same time and wait for them to return.

Our Desired Return Response
{
  "hasMore": boolean,
  "totalCount": integer,
  "totalReturned": integer,
  "data": [] // employees list
}

The code below is our GetEmployees() call we have exposed in our REST API.
public dynamic GetEmployees(int skip, int take)
{
    EmployeesCollection coll = null;
    int count = 0;

    Parallel.Invoke(
    () =>
    {
        // Get the total count
        count = new EmployeesQuery("e", out var q)
          .Select(q.Count())
          .ExecuteScalar<int>();
    },
    () =>
    {
        // Get "paged" list, must order by when paging 
        coll = new EmployeesQuery("e", out var q)
          .Select(q.EmployeeID, q.LastName).Skip(skip).Take(take)
          .OrderBy(q.LastName.Ascending)
          .ToCollection<EmployeesCollection>();
    });
    
    // For "blog post" simplicity, we return an anonymous object
    return new
    {
        hasMore = count > (skip + take),
        totalCount = count,
        totalReturned = coll.Count,
        data = coll
    };
}

This is the JSON as it would be returned if the "take" was 2 and the "skip" was 0 (our first page). Technically, in the GetEmployees() method above we would limit the "take" to 50, however for brevity sake that was left out of the code.
{
  "hasMore": true,
  "totalCount": 22,
  "totalReturned": 2,
  "data": [
    {
      "EmployeeID": 8,
      "LastName": "Callahan"
    },
    {
      "EmployeeID": 1,
      "LastName": "Davolio"
    }
  ]
}

So, next time you need to fetch several sets of data from your database with EntitySpaces consider Parallel.Invoke(). In my next blog post I will show you how to get the total count and the data in a single query using EntitySpaces.


Sunday, February 2, 2020

EntitySpaces 2020.2.1 Released

In this release of EntitySpaces the "esAlias" class is introduced and the OVER Clause is officially released for Microsoft SQL Server. An instance of the esAlias class can be obtained when aliasing a column via the .As() syntax. We are going to introduce these new features by demonstrating how they come to the rescue in query we need to write.

The Problem


We need to obtain a result set to see the sales amount for each company per year. This part is easy, we can use a simply GroupBy() to obtain the sales amount per each company per year. However, we also need a running cumulative amount per company and the total amount of sales per the the entire company, and for this we need to use the new OVER clause syntax. Notice below how the Cumulative Amount is adding the Amount per each year of the company, while the Total Amount.
                                              Total            Cumulative   Total
CompanyName                          Period   Items  Amount    Amount       Amount 
------------------------------------ -------  ------ --------  -----------  -------
Alfreds Futterkiste                  1997     6      2022.50   2022.50      4273.00
Alfreds Futterkiste                  1998     6      2250.50   4273.00      4273.00
Ana Trujillo Emparedados y helados   1996     2        88.80     88.80      1402.95
Ana Trujillo Emparedados y helados   1997     4       799.75    888.55      1402.95
Ana Trujillo Emparedados y helados   1998     4       514.40   1402.95      1402.95
Antonio Moreno Taquería              1996     1       403.20    403.20      7023.97
Antonio Moreno Taquería              1997     14     5960.77   6363.97      7023.97
Antonio Moreno Taquería              1998     2       660.00   7023.97      7023.97

In fact, we need a nested query to get the entire result set. So we'll attack this query in two steps.

Step 1


Let's create what will become our nested query. We need to obtain the CompanyName, Period, TotalItems, and Amount grouped by CompanyName and the YEAR. We start from the Orders table and join with the Customers table to get the CompanyName and the Order Details table to get the line item information for each order.
OrdersCollection coll = new OrdersQuery("o", out var o)
.InnerJoin<CustomersQuery>("c", out var c).On(c.CustomerID == o.CustomerID)
.InnerJoin<OrderDetailsQuery>("od", out var od).On(od.OrderID == o.OrderID)
.Select
(
    // We're going to grab the aliased columns here for re-use in the outer query later
    c.CompanyName.As("CompanyName"),
    o.OrderDate.DatePart("year").As("Period"),
    o.Count().As("TotalItems"),
    ((1.00M - od.Discount) * od.UnitPrice * od.Quantity).Cast(esCastType.Decimal, 19, 2)
        .Sum().Round(2).As("Amount")
)
.GroupBy(c.CompanyName, o.OrderDate.DatePart("year"))
.OrderBy(c.CompanyName.Ascending, o.OrderDate.DatePart("year").Ascending)
.ToCollection<OrdersCollection>();

The SQL generated by the above query is as follows:
SELECT
   c.[CompanyName] AS 'CompanyName',
   DATEPART(year, o.[OrderDate]) AS 'Period',
   COUNT(*) AS 'TotalItems',
   CAST(SUM(ROUND((((1.00 - od.[Discount]) * od.[UnitPrice]) * od.[Quantity]), 2)) 
      AS decimal(19, 2)) AS 'Amount' 
FROM
   [Orders] o 
   INNER JOIN [Customers] c ON c.[CustomerID] = o.[CustomerID] 
   INNER JOIN [Order Details] od ON od.[OrderID] = o.[OrderID] 
GROUP BY c.[CompanyName], DATEPART(year, o.[OrderDate]) 
ORDER BY c.[CompanyName] ASC, DATEPART(year, o.[OrderDate]) ASC

The result set is as follows:

                                                     Total
CompanyName                              Period      Items       Amount
---------------------------------------- ----------- ----------- -----------
Alfreds Futterkiste                      1997        6           2022.50
Alfreds Futterkiste                      1998        6           2250.50
Ana Trujillo Emparedados y helados       1996        2             88.80
Ana Trujillo Emparedados y helados       1997        4            799.75
Ana Trujillo Emparedados y helados       1998        4            514.40
Antonio Moreno Taquería                  1996        1            403.20
Antonio Moreno Taquería                  1997        14          5960.77
Antonio Moreno Taquería                  1998        2            660.00

Step 2


Okay, so step one is complete. But now we need to obtain a cumulative running total sales amount for the company as each year progresses and we need the total amount of sales for the entire company. These two values are not "per" year so we are going to nest the query above into yet another query and use the OVER clause syntax to get the two additional values. Let's see how that is done.

Don't let this query overwhelm you. If you could collapse the nested query all you would see is the Select() and the OrderBy() on the nested queries columns. Take a close look at how we obtain the "CumulativeAmount" and "TotalAmount" values. Also notice the use of esAlias and that it's knows what alias to use, in this case "sub".

Our full query.
// We grab these aliases in the nested query via "out" parameters
esAlias aliasCompany = null, aliasPeriod = null, aliasAmount = null, aliasItemCount = null;

OrdersCollection coll = new OrdersQuery("q", out var q)
.From<OrdersQuery>(out var sub, () => // mimic a CTE
{
    // Nested Query from Step 1 above
    return new OrdersQuery("o", out var o)
    .InnerJoin<CustomersQuery>("c", out var c).On(c.CustomerID == o.CustomerID)
    .InnerJoin<OrderDetailsQuery>("od", out var od).On(od.OrderID == o.OrderID)
    .Select
    (
        // We're going to grab the aliased columns here for re-use in the outer query later
        c.CompanyName.As("CompanyName", out aliasCompany),
        o.OrderDate.DatePart("year").As("Period", out aliasPeriod),
        o.Count().As("TotalItems", out aliasItemCount),
        ((1.00M - od.Discount) * od.UnitPrice * od.Quantity).Cast(esCastType.Decimal, 19, 2)
            .Sum().Round(2).As("Amount", out aliasAmount)
    )
    .GroupBy(c.CompanyName, o.OrderDate.DatePart("year"));
}).As("sub")
// Now act on "sub" query columns using the OVER syntax
.Select(
   aliasCompany(), aliasPeriod(), aliasAmount(), aliasItemCount(),  
   q.Over.Sum(aliasAmount()).PartitionBy(aliasCompany()).OrderBy(aliasPeriod().Ascending).Rows
      .UnBoundedPreceding.As("CumulativeAmount"),
   q.Over.Sum(aliasAmount()).PartitionBy(aliasCompany()).As("TotalAmount")
)
.OrderBy(aliasCompany().Ascending, aliasPeriod().Ascending)
.ToCollection<OrdersCollection>();

if(coll.Count > 0)
{
    // we loaded data
}
The SQL generated by the above query is as follows:

SELECT
   sub.[CompanyName],
   sub.[Period],
   sub.[Amount],
   sub.[TotalItems],
   -- Notice the OVER() clause usage below
   SUM([Amount]) OVER( PARTITION BY [CompanyName] ORDER BY sub.[Period] ASC ROWS UNBOUNDED PRECEDING )
       AS 'CumulativeAmount',
   SUM([Amount]) OVER( PARTITION BY [CompanyName] ) AS 'TotalAmount' 
FROM
   (
      SELECT
         c.[CompanyName] AS 'CompanyName',
         DATEPART(year, o.[OrderDate]) AS 'Period',
         COUNT(*) AS 'TotalItems',
         CAST(SUM(ROUND((((1.00 - od.[Discount]) * od.[UnitPrice]) * od.[Quantity]), 2)) AS decimal(19, 2))
             AS 'Amount' 
      FROM [Orders] o 
      INNER JOIN [Customers] c ON c.[CustomerID] = o.[CustomerID] 
      INNER JOIN [Order Details] od ON od.[OrderID] = o.[OrderID] 
      GROUP BY c.[CompanyName], DATEPART(year, o.[OrderDate])
   )
   AS sub 
ORDER BY sub.[CompanyName] ASC, sub.[Period] ASC
Our final result set is as follows:

                                              Total            Cumulative   Total
CompanyName                          Period   Items  Amount    Amount       Amount 
------------------------------------ -------  ------ --------  -----------  -------
Alfreds Futterkiste                  1997     6      2022.50   2022.50      4273.00
Alfreds Futterkiste                  1998     6      2250.50   4273.00      4273.00
Ana Trujillo Emparedados y helados   1996     2        88.80     88.80      1402.95
Ana Trujillo Emparedados y helados   1997     4       799.75    888.55      1402.95
Ana Trujillo Emparedados y helados   1998     4       514.40   1402.95      1402.95
Antonio Moreno Taquería              1996     1       403.20    403.20      7023.97
Antonio Moreno Taquería              1997     14     5960.77   6363.97      7023.97
Antonio Moreno Taquería              1998     2       660.00   7023.97      7023.97


That is some pretty powerful functionality that EntitySpaces is packing. Both the full SQL Over Clause syntax along with the full SQL Window Frame syntax are supported.