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.