Deadlocks…Avoid them by only having one save method in your repository.

I’ve been trying to figure out the cause of a SQL deadlock problem, and it dawned on me that preventing deadlocks shouldn’t be that hard. You just have to save data in the same order every time, and the easiest way to do that is to have a repository with one save method. I’ll show you what a deadlock is, and how to prevent them.

What’s the difference between Span of T and Memory of T?

What’s the difference between Span of T and Memory of T? My goal is not to tell you how awesome they are, but to get you over the hump of understanding how they work. I cover what they do and why, along with some description of their internals. Finally, I discuss what the owner consumer model is, and why it’s relevant to Memory of T.

Loading Millions Of Rows Of Test Data In Seconds

Most tools that generate test data do so iteratively. But SQL loves set based operations. With a little T-SQL know how, you can create millions of rows of test data in seconds. You can even leverage public data sources to create more realistic data that conforms to your applications business rules. No third party tools, just a handful of queries. I’m keeping it simple.

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!