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.

Friday, January 24, 2020

EntitySpaces - SQL Window Frame Clause

When adding new fluent query API functionality to EntitySpaces it is important to me that the syntax looks and feels like SQL itself. That way, if you understand one you understand the other. In order to finish out the SQL OVER syntax I needed to support the "Window Frame Clause". I liked the way this document outlines the Window Frame Clause and after studying the syntax was able to come with a very elegant representation in EntitySpaces.

SQL - Window Frame Clause

{ ROWS }
{
  { UNBOUNDED PRECEDING | numeric_expression PRECEDING | CURRENT ROW }
  |
  {
    BETWEEN 
      { UNBOUNDED PRECEDING | numeric_expression { PRECEDING | FOLLOWING } | CURRENT ROW }
      AND
      { UNBOUNDED FOLLOWING | numeric_expression { PRECEDING | FOLLOWING } | CURRENT ROW }
  }
}

EntitiySpaces - Window Frame Clause Implementation

{ Rows.UnBoundedPreceding | Rows(x).Preceding | Rows.CurrentRow }.As() 
  |
{ Rows.
  { Between.UnboundedPreceding     | { Between(x).Preceding | Between(x).Following } | Between.CurrentRow     }
  { And.Between.UnboundedFollowing | { And(x).Preceding     | And(x).Following     } | And.Between.CurrentRow }
  .As()
}

Real Working Examples

Notice how similar the two syntaxes are to each other.
/* SQL */ SELECT ...  OVER(... ROWS UNBOUNDED PRECEDING) "Rows"
/* EntitySpaces */            .Rows.UnBoundedPreceding.As("Rows"))

/* SQL */ SELECT ...  OVER(... ROWS BETWEEN 3  PRECEDING  AND CURRENT ROW) as "Rows"
/* EntitySpaces */            .Rows.Between(3).Precending.And.CurrentRow.As("Rows"))


/* SQL */ SELECT ...  OVER(... ROWS BETWEEN 1  PRECEDING  AND 1  FOLLOWING) as "Rows"
/* EntitySpaces */            .Rows.Between(1).Precending.And(1).Following.As("Rows"))
Once I get this finished up I will release updated NuGet packages, should be in the next week or so.


Wednesday, January 22, 2020

Flurl for HTTP Requests

If you're using HttpClient directly I'm here to tell you that there is a better way. Once I found Flurl I never looked back. I use it for all of my HTTP requests. However, before we dive into the advantages of Flurl let's take a look at what a lot of people aren't aware of concerning using HttpClient directly.

Why is this usage of HttpClient bad?


This is the typical usage pattern for how developers use HttpClient. Unfortunately, it's all wrong!!
using(var client = new HttpClient())
{
    //do something with http client
}
As noted in YOU'RE USING HTTPCLIENT WRONG AND IT IS DESTABILIZING YOUR SOFTWARE using HttpClient in the manner shown above eats up socket connections and under heavy load will bring your site to its knees. In fact, Windows will hold the socket connection open servicing the above request for 240 seconds even though you correctly used the 'using' syntax. This will result in SocketException errors under heavy loads.

Flurl to the Rescue

Flurl solves the problem above and then some. I prefer the one-liner fluent HTTP request syntax that Flurl provides. I write Azure micro-services and consume REST API services both on a daily bases so I have put a lot of miles on Flurl, it just works. Using Flurl also lends itself to writing very readable code as well.

Let's look at an example.
// Grab our oAuth token, I'm being lazy here with the use of 'dynamic' type
dynamic accessToken = await "https://mywebsite.com/"
    .AppendPathSegment("oauth/token")
    .PostJsonAsync(new
    {
        client_id = "......",
        client_secret = "......",
        audience = "https://www.mywebsite.com/CoolRestAPI",
        username = "MyUser@CoolRestApi.com",
        password = "......",
        grant_type = "password"
    })
    .ReceiveJson();

// Grab the JWT token ...
string token = accessToken.access_token;

// Now make another call using our oAuth token to get a list of Widgets
WidgetCollection widgets = await "https://mywebsite.com/"
    .AppendPathSegment("/Widget")
    .WithOAuthBearerToken(token)
    .SetQueryParams(new
    {
        skip = 20,
        take = 50,
    })
    .AllowAnyHttpStatus()
    .GetJsonAsync();

Notice that the two HTTP requests made using Flurl above are one-line fluent calls. Also, and here's the kicker, think of the URL after the await keyword as the 'cache' indicator. That is, Flurl only used one HttpClient the above code and that would have been true had I made a 1,000 requests, as long as the URL is the same. So Flurl solves the HttpClient socket resource allocation issue and provides you with an amazingly simple way to make HttpClient calls. I use this approach in all of the micro-services I create. Flurl also works on Xamarin as well.

HttpClientFactory

Okay, so your company is insisting that you use HttpClientFactory via Dependency Injection because that's the Microsoft way. No worries, you can still use Flurl, all you need do is wrap the HttpClient with the FlurlClient and you can still use the Flurl fluent syntax.

