Generating Geographic Test Data With T-SQL and ChatGPT

When you need lots of test data (100K+) for addresses that represent real places you could leverage ChatGPT and some T-SQL wizardry. 

To do this I asked ChatGPT to give me a list of all the US state capitols and include their longitude and latitude, then turn the data into a TSQL insert statement.

That gives me the longitude and latitude of a point in each state. I created an Address table that will store a list of addresses.

I want to create 100000 rows in the address table.  SQL is really good at manipulating sets of data, so I want to insert rows into the table with a single insert statement, to make the process faster.  To do that I’m going to create a table variable and populate it with a list of integers.  Lots of integers. 

Once the table is created, I can insert a single row to seed the table, with the number 1.  A while loop can be used to double the number of inserts over each iteration by selecting all the rows in the table and multiplying the integer by 2.

Now that we have a table of integers from 1 to 2,000,000, I can use the table to create new data.

I’ll select all the rows from the @Nums table where the number is less than or equal to the number of addresses I want to create.  In this case 100,000.  I also want to join with the table of state capitals, so I can use the city, state, longitude and latitude fields to populate the data.  I can use the modulus operator to match with one of the 50 rows in the state capitols table.

To convert the longitude and latitude to a geography column type, I can use the STGeomFromText function.  If you needed locations with more variance, you could add a small random value to the longitude and latitude of each row as it’s inserted into the address table. The script took about 15 seconds to populate the table, but most of the time was in calculating the geography value, so using the table of numbers to generate data is very fast.

Accelerate By Sorting

A fundamental axiom of computer science is that you cannot make a computer go faster, you can only reduce the work it has to do.  One of the most common ways to do less work is to sort the things your code is processing.  While experimenting with different ways to populate a set of aggregate roots, I found that the simple act of sorting the results from the database could make a significant difference in performance.

Normally when I have a set of parent objects, each having a collection of child objects, I’ll use a foreach loop to iterate over the parent collection.  A LINQ where predicate provides an easy way to filter which children belong to with their parents.  The problem with this is that using the LINQ where predicate creates more allocations than are necessary, and it loops over all the child objects once for each parent object.

foreach (var customer in customers)
{
     _setOrders.SetValue(customer, orders.Where(_ => _.CustomerId == customer.CustomerId.Value).ToList());
}

If my child collection was sorted by the parent identifier, I would only have to loop over the child collection once.  Let’s look at what that looks like.


position = 0;
for (var i = 0; i < customers.Length; i++)
{
     while (position < orders.Length && orders[position].CustomerId == customers[i].CustomerId)
     {
          customers[i].Orders.Add(orders[position]);
          position++;
     }
}

The counter is defined outside the for each loop, and does not get reset to the beginning of the child collection with each iteration of the parent collection.  Doing this eliminates having to iterate over the part of the child collection that has already been covered.  Once the element of the child collection doesn’t match with the current parent element, we can advance to the next parent element.

Using the nested while statement with a counter may reduce the readability of the code, but it’s not overly complex, and the performance benefit speaks for itself. 

So how much of a performance boost do you get from using this technique?  Obviously it depends on how many objects are in the collections.  If there is only one parent and one child, it will be slower, but not by much.  It doesn’t take long to sort a collection of one. 

Using a collection of 100 parents, 1000 children, and 10,000 grandchildren.  There was a 50X improvement.  Iterating over 10,000 grandchildren 1,000 times makes a big difference.

The next question that should spring to mind is how relevant are the time savings when making out of process calls to a database. Again it varies, but with sample data, I’m seeing a 3X improvement.

My suggestion is if you identify a situation where you need a performance improvement that involves iterating over a collection to find set of items….sort it. The impact may surprise you.

Supercharged Dapper Repositories

Part #1: Separate Filters From Queries

Dapper has been a really popular data access library for over a decade, but when it comes to use it with domain entities, there are a few things that can create friction. In this series, I want to show you how to supercharge your Dapper repositories to be faster and more maintainable.

Sample Code: https://github.com/JZuerlein/DapperPopulateAggregate

Paging Data With SQL Server and C#

Learn about several strategies to improve the performance of paging data with .Net and SQL Server, as well as different ways to measure the impact of your software.

NPI data is available at CMS.gov https://download.cms.gov/nppes/NPI_Files.html

SqlConnection Statistics For SQL Server https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql/provider-statistics-for-sql-server

Jeff Zuerlein https://www.linkedin.com/in/jeff-zuerlein-2aa67b7/

Traffic Report: Avoid the 403, Make Authorization Checks In Your UI To Avoid Confusion

Nothing disturbs the flow of automobile traffic like a sink hole big enough to swallow your car. Drivers need to navigate around it, and they become scared to death that another one will appear. HTTP 403 Forbidden error messages have a similar effect. When a user is happily clicking through your web page and suddenly get an error message, because they don’t have authorization to perform an operation, it has a really negative effect. They don’t have an understanding of why it occurred, so it’s up to you to prevent these type of problems.To provide a good user experience, UI elements that lead to an action the user can not perform should be hidden, modified, or never included in the page at all. The trick to making this easier, is to leverage the tools and code you have already put in place to protect Controllers and Actions in your ASP.NET Core application. Let’s take a look at using existing Authorization Policies and Tag Helpers when constructing Views.

IAuthorizationService

Authorization checks in ASP.NET Core can be made imperatively using an instance of the IAuthorizationService. It has a method called AuthorizeAsync that can take in a user, a policy name, and on optional resource to determine if the user has can perform an operation. This means that we can use the same policies that were defined in our StartUp.cs file to find out if a user will be able to execute an action in a controller. To make that happen, we’re going to need the same instance of IAuthorization that was configured in the Startup.cs file. The dependency injection system that comes with ASP.NET Core is also available in Razor views, so we can use it to get a copy of the authorization service. The @inject directive can be added to the top of a Razor page along with a variable declaration to get a reference to the object in the dependency injection system. If you need to include authorization checks on all of your pages, it may make sense to include the authorization service in the _ViewImports.cshtml file. Any using or injection directives you add to the _ViewImports.cshtml file will become part of any view that get rendered.

@using Microsoft.AspNetCore.Authorization
@inject IAuthorizationService AuthorizationService

Once we have used DI to get an instance of IAuthorizationService, we can use it to perform authorization evaluations. Suppose we have a list of resources, and we want to display a link to each resource that the user can view. We could simply loop through the collection of resources and perform an authorization check passing in the resource. The AuthorizeAsync function found in the authorization service will perform the policy evaluation and return the result. We can use that result to decide if the link should be included in the page. For the example below, I’m using a resource that represents a server room, and the user must meet certain criteria in order to enter it.

@foreach (var serverRoom in Model)
{
  if ((await AuthorizationService.AuthorizeAsync(User,
                serverRoom,
               "EnterServerRoom")).Succeeded)
  {
    <a asp-controller="Home" asp-action="ServerRoom”
      class="btn btn-primary">@serverRoom.Name</a>
  }
}

Because we have a list of server rooms, we need to loop through each one, and perform a resource-based authorization check, using the instance of the ServerRoom class, along with the user, and the name of the policy. If the evaluation of the policy succeeds, the link is added to the page. Once you’ve gone through the process of setting up the policies for you application, adding Authorization checks to your View is very straight forward.

TagHelpers

The problem is, including authorization checks in your Razor pages can make the code cumbersome and difficult to read. For this reason, I suggest you utilize a tag helper when implementing authorization in a view. It’s not terribly difficult to implement your own tag helper, and it will save a ton of time. To start, lets look at what information our tag helper will need to perform our authorization checks.

• An instance of the AuthorizationService.
• The user from the HttpContext.
• The resource we are evaluating.
• The name of the policy that we will evaluate.

With these requirements in mind, lets look at how we can build our tag helper.
Start with a new class called AuthorizationTagHelper. We’re going to need to include a few libraries for our class, so we can reference instances of the AuthorizationService and HttpContextAccessor, as well as inherit from the TagHelper base class.

using Microsoft.AspNetCore.Authorization;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Razor.TagHelpers;

Now we need to define the class, and decorate it with an HtmlTargetElement attribute. This will allow us to specify the attribute in our Razor code that causes the TagHelper to modify the HTML. For this case, I’ve chosen to use “authorize”. The first two pieces of information we need to perform an evaluation are the IAuthorizationService and HttpContextAccessor. We can get those via dependency injection, so I’ll create private readonly variables for them, and add them to the constructor of our new class. The resource and name of the policy must be specified by the user, so I’ve created a property for each. Because the user will be assigning them in the view, I want to give each property an attribute name.

[HtmlTargetElement(Attributes = "authorize")]
public class AuthorizationTagHelper : TagHelper
{
  private readonly IAuthorizationService
_authorizationService;
  private readonly IHttpContextAccessor
_httpContextAccessor;

  [HtmlAttributeName("policy-name")]
  public string PolicyName { get; set; }

  [HtmlAttributeName("authorize-resource")]
  public object Resource { get; set; }

  public AuthorizationTagHelper(IAuthorizationService authorizationService, IHttpContextAccessor httpContextAccessor)
  {
    _authorizationService = authorizationService;
    _httpContextAccessor = httpContextAccessor;
  }
}

The last thing we need to do is override the ProcessAsync function from the TagHelper base class.

public override async Task ProcessAsync(TagHelperContext context, TagHelperOutput output)
{
  if (!(await _authorizationService.AuthorizeAsync(_httpContextAccessor.HttpContext.User, Resource, PolicyName)).Succeeded)
  {
    output.TagName = null;
    output.SuppressOutput();
  }
  output.Attributes.RemoveAll("authorize");
}

Inside this method, we need to call AuthorizeAsync just like we would have done in the Razor code. The difference here is that we need to change the output based on the result of the authorization evaluation. If the output should not be included in the page, if the authorization check fails. To do this, we set the element the “authorize” tag was placed on to null to remove it from the page, and call output.SuppressOutput(). If the check succeeded, we don’t have to do anything. For sake of keeping the HTML clean, we can remove the “authorize” tag by calling output.Attributes.RemoveAll(“authorize”);

Here is an example of how our Tag Helper can clean up our Razor code.

<h3>Server Rooms:</h3>
@foreach (var serverRoom in Model)
{
  if ((await AuthorizationService.AuthorizeAsync(User, serverRoom, nameof(CustomPolicies.EnterServerRoom))).Succeeded)
  {
    <a asp-controller="Home" asp-action="ServerRoom" asp-route-id="@serverRoom.Name" class="btn btn-primary">@serverRoom.Name</a>
  }
}

<h3>TagHelperTest</h3>
@foreach (var serverRoom in Model)
{
  <a authorize policy-name=@nameof(CustomPolicies.EnterServerRoom) authorize-resource=serverRoom asp-controller="Home" asp-action="ServerRoom" asp-route-id="@serverRoom.Name" class="btn btn-primary">@serverRoom.Name</a>
}

Looks much cleaner, and you won’t have to worry about traffic hitting those 403 errors!

Chicago Code Camp 2018

I’m very excited to present in this year’s Chicago Code Camp!

This year’s topic is …

Authorization in ASP.NET Core: Tips for securing modern applications!

Microsoft’s transition to a claims based identity model, and policy based authorization opens the door to new techniques to manage modern applications. I’ll briefly explain the basics of Authorization in ASP.NET Core, then take a deeper dive into custom policies, authentication schemes, combining authorization requirements, resource-based, and view-based authorization. We’ll look at how these techniques can decouple authorization logic from business code and facilitate unit testing. Finally, I’ll talk about the benefits of using an authorization server when trying to administer the services feeding your applications.

If you attended my session and want to get a copy of the presentation or demo code, you’ve come to the right place.

Power Point
Source Code

DevExpress: Updating a dxGrid widget.

DevExpress has a line of web controls that I’ve been playing with.
https://js.devexpress.com/

On the whole, I think they are good. I have found the documentation to be plentiful, but the specifics can be scattered in different places. Which is why I’m sharing my experience with their data grid control. It’s more formally called a dxDataGrid. My goal was to simply have an input tag that could hold a foreign key ID. When that value changed, I wanted the data grid to be updated with JSON data from a WebAPI. Pretty common scenario, but I didn’t see a copy & paste example in the documentation that didn’t use Angular. So here is an example to save you some time.

I’m limiting the code to only the relevant parts. It basically displays a list of applications. When you click on one, the data grid gets populated with a list of roles for the application.

Index.cshtml

<div id="applicationList"></div>
<input id=”applicationExternalID” />
<div id=”applicationRolesGrid”></div>

In terms of the markup, this is pretty minimal. DevExpress offers some tag helpers that you can use to create the controls, but they stick a bunch of java script in the middle of the HTML, and I rather keep the java script in separate files. All the configuration can be done in JS.

Index.js

(function ($) {
    
    var applicationList = $("#applicationList").dxList({
        dataSource: DevExpress.data.AspNet.createStore({
            key: "ApplicationID",
            loadUrl: "https://localhost:44356/application/GetApplicationsByTenant",
            onBeforeSend: function (method, ajaxOptions) {
                ajaxOptions.xhrFields = { withCredentials: true };
            }
        }),
        height: 400,
        itemTemplate: function (data) {
            return $("<div>").text(data.DisplayName);
        },
        onItemClick: function (e) {
            var externalID = e.itemData.ExternalID;
            $("#applicatonExternalID").val(externalID);
            $("#DisplayName").text(e.itemData.DisplayName);

            roleSource = DevExpress.data.AspNet.createStore({
                key: "ApplicationRoleID",
                loadUrl: "https://localhost:44356/application/GetApplicationRolesByApplication",
                loadParams: { externalID: externalID },
                onBeforeSend: function (method, ajaxOptions) {
                    ajaxOptions.xhrFields = { withCredentials: true };
                }
            })
            applicationRolesGrid.option('dataSource', roleSource);
            applicationRolesGrid.refresh();
        }
    }).dxList("instance");

    var rolesSource = DevExpress.data.AspNet.createStore({
        key: "ApplicationRoleID",
        loadUrl: "https://localhost:44356/application/GetApplicationRolesByApplication",
        onBeforeSend: function (method, ajaxOptions) {
            ajaxOptions.xhrFields = { withCredentials: true };
        }
    })

    var applicationRolesGrid = $("#applicationRolesGrid").dxDataGrid({
    
        columns: [{
            dataField: "ApplicationRoleID",
            width: 80
        }, {
            dataField: "DisplayName"
        }],

        paging: {
            pageSize: 10
        }
    }).dxDataGrid("instance");

}(jQuery))

So the first thing to take note of is the data stores that are used. DevExpress has created a project on GitHub that allows their client side widgets (controls) to consume data from a ASP.NET web api.
https://github.com/DevExpress/DevExtreme.AspNet.Data
In the case of loading data for the list of applications, I was able to provide the data store with a URL to get the data. You can pass it credentials, and specify a key for each unique record. This is a pretty easy use case. To tell the dxList to use the data store, just set the list’s dataSource parameter to an instance of the data store. The rest is pretty much magic.

I actually create a second data store to get the list of roles for the selected application. This one will require the use of a parameter called loadParams. The WebAPI has a function that takes in a string called externalID, so the loadParams is provided with an object that contain the property externalID.

public async Task<IActionResult> GetApplicationRolesByApplication(string externalID)

This parameter should change every time a different item is clicked in the dxList. So I’ve added a handler to the dxList.onItemClick event. When that happens, I need to update the loadParams object with the new externalID. Here is the sticky party. I expected that I could call the data grid’s refresh method, and it would make the call to the web api and display the new data. But that’s not the case. I actually had to reassign the dxGrid’s dataSource property. That can be done by using the option function to reassign the dataSource property.

applicationRolesGrid.option('dataSource', roleSource);

This is really pretty simple to use, you have to find all the bits that need to be touched. If you know of better ways to do this, feel free to post in the comments.

Using JWTs with the ASP.NET UserManager

Last night I got stuck trying to fix a bug in a REST API that really drove me nuts, so I’ll share in the hopes it will save someone else a headache.
The service uses JWTs for authentication, and are issued in an authentication controller, while the data is provided by another controller. I have a CreateToken method that creates an instance of ApplicationUser for the requested user, and adds some claims.

                        var claims = new[]
                        {
                              new Claim(JwtRegisteredClaimNames.Sub, user.UserName),
                              new Claim(JwtRegisteredClaimNames.Jti, Guid.NewGuid().ToString()),
                              new Claim(JwtRegisteredClaimNames.Email, user.Email)
                        }.Union(userClaims);

I’m sure this looks familiar if you’ve used JWTs in .NET. The method creates a valid token, and returns it to the user. The user can then take that token and pass it along to a different controller for authentication. In my case, I had customized the ApplicationUser to include a property called TenantID, and in the controller, I needed to create an instance of the ApplicationUser, and use the TenantID.

var user = await _userManager.GetUserAsync(User);

The problem was that the GetUserAsync(User) always returned null. After screwing around with it for way too long, I went to GitHub to see what GetUserAsync was actually doing.

https://github.com/aspnet/Identity/blob/dev/src/Core/UserManager.cs

It calls GetUserID, and passes in the ClaimsPrincipal.

        public virtual string GetUserId(ClaimsPrincipal principal)
	        {
	            if (principal == null)
	            {
	                throw new ArgumentNullException(nameof(principal));
	            }
	            return principal.FindFirstValue(Options.ClaimsIdentity.UserIdClaimType);
	        }

It wants to find the first claim associated with the UserIdClaimType….Which is what?

JwtRegisteredClaimNames.Sub

When I was creating the token, I was giving the Sub claim the user’s username, but ASP.NET identity was searching the database for the Id field in the AspNetUsers table in SQL.

UGH.

So I changed the assignment when the token is created, and everything seems to work fine now.

                        var claims = new[]
                        {
                              new Claim(JwtRegisteredClaimNames.Sub, user.Id),
                              new Claim(JwtRegisteredClaimNames.UniqueName, user.UserName),
                              new Claim(JwtRegisteredClaimNames.Jti, Guid.NewGuid().ToString()),
                              new Claim(JwtRegisteredClaimNames.Email, user.Email)
                        }.Union(userClaims);