[HttpGet]  
public static async Task ControllerMethod(HttpClient httpClient)
{
    // Flurl can use an existing HttpClient
    var flurlCient = new FlurlClient(httpClient);

    dynamic accessToken = await flurlCient.Request()
        .AppendPathSegment("oauth/token")
        .PostJsonAsync(new
        {
            client_id = "......",
            client_secret = "......",
            audience = "https://www.mywebsite.com/CoolRestAPI",
            username = "MyUser@CoolRestApi.com",
            password = "......",
            grant_type = "password"
        })
        .ReceiveJson(); 
}

In the above example the base url is already set to "https://mywebsite.com/" so all we need to do is append out path. I encourage you to give Flurl a try, just add the Flurl.Http NuGet package and you're off.


Major EntitySpaces Enhancements Coming

The SQL "Over" syntax is being added to EntitySpaces along with the ability to truly take advantage of aliased columns.

Aliased Column Enhancements


When you use the SQL "Over" syntax you need to give the derived column an alias in order to be able to use it later in your query. This is now supported by EntitySpaces. Notice the two esQueryItem variables at the beginning the source code below. These are provided to the .As() method as "out" parameters and used later throughout the query. For instance, notice how "rowNumber" is created in the nested query and then used in the outer Select() and the outer Where() clause as well.

SQL 'Over' Syntax Now Supported


Notice the Over.RowNumber() column in the nested query's Select() clause. We need to use this derived column later in the outer Select() and Where() clauses. Thus, we use the 'out' parameter in the .As() so we can use the aliased column later in our query.

C# Query Example

This query doesn't really make sense though it works, it's just to demonstrate the syntax.

esAlias rowNumber = null;

OrdersCollection coll = new OrdersQuery("o", out var o)
.From<OrderDetailsQuery>(out var od, () =>
{
    // Nested Query
    return new OrderDetailsQuery("od", out var subQuery)
    .Select
    (
        subQuery.Over.RowNumber().OrderBy(subQuery.OrderID.Descending).As("RowNumber", out rowNumber)
    )
    .GroupBy(subQuery.OrderID);
}).As("sub")
.Select(rowNumber())
.Where(rowNumber() > 5)
.ToCollection<OrdersCollection>();

if (coll.Count > 0)
{
    // Then we loaded at least one record
}

The SQL Generated by the Query


Notice that the SQL generated by EntitySpaces is exactly what you'd expect. The columns alias 'sub' is used where needed only.

Resulting SQL Query
SELECT sub.[RowNumber] 
FROM
   (
      SELECT ROW_NUMBER() OVER( ORDER BY od.[OrderID] DESC ) AS 'RowNumber' 
      FROM [Order Details] od 
      GROUP BY od.[OrderID]
   )
   AS sub 
WHERE sub.[RowNumber] > @Expr1

List of Supported SQL Over Functions


These are the SQL Over functions supported by EntitySpaces.
See this link for more information => SELECT - OVER Clause (Transact-SQL).
These enhancements are initially coming for Microsoft SQL Server but will be added to the rest of the providers shortly.

Ranking Over Functions

  • DENSE_RANK() OVER
  • NTILE() OVER
  • RANK() OVER
  • ROW_NUMBER() OVER

Aggregate Over Functions

  • COUNT() OVER
  • COUNT_BIG() OVER
  • AVG() OVER
  • MIN() OVER
  • MAX() OVER
  • SUM() OVER
  • STDDEV() OVER
  • STDDEVP() OVER
  • VAR() OVER
  • VARP() OVER

Analytical Over Functions

  • LAG() OVER
  • LEAD() OVER
  • CUM_DIST() OVER
  • FIRST_VALUE() OVER
  • LAST_VALUE() OVER
  • PERCENTILE_CONT() OVER
  • PERCENTILE_DISC() OVER

This query, although not realistic, demonstrates the syntax in a real live working query ...
OrdersCollection coll = new OrdersQuery("q", out var q)
.Select
(
    q.Over.RowNumber().OrderBy(q.EmployeeID.Descending).As("Alias1"),
    q.Over.RowNumber().PartitionBy(q.Freight.Sum() * 10).OrderBy(q.EmployeeID.Descending).As("Alias2"),
    q.Over.Rank().OrderBy(q.EmployeeID.Descending).As("Alias3"),
    q.Over.DenseRank().OrderBy(q.EmployeeID.Descending).As("Alias4"),
    q.Over.Ntile(4).OrderBy(q.EmployeeID.Descending).As("Alias5"),
    q.Over.Sum(q.Freight).PartitionBy(q.EmployeeID).OrderBy(q.EmployeeID.Descending).As("Alias6")
)
.GroupBy(q.EmployeeID, q.Freight)
.ToCollection();

This functionality will be released in the next 10 days or so. Stay tuned. See EntitySpaces for more information.