Tools

To make data-tier application or BACPAC style backup of database and upload it to Azure Blob Storage we need two utilities:

• SqlPackage – create BACPAC file of given database.
• AzCopy – command-line utility to manage Azure Blob Storage

To keep things simple and minimalistic I downloaded both utilities as zip-files so I can keep all stuff in one folder. To update utilities I don’t have to uninstall them first – I just download newer zip-files and write files over.

Here’s how I organized files on disk.

As SqlPackage comes with number of files I put it in separate directory so I don’t have any mess in my backup scripts directory.

Backup script

We will use simple DOS batch file to get work done. This is what our backup script will do:

1. Define variables for:
1. Azure Blob Storage container URL
2. Storage container Shared Access Signature (SAS) token. If you don’t know how to generate SAS token then here’s the official guide by Microsoft: Manage blob containers using the Azure portal (a little bit before middle part of page).
3. Date based name for backup.
4. Database connection string.
2. Bump database to local BACPAC file.
3. Upload file to Azure Blob Storage container.
4. Delete local BACPAC file.

Here’s the script with explaining comments. The only tricky part is getting the date right.

REM Define variables for connection string, storage URL and SAS token
set "CONNSTR=Server=tcp:localhost,1433;Database=db;User ID=X;Password=X;Trusted_Connection=False;Encrypt=False"
set STORAGE=https://my.blob.core.windows.net/container
set "SAS=YOUR_SAS_TOKEN_HERE"
 
REM Create date based file name
REM ~6,4 means from sixth character take next four
REM Correct date parts depend on date format in your system
set CUR_YYYY=%date:~6,4%
set CUR_MM=%date:~3,2%
set CUR_DD=%date:~0,2%
set FILENAME=%CUR_YYYY%%CUR_MM%%CUR_DD%.bacpac
 
REM Bump database to BACPAC file
SqlPackage\sqlpackage.exe /TargetFile:%FILENAME% /Action:Export /SourceConnectionString:%CONNSTR%
 
REM Copy BACPAC to Azure Blob Storage
Azcopy copy %FILENAME% "%STORAGE%?%SAS%"
 
REM Delete local BACPAC file
del %FILENAME% /Q

You can take this script with copy and paste, replace values and make it run before heading to next step.

Backup task on Task Scheduler

We can leave this our script on disk and run it manually but usually we want to run it automatically. One of the most classic options is Windows Task Scheduler. If you know what is SQL Server and what is Windows Task Scheduler you probably don’t need tutorial for scheduled tasks.

There’s one important thing. Make sure you set directory for script. It’s Start in field on the following dialog.

Now it’s time to run the script as scheduled task and make sure it runs to the glory end without any errors.

Wrapping up

We automed MSSQL backups using SqlPackage and AzCopy. Our backups are running on Windows Task Scheduler as nightly batch. I know that using DOS batch is kind of retro approach and we also have Powershell but this time I decided to go with minimalistic set of tools. In the end we have working solution for cases when exporting data-tier application and uploading it to cloud is enough for us.

The post SQL Server database backup to Azure Blob Storage appeared first on Gunnar Peipman - Programming Blog.

Sample project

I start with .NET 5.0 console application ported to .NET 6.0 so I have this normal Program.cs file with namespace and Program class. I really don’t like this new age script style stuff that comes with .NET 6.0. It’s good when teaching students to code in C# but in practice I see it as pretty useless feature.

Suppose we have have console program that runs on Task Scheduler and performs some nightly tasks. Screenshot of Solution Explorer on right illustrates what we have. There are some classes to access local file system and cloud storage. There are also some classes to perform import tasks.

Classes that move data from one place to another usually have some repeated using directives. One good example is System.Data and its child namespaces. Everytime we add new class we have to add those using directives again. This holds also true for importer classes shown on screenshot.

Here’s the fragment on one importer class.

using System.Data;
using System.Data.SqlClient;
using ConsoleApp4.FileAccess;
 
namespace ConsoleApp4.ImportExport;
 
public class CustomersImporter
{
     private readonly IFileClient _fileClient;
     private readonly string _connectionString;

     public CustomersImporter(IFileClient fileClient, string connectionString)
     {
         _fileClient = fileClient;
         _connectionString = connectionString;
     }

     // ...
}

Three using declarations – first three lines of code above – are present also in other import and export classes.

Introducing global usings

C# 10 introduces global usings that are effective over whole project. Let’s add new file GlobalUsings.cs to our project. In this file we define usings that must be available in every class and interface file in our project.

global using System.Data;
global using System.Data.SqlClient;
global using ConsoleApp4.FileAccess;

We can remove these usings from all other files in our project.

Don’t put global usings to same file with class or interface. It’s easy to create a mess when global usings are defined in same file with classes or interfaces. Always keep global usings in separate file.

ASP.NET Core 6 application

Global usings work also in ASP.NET Core applications. There’s _ViewImports.cs file in ASP.NET Core where we can define usings for all views in project. Here’s the example of _ViewImports.cs file after creating a new ASP.NET Core MVC project.

@using WebApplication1
@using WebApplication1.Models
@addTagHelper *, Microsoft.AspNetCore.Mvc.TagHelpers

Global usings work also for ASP.NET Core MVC views and Razor pages. Instead of keeping usings in _ViewImports.cs file we can make them global in global usings file.

global using System.Diagnostics;
global using Microsoft.AspNetCore.Mvc;
global using Microsoft.AspNetCore.Mvc.RazorPages;

Not so fast… Before abandoning _ViewImports.cs for using declarations think for a moment. Views can be taken as a separate context from all other code in your web application. Using declarations for views may be not needed in code files of your application. Don’t pollute your classes with using declarations you only use in views.

Wrapping up

Global usings is useful feature introduced in C# 10. It may also come specially handy when working with code that uses (too) many namespaces. Global usings are also good feature to keep code files cleaner and define all most needed usings in same file. Luckily it works also with ASP.NET Core MVC views and Razor pages.

The post Global usings in C# 10 appeared first on Gunnar Peipman - Programming Blog.

Back in days when I was young and beautyful (now I’m only beautyful) I found utility application to get documents and their metadata out from SharePoint 2001. Although servers were powerful the exporting process was very slow. I took source code of utility and added bunch of sanity checks before exception handling to make sure that code doesn’t hit try-catch blocks if it can be avoided. Instead of 10 days our exports took 4.5 days after my little tweaks.

It’s hard to notice the effect of exceptions if we measure just one exception but they can be fatal to performance when they appear in loops. Let’s dig a little bit deeper.

How exceptions are handled?

Those who think exceptions are just fancy way to return errors are usually surprised when they hear how complex can things be internally – somewhere deep in Common Language Runtime (CLR).

The excellent book Expert .NET 2.0 IL Assembler by Serge Lidin describes what goes on under the hood.

The execution engine of the CLR processes an exception in two passes. The first pass determines which, if any, of the managed handlers will process the exception. Starting at the top of the Exception Handling (EH) table for the current method frame, the execution engine compares the address where the exception occured to the TryOffset and TryLength entries of each EH clause. If it finds that the exception happened in guarded block, the execution engine checks to see whether the handler specified in this clause will process the exception. … If none of the clauses in the EH table for the current method is suited to handling the exception, the execution engine steps up the call stack and starts checking the exception against EH tables of the method that called the method where the exception occured.
…
During the second pass, the finally and fault handlers are invoked with an empty evaluation stack. These handlers do nothing about the exception itself and work only with method arguments and local variables, so the execution engine doesn’t bother providing the exception object.

Without any numbers there’s are two alert reds for me:

1. Processing in two phases
2. Climbing up in method call stack and checking for exception handlers

These two activities both take probably more time than “usual” things we are doing in code.

Exception versus avoiding exception

Let’s get into code and numbers to get better understanding about exceptions effect to performance. I’m using simple program that fills list with some strings and nulls. After this let’s ask string length for each element in list and measure how long it takes.

Let’s start with version where asking string length is in try-catch block.

var list = new List<string>();
for (int i = 0; i < Math.Pow(10, 6); i++)
{
    if (i == 1 || i % 2 != 0)
    {
        list.Add(i.ToString());
    }
    else
    {
        list.Add(null);
    }
}
 
var watch = new Stopwatch();
watch.Start();
 
foreach (var s in list)
{
    try
    {
        var i = s.Length;
    }
    catch (Exception ex)
    {
        var e = ex.Message;
    }
}
 
watch.Stop();
Console.WriteLine(watch.Elapsed);

On my machine this code ran through with 4.58 seconds.

Let’s remove exception handling and replace it with null check so we don’t ask length of null-string.

var list = new List<string>();
for (int i = 0; i < Math.Pow(10, 6); i++)
{
    if (i == 1 || i % 2 != 0)
    {
        list.Add(i.ToString());
    }
    else
    {
        list.Add(null);
    }
}
 
var watch = new Stopwatch();
watch.Start();
 
foreach(var s in list)
{
    if(s == null)
    {
        continue;
    }
 
    var i = s.Length;
}
 
watch.Stop();
Console.WriteLine(watch.Elapsed);

After this modification the code takes 0.008 seconds to run. It’s roughly taken 570 times faster than letting code fall to exception. So, cost of exceptions can be very high.

Wrapping up

Exceptions are powerful feature and without exceptions we should think about our own mechanism how to organize error handling in our code. Like all other powerful features exceptions come with cost. This blog post demonstrated one aspect of it and one popular way how exceptions are abused. The code samples here went from 4.5 seconds to 0.008 seconds by avoiding exceptions. For long processes the win in time can be way bigger.

The post Cost of exceptions appeared first on Gunnar Peipman - Programming Blog.

How things does not work

Adding claims to existing identity seems like small task to accomplish. But, well, it doesn’t go so easy. We can build middleware class and try something like shown here.

foreach(var role in user.Roles)
{
    var claim = new Claim(newIdentity.RoleClaimType, role.Name);
    identity.AddClaim(claim);
}

But it doesn’t work with existing identity. No errors, code runs smooth but role claims are just ignored.

Using claims transformation

There’s correct way to edit existing identity and it’s called claims transformation. Basically we have to write a custom class that implements IClaimsTransformation interface. Documentation doesn’t give much information about it but most important thing is said – we need to clone given identity.

In short, here’s how the process goes:

1. Clone current user identity
2. Add custom claims
3. Return cloned identity

Here’s my claims transformation that adds roles to user identity. Notice that I can use dependency injection to inject instances of service classes to my claims transformation. Remember important trick – we need a clone of current identity to make things work.

public class AddRolesClaimsTransformation : IClaimsTransformation
{
    private readonly IUserService _userService;
 
    public AddRolesClaimsTransformation(IUserService userService)
    {
        _userService = userService;
    }
 
    public async Task<ClaimsPrincipal> TransformAsync(ClaimsPrincipal principal)
    {
        // Clone current identity
        var clone = principal.Clone();
        var newIdentity = (ClaimsIdentity)clone.Identity;
 
        // Support AD and local accounts
        var nameId = principal.Claims.FirstOrDefault(c => c.Type == ClaimTypes.NameIdentifier ||
                                                          c.Type == ClaimTypes.Name);
        if (nameId == null)
        {
            return principal;
        }
 
        // Get user from database
        var user = await _userService.GetByUserName(nameId.Value);
        if (user == null)
        {
            return principal;
        }
 
        // Add role claims to cloned identity
        foreach(var role in user.Roles)
        {
            var claim = new Claim(newIdentity.RoleClaimType, role.Name);
            newIdentity.AddClaim(claim);
        }
 
        return clone;
    }
}

The final thing to do is to register claims transformation with dependency injection in ConfigureServices() method of Startup class.

services.AddScoped<IClaimsTransformation, AddRolesClaimsTransformation>();

Considering you have authentication already configured and it works, the registered transform will get to authentication flow.

Wrapping up

Although adding few claims to existing identity seems like piece of cake, it is not so easy. We cannot just add claims to existing identity and hack it. Claims transformation as custom implementation of IClaimsTransformation interface is the tool we need to use to add claims to existing identity. As we saw we don’t modify existing identity but we clone it, add claims and then return the cloned instance.

The post Adding claims to existing identity appeared first on Gunnar Peipman - Programming Blog.

Prerequisites

To try ASP.NET Core on .NET 5 and Azure App Service out you need the following things:

• Visual Studio 2019 Preview
• .NET 5 RC SDK
• Account on Azure Portal
• App Service Plan and one App Service

For testing I strongly recommend to go with clean App Service Plan and App Service to not affect any live systems you have. After you are done with prerequisites it’s time to create test application.

Creating web application

Run Visual Studio 2019 Preview and create new ASP.NET Core Web Application. Here’s the screenshot with settings I used.

After creating web application you should see classic ASP.NET Core project structure in Visual Studio.

Run your application to make sure there are no issues with Visual Studio 2019 Preview or .NET Core 5. You should see default ASP.NET Core application in your browser.

If there were no issues with application and tooling then let’s publish it to Azure cloud.

Publishing ASP.NET Core 5 application to App Service

Right click on your ASP.NET Core 5 project in Visual Studio 2019 Preview and select publish. Follow these steps:

1. Select Azure as publishing target and click Next
2. Select Azure App Service as specific target and click Next
3. From Azure app service instances list select the instance you created and click Finish

Visual Studio creates publishing profile and opens it automatically.

Click on pen icon after Deployment mode field and select Self-contained as deployment mode. By default target runtime is changed to win-x86. You can leave it like it is but you can also switch over to win-x64. Click Save when done. This is how publishing window should look like after these changes.

And there’s only one thing left to do – click Publish button. After publishing Visual Studio will automatically open your application in new browser window.

NB! If you once published application to App Service then next times publishing may fail. Sometimes there is error about no permissions to write over your application files. If this error comes then just restart your App Service and try again. Make sure you don’t click anywhere in browser window where your application is opened as it will load libraries again.

References

• Self-contained executable with .NET Core 3.0 on Windows, Linux and Raspberry
• Publishing ASP.NET Core 3.0 application as self-contained executable to Azure App Service

The post Running ASP.NET Core 5 RC applications on Azure App Service appeared first on Gunnar Peipman - Programming Blog.

Azure B-series virtual machines. The B-series VMs are ideal for workloads that do not need the full performance of the CPU continuously, like web servers, proof of concepts, small databases and development build environments. These workloads typically have burstable performance requirements. The B-series provides you with the ability to purchase a VM size with baseline performance and the VM instance builds up credits when it is using less than its baseline. Source: B-series burstable virtual machine sizes (Microsoft)

By my own experiences I agree with Jiří Činčura – B1ls VM-s are great for hosting small applications that doesn’t expect big number of visitors. Also applications hosted on those small VM-s shouldn’t be resource eaters. And it’s just 3.20 EUR/month for Linux VM that is alive 24/7.

Optimizing small virtual machines

Still things may get out of hand if we push it to the limits and I have some advice to build simple safety net for your application to avoid bad surprises:

• Use SSD disk – SSD disks are more stable and reliable than regular HDD-s.
• Create swap file – if your application needs more memory then without swap file your VM will be unresponsive black hole for more than half an hour. Swap file is not magic bullet – don’t forget there’s tight IOPS limit. Also programs may need physical memory and run out of it although swap file is available with enough space.
• Keep static content on blob storage – don’t forget you have only small number of IOPS to spend. Static files like images, styles and scripts can be held on Azure blob storage if they put too much load on VM.
• Use Network Security group rules – if you know IP-s from where users come then limit access to your VM to these IP-s. You can use Network security group of your VM to do that. This way bots and malicious scripts doesn’t hit your application and doesn’t make it consume resources.
• Disable OMS agent – OMS agent monitors your VM on guest level and sometimes it takes too much resources. I had to uninstall it from few of my small VM-s. You can do it from VM settings. Just open Extensions page from left menu. Of course, disable OMS agent only if you really need to do so.

B1ls VM-s are not good choice for public web servers and reverse proxies. There are many malicious bots and nasty spammers lurking around in internet and they can be active enough to make web server run out from resources.

Keep the secret, please :) I know few public sites that use B-series VM-s. B1s enough to run WordPress behing reverse proxy and B1ms is enough to have reverse proxy with caching and security modules. It’s easy to hide WordPress from public web and make it accessible only through reverse proxy. If it seems interesting then do it only on your own responsibility.

Sizes of small B-series virtual machines

Here’s the table with smaller B-series virtual machines and their prices. Notice that prices may differ by region.

If you grow out from B1ls then it’s possible to jump to B1s or B1ms. The trick is simple – always take next size and see if it works out well. Of course, it’s also clever to compare prices of other VM series to see if there’s some better option than what B-series has to offer.

Even more penny-picking. You can make VM automatically shut down at given time of day. There is setting for this in Azure portal. If you want VM to automatically wake up in the morning then you must write some script. By example, you can use az vm start command from Azure CLI to start virtual machine. Now you even less as your VM consumes resources only when it runs.

Wrapping up

B-series virtual machines on Azure are cheapest ones to host applications. But with low costs come also very limited resources. Running public sites on cheapest VM-s is risky business and in my experience you need a good game plan to do it. Applications with restricted access are better choice as you don’t have to consider unexpected surprises from public web. B-series VM-s allow your application to grow the cheap way as VM size next to current gives you more resources with small raise in expenses.

The post Hosting applications on Azure B-series virtual machines appeared first on Gunnar Peipman - Programming Blog.

It’s all from real life. Before and during writing this post I tried out all options described in this post. I used real ASP.NET Core project I’m writing for one of my customer and I tried to complete real tasks on Surface Go 2.

1. Remote Desktop to in-prem machine

Maybe the easiest way to build software on Surface Go 2 is to have VPN connection to company network and use Remote Desktop to connect to main dev box. All you need to do is to set up VPN connection in your Surface Go 2 and you are good to go.

If you are working from home like me then probably you are interested in connection to machine located in your home network. Yes, sure, you can open Remote Desktop port in your router and redirect all traffic to your dev box but stop for a moment. It’s not secure. Depending on router and its software you can probably set up VPN right in your router. I strongly recommend you to do it to keep your machines and home network safe.

With RDC you can configure screen size and resolution of session to remote machine. With very low settings you should be able to have stable connection to dev box even over slow network.

Pros

• Energy efficient as Remote Desktop has compact protocol
• Leaves enough resources for other applications to run

Cons

• No chance for offline work
• Not all applications scale on screen over Remote Desktop
• Sometimes it’s not so easy to get VPN to company network
• For home network all security is up to you

2. Remote Desktop to cloud machine

If you don’t want to buy hardware for dev box and you prefer to go with what cloud environments offer then you may have your virtual dev box in cloud. On Azure you can set up different virtual machines and use Windows or Linux as operating system.

On Azure virtual machines have nice feature – automatic shutdown. If you leave virtual machine running when you finish your work then it still consumes resources and grows your bill. And it’s easy to forget virtual machine running. With automatic shutdown feature you can set time when virtual machine is shutdown automatically. If you vote for Azure cloud I strongly recommend to use this feature.

With virtual machines on Azure you have two options to get connected – Remote Desktop and SSH. If you prefer Remote Desktop then it’s practically like the first option discussed here. On Azure you can have virtual network and VPN gateway to make your dev box accessible only over VPN. But be warner – this option comes with high price tag.

Pros

• Energy efficient as Remote Desktop has compact protocol
• Leaves enough resources for other applications to run
• You can set all things up exactly like you wish

Cons

• You need stable network connection to use Remote Desktop
• No chance for offline work
• Not all applications scale on screen over Remote Desktop
• VPN gateway service is expensive

3. SSH to remote machine using Visual Studio Code

No matter where your dev box is located, in some cases you can keeps things smaller when accessing your dev box over SSH – be it Windows or Linux machine. And what’s best – Visual Studio Code has extensions for SSH.

What it means? You can run Visual Studio Code straight on your Surface Go 2 and connect to your project folder in dev box using SSH. User experience is practically the same as using Visual Studio Code for local development.

What’s still up to you is to organize dev machine and host it somewhere – be it company network, cloud or your home network.

Pros

• It’s Visual Studio Code with all goodness running on your machine
• Enough resources to run other programs too

Cons

• No option for offline work
• You need stable internet connection
• Hosting and managing of dev box is up to you

4. Using Visual Studio Codespaces with Visual Studio Code

Visual Studio Codespaces is online service by Microsoft that you can use to run virtual dev box on cloud and write code using Visual Studio Code or online code editor provided by service.

This option is a little bit similar to SSH to remote machine using Visual Studio Code but instead of machine set up by you there’s VM set up by Codespaces service. When configuring this machine you will specify code repository, size of machine and idle timeout. If you have not used your VM for specified amount of time then Codespaces service will shut it down to save expenses.

Pros

• Usual Visual Studio Code experience with all its goodness

Cons

• Not an option for offline work
• Service has only Linux boxes right now
• Not much options to configure your VM

5. Local development with Visual Studio Code

If you don’t have complex application to build then it’s also possible to build it locally on Surface Go 2. This option is perfect for more lightweight solutions like web applications, Azure Functions and console applications.

Some of my readers probably want to ask now – but what about database? Surface Go 2 is not for running SQL Server, even Express Edition will be overkill for this machine.

There are few things you can do:

1. Connect to SQL Server hosted on some other machine. It’s not perfect choice, specially when SQL Server is located thousands of miles away. The closer you are to SQL Server the better connection you have.
2. Use SQLite on Surface Go 2. You can use SQLite as it is small and lightweight. You get something close to relational database but consider one important thing – SQLite is not SQL Server. Make sure you have unit and integration tests for your code to make sure that your LINQ-queries are translated to correct SQL Server commands. SQLite is supported by NHibernate and Entity Framework Core.
3. Use in-memory database of Entity Framework Core. In-memory database that comes with Entity Framework Core doesn’t have any files on disk. It lives only in machine memory until your program is running. When program is stopped then in-memory database is gone. Again – in-memory database is not same as SQL Server and I strongly recommend you to use tests to make sure that code you wrote works well with SQL Server too.

If you are using Entity Framework Core or NHibernate as you object-relational mapper you can be sure both of them support SQLite. If you wrote your own data layer then things may get very complicated on supporting different database products and I highly recommend you to use database in some other machine.

Pros

• Visual Studio Code with all plugins and extensions you need
• Work online or offline
• It’s possible to use SQLite or in-memory database to mimic real databases

Cons

• When offline you have no access to real databases like MSSQL, MySL, Postgre etc
• Building of solution may drain the battery fast
• When travelling it’s easy to forget to commit code changes back to repository

Conclusion

Although Surface Go 2 is not for software development but targeted more to business people it is still possible to use it for coding. Sure, it will not be the main dev box but it’s excellent complement to it for days when there are meetings at customer site or you need to travel from one place to another. If you really plan to use Surface Go 2 for software development then I suggest LTE version as it’s always connected and you don’t depend on availability and stability of near-by WiFi.

The post Developing software on Surface Go 2 appeared first on Gunnar Peipman - Programming Blog.

What we want to achieve

Here’s the simple overview of architecture components involved to blob storage topic. On this diagram components are connected the way I want it to be finally.

Publisher must have direct access to blob storage from specified static IP. Backend machines must also be able to access blob storage as they publish newly combined and minified CSS and JS files to blob storage automatically. It’s clear that Azure CDN must be able to access blob storage. The last accessor is blog reader from public internet. Readers access static content only through Azure CDN.

Virtual network for virtual machines

As my blog is hosted on virtual machines (VM) then I have virtual network where all these VM-s belong. One of them is directly visible to public internet while others are not directly accessible.

If I restrict access to blob storage then somehow VM-s must still have access to it and for this we need service endpoint to blob storage. This will be the link between vnet and blob storage. 

Configuring storage account

The easiest way to restrict access to blob storage and create service endpoint at same time is to configure storage account. With just few moves we will get all settings in place.

Here are the steps I did:

1. Open storage account settings in Azure portal
2. Move to Firewalls and virtual networks section
3. Allow access from selected networks only
4. Select your vnet and let Azure to add service end-point there for blob storage
5. To firewall address ranges add the IP-s you use to access blob storage (static IP in office or home)
6. Important! Add address range of Azure CDN service: 147.243.0.0/16
7. Make sure you have selected checkbox “Allow trusted Microsoft services to access this storage account”
8. Leave all other settings like they are and click Save.

The whole process takes few minutes of time and I faced no single issue. When file is directly requested from blob storage then the result is XML with error information.

Wrapping up

It’s great how I can control and configure services on Azure I use to host this blog. Keeping expenses under control by restricting access to blob storage may come with some small financial wins. But more important is to avoid mistakes like allowing blog to generate direct links to blob storage when Azure CDN is there to take all static content as close to reader as possible. I’m even more suspicious on aggressive bots that are trying to index things too frequently and therefore generate big amount of storage transactions.

The post Accessing restricted blob storage from virtual networks and Azure CDN appeared first on Gunnar Peipman - Programming Blog.

Classic console application

Here’s the classic console application. We have seen it thousands of times and we know what it does and how it works.

using System;
 
namespace ConsoleApp6
{
    class Program
    {
        static void Main(string[] args)
        {
            Console.WriteLine("Hello World!");
        }
    }
}

When students see this they usually have more than one question:

1. What is doing “using System”?
2. What is “namespace”?
3. What is string “string[] args”?

Besides question there’s too much noice for them and all work on whiteboard happens inside Main() method.

What is top-level program?

With C# 9 we can skip all the noise and use top-level programs. Same code with this new feature looks like this.

using System;
 
Console.WriteLine("Hello World!");

Just two lines, no spacing and all the screen space is for my examples. Great!

Of course, I must have using directives but here’s the little trick to calm youngsters down.

using System; // let's talk about it later
 
Console.WriteLine("Hello World!");

Not only for teachers. Top-level programs can be used also in practice when writing real code. It is perfect when writing simple utility applications that doesn’t have much code.

Top-level programs are C# language feature and it doesn’t come down to Common Language Runtime (CLR). C# compiler produces Program class and Main() method (almost) like it was before.

But what about args argument of Main() method? It’s magically here and available in top-level programs. Here’s how to write out all arguments of program.

for(var i = 0; i < args.Length; i++)
{
    Console.Write(i);
    Console.Write(" ");
    Console.WriteLine(args[i]);
}

Using methods in top-level programs

We can write methods too. Notice that methods in top-level programs must be static.

using System; // let's talk about it later
 
Console.WriteLine(SayHello("students"));
 
static string SayHello(string name)
{
    return "Hello, " + name;
}

It’s interesting to see how methods in top-level programs look like functions in functional languages.

Using classes in top-level programs

We can also use classes and other things like structures, enums etc. The next code sample demonstrates primitive greeter class.

using System; // let's talk about it later
 
var greeter = new Greeter();
 
var helloTeacher = greeter.Greet("teacher");
var helloStudents = SayHello("students");
 
Console.WriteLine(helloTeacher);
Console.WriteLine(helloStudents);
 
static string SayHello(string name)
{
    return "Hello, " + name;
}
 
public class Greeter
{
    public string Greet(string name)
    {
        return "Hello, " + name;
    }
}

In this point things may get a little bit messy as there are methods and classes coming one after another.

Order matters! In top-level programs type definitions must come after top-level statements. It means after everything that goes to Program class.

Top-level programs after compiling

Classes in top-level programs are not particularly interesting as they are compiled like usual. But things get interesting with Program class and Main() method. Here’s the decompiled code of sample with greeter class.

using System;
using System.Runtime.CompilerServices;
 
[CompilerGenerated]
internal static class <Program>$
{
    private static void <Main>$(string[] args)
    {
        string str1 = new Greeter().Greet("teacher");
        string str2 = <Program>$.<<Main>$>g__SayHello|0_0("students");
        Console.WriteLine(str1);
        Console.WriteLine(str2);
    }
 
    internal static string <<Main>$>g__SayHello|0_0(string name)
    {
        return "Hello, " + name;
    }
}

Important things to notice:

• Program class has CompilerGenerated attribute
• Program class, Main() method and SayHello() method have obfuscated names that make it impossible to refer to them in .NET languages.
• All top-level code members are in private or internal scope and invisible to other libraries.

Of course, it doesn’t stop us using obfuscated names if we use reflection. But I really don’t see any use case where we would need it and where we really cannot use some other approach.

Wrapping up

Top-level programs is nice C# language feature that cleans up Main() method of Program class by generating class and method automatically. All we have to do is to write the code and build it. Top-level programs have different Program class and Main() method as these are compiler generated. These two are not directly accessible from other libraries although we can do everything we want with reflection. Practical use cases for top-level programs are teaching coding to beginners and writing simple utility applications.

The post Top-level programs in C# 9.0 appeared first on Gunnar Peipman - Programming Blog.

ToSql() extension method

I wrote simple ToSql() extension method for IQueryable generated by NHibernate LINQ. It also writes out parameters.

public static class NHibernateExtensions
{
    public static string ToSql(this IQueryable query)
    {
        var provider = query.Provider as DefaultQueryProvider;
        if(provider == null)
        {
            return null;
        }
 
        var sessionImpl = provider.Session;
        var factory = sessionImpl.Factory;
        var linqExpression = new NhLinqExpression(query.Expression, factory);
        var translatorFactory = new ASTQueryTranslatorFactory();
        var translator = translatorFactory.CreateQueryTranslators(linqExpression, null, false, sessionImpl.EnabledFilters, factory).First();
        var parameters = linqExpression.ParameterValuesByName.ToDictionary(x => x.Key, x => x.Value.Item1);
 
        var result = translator.SQLString;
        foreach (var parameter in parameters)
        {
            result += "\r\n" + parameter.Key + ": " + parameter.Value;
        }
 
        return result.Trim();
    }
}

ToSql() extension method uses only classes needed to translate LINQ expression tree to SQL.

Using ToSql() extension method

The following piece of code shows how to use ToSql() extension method. To find out more about my IDataMapper interface that mimics DbContext of Entity Framework Core, please read my blog post NHibernate on ASP.NET Core.

var projectQuery = _dataMapper.AsNoTracking()
                              .Projects
                              .Fetch(c => c.Customer)
                              .Fetch(c => c.Mediator)
                              .Where(p => p.Id == id);
 
var projectSql = projectQuery.ToSql();
var projects = await projectQuery.FirstOrDefaultAsync();

When projectSql variable is evaluated the query is not executed. It is only translated to SQL. Query is executed when projects variable is evaluated as then data is actually asked from projectQuery.

Here’s the result of ToSql() extension method.

select 
    project0_.Id as id1_2_0_, 
    customer1_.Id as id1_1_1_, 
    customer2_.Id as id1_1_2_, 
    project0_.Title as title2_2_0_, 
    project0_.ProjectKey as projectkey3_2_0_, 
    project0_.IsActive as isactive4_2_0_, 
    project0_.CustomerId as customerid5_2_0_, 
    project0_.MediatorId as mediatorid6_2_0_, 
    project0_.Deadline as deadline7_2_0_, 
    project0_.Description as description8_2_0_, 
    customer1_.Name as name2_1_1_, 
    customer2_.Name as name2_1_2_ 
from 
    Projects project0_ 
    left outer join Customers customer1_ on 
        project0_.CustomerId=customer1_.Id 
    left outer join Customers customer2_ on 
        project0_.MediatorId=customer2_.Id 
where 
    project0_.Id=?
 
p1: d9571e6b-fa73-4553-9c90-57aa7c050298

NB! ToSql() extension method doesn’t give formatted SQL. It gives it like it is for executing. You can use Poor Man’s T-SQL Formatter service to format queries returned by ToSql() extension method.

Wrapping up

Translating NHibernate LINQ expression tree to SQL without executing query against database server is trick we can use to investigate generated SQL when writing complex queries. I’m using ToSql() extension method also when optimizing slow queries to find out what was actually generated by NHibernate. For more serious work I recomment using advanced tools to monitor NHibernate.

The post Translating NHibernate LINQ query to SQL appeared first on Gunnar Peipman - Programming Blog.

NB! In this post I will extend some parts of code shown in my blog post NHibernate on ASP.NET Core. Although I copied some code from this post here I suggest you to take time Myand see my previous work.

Getting NHibernate to ASP.NET Core

I defined IMapperSession interface that follows semantics of DbContext from Entity Framework Core. It’s similar but more general. We can use it with NHibernate, Entity Framework Core, Dapper, etc.

public interface IMapperSession
{
    void BeginTransaction();
    Task Commit();
    Task Rollback();
    void CloseTransaction();
    Task Save(Book entity);
    Task Delete(Book entity);
 
    IQueryable<Book> Books { get; }
}

I also implemented this interface for NHibernate. Here’s my demo code that is short and not very fool-proof.

public class NHibernateMapperSession : IMapperSession
{
    private readonly ISession _session;
    private ITransaction _transaction;
 
    public NHibernateMapperSession(ISession session)
    {
        _session = session;
    }
 
    public IQueryable<Book> Books => _session.Query<Book>();
 
    public void BeginTransaction()
    {
        _transaction = _session.BeginTransaction();
    }
 
    public async Task Commit()
    {
        await _transaction.CommitAsync();
    }
 
    public async Task Rollback()
    {
        await _transaction.RollbackAsync();
    }
 
    public void CloseTransaction()
    {
        if(_transaction != null)
        {
            _transaction.Dispose();
            _transaction = null;
        }
    }
 
    public async Task Save(Book entity)
    {
        await _session.SaveOrUpdateAsync(entity);
    }
 
    public async Task Delete(Book entity)
    {
        await _session.DeleteAsync(entity);
    }
}

I also provided solution for adding NHibernate to services collection in application Startup class.

public void ConfigureServices(IServiceCollection services)
{
    var connStr = Configuration.GetConnectionString("DefaultConnection");
 
    services.AddNHibernate(connStr);
    services.AddControllersWithViews();
}

Not excellent but works! It would be more polite and perfect to use options approach for getting NHibernate settings from application configuration to AddNHibernate() extension method. I don’t focus in it here but leave it as a topic for one of my future posts about NHibernate.

Here is AddNHibernate() extension method from my previous post.

public static class NHibernateExtensions
{
    public static IServiceCollection AddNHibernate(this IServiceCollection services, string connectionString)
    {
        var mapper = new ModelMapper();
        mapper.AddMappings(typeof(NHibernateExtensions).Assembly.ExportedTypes);
        HbmMapping domainMapping = mapper.CompileMappingForAllExplicitlyAddedEntities();
 
        var configuration = new Configuration();
        configuration.DataBaseIntegration(c =>
        {
            c.Dialect<MsSql2012Dialect>();
            c.ConnectionString = connectionString;
            c.KeywordsAutoImport = Hbm2DDLKeyWords.AutoQuote;
            c.SchemaAction = SchemaAutoAction.Validate;
            c.LogFormattedSql = true;
        });
        configuration.AddMapping(domainMapping);
 
        var sessionFactory = configuration.BuildSessionFactory();
 
        services.AddSingleton(sessionFactory);
        services.AddScoped(factory => sessionFactory.OpenSession());
        services.AddScoped<IMapperSession, NHibernateMapperSession>();
 
        return services;
    }
}

There’s actually no SQL logging when this solution is used.

Logging SQL to debug window

NHibernate sessions may have interceptor that give us access to some internals of NHibernate query execution. Bad thing is there can be only one interceptor set for NHibernate session. If we have more things to do through interceptor then we have to add code to our existing interceptor. We can use custom interceptor to log SQL.

My previous blog post about SQL logging in NHibernate demonstrated simple SQL logging interceptor that logged SQL to debug window.

public class SqlDebugOutputInterceptor : EmptyInterceptor
{
    public override SqlString OnPrepareStatement(SqlString sql)
    {
        Debug.Write("NHibernate: ");
        Debug.WriteLine(sql);
 
        return base.OnPrepareStatement(sql);
    }
}

Although NHibernate has IInterceptor interface defined it’s better to use EmptyInterceptor as base class so we don’t have to implement methods we don’t need.

Logging SQL to ASP.NET Core logger

We can use logger interceptor above to log SQL also to ASP.NET Core logger. For this we have to extend interceptor a little bit. Interceptor needs instance of ASP.NET Core logger and we will provide it through dependency injection. I also rename this interceptor to SqlLoggingInterceptor. Here’s the code.

public class SqlLoggingInterceptor : EmptyInterceptor
{
    private readonly ILogger<IMapperSession> _logger;
 
    public SqlLoggingInterceptor(ILogger<IMapperSession> logger)
    {
        _logger = logger;
    }
 
    public override SqlString OnPrepareStatement(SqlString sql)
    {
        if (_logger != null)
        {
            _logger.LogDebug(sql.ToString());
        }
 
        return base.OnPrepareStatement(sql);
    }
}

In ConfigureServices() method of Startup class we can add our interceptor to dependency injection like shown here.

#if (DEBUG)
            services.AddScoped<IInterceptor, SqlLoggingInterceptor>();
#endif

If application is released then SQL queries will not be logged.

Now we have to use SqlLoggingInterceptor in AddNHibernate() extension method. We can use services collection when creating new ISession to get interceptor if it is available. Here is the dependencies block from AddNHibernate() extension method.

services.AddSingleton(sessionFactory);
services.AddScoped(serviceProvider => 
{
    var interceptor = serviceProvider.GetService<IInterceptor>();
 
    return sessionFactory.OpenSession()
                         .SessionWithOptions()
                         .Interceptor(interceptor)
                         .OpenSession();
});
services.AddScoped<IMapperSession, NHibernateMapperSession>();

All log providers that log debug level messages will get generated SQL when it is written to log by interceptor.

NB! This solution doesn’t work with stateless sessions as stateless sessions doesn’t support interceptors. For stateless sessions we need some other approach and I will cover this topic in near future here in this blog.

What to do with stateless sessions?

Stateless sessions in NHibernate are great for reading data but they don’t support interceptors. It’s possible to hook to underlying connection and monitor it but it means building up a custom solution that may turn to be complex one.

If you are in hurry then the easiest thing to do is to offer few bucks and use Azure Monitor/Application Insights service. It is able to monitor external dependencies automatically and one of those dependencies is SQL database. You can run your ASP.NET Core application on your dev box and see SQL logs in Azure Portal.

Wrapping up

Using custom interceptor to log SQL queries in NHibernate to ASP.NET Core logger is simple but a little bit limited. It works with ISession that is stateful but not with IStatelessSession that doesn’t have state and therefore doesn’t support interceptors. As long as stateful sessions are used the logging interceptor logs all SQL that NHibernate sends out. It doesn’t matter what querying API is used – is it ICriteria, QueryOver or LINQ – all SQL will be written to logs.

The post How to log NHibernate SQL to ASP.NET Core loggers appeared first on Gunnar Peipman - Programming Blog.

Legacy warning! The code here and solutions are for classic ASP.NET MVC. If you are working on legacy application written on it and you need to introduce dependency injection there or you need to get rid of ObjectFactory class of old Structuremap then this post is for you. In this post I stay in context of legacy app I’m working on and I don’t want to introduce any big changes to code base.

As long as we work in Application class or controllers things are quiet easy. At application class we create DI container, configure it and then set up controller factory that we introduce to ASP.NET MVC.

But there are ASP.NET and ASP.NET MVC components that doesn’t support dependency injection. Some examples:

• layout pages and views
• membership and roles providers
• controller attributes

There’s no straightforward way to make those things use dependency injection like we want. There was ObjectFactory class in Structuremap but it is deprecated. As a last option we can use Service Locator pattern (once pattern but now anti-pattern). It provides us with static instance that resolves dependencies for us.

But let’s get to dependency injection step by step.

Using Structuremap for dependency injection

I am long time user of Structuremap. It is DI/IoC container that supports assembly scanning and dependency definition registries. Registries are simple classes where containers are configured.

Here’s the primitive example of Structuremap registry.

public class MyRegistry : Registry
{
    public MyRegistry()
    {
        For<IDataMapper>().Use<NHibernateMapperSession>()
                          .LifecycleIs<ThreadLocalStorageLifecycle>();
 
        For<ILogger>().Use<NLogWrapper>()
                      .LifecycleIs<ThreadLocalStorageLifecycle>();
 
        For<IInvoiceGenerator>().Use<InvoiceGenerator>()
                                .LifecycleIs<ThreadLocalStorageLifecycle>();
    }
}

And here’s how Structuremap containers are configured (scanning is also possible).

var container = new Container(x =>
{
    x.AddRegistry<MyRegistry>();
    x.AddRegistry<SomeOtherRegistry>();
});

If instance is needed then it’s easy to get it from container.

var logger = container.GetInstance<ILogger>();

It’s all nice but it’s not enough to get dependency injection to classic ASP.NET MVC. We need some more pieces.

Building controller factory

Controller factory is MVC component that builds controller instances when framework needs one. We need custom controller factory for Structuremap to get dependencies resolved and controller instance built when request comes in.

public class StructuremapControllerFactory : DefaultControllerFactory
{
    private readonly Container _container;
 
    public StructuremapControllerFactory(Container container)
    {
        _container = container;
    }
 
    protected override IController GetControllerInstance(RequestContext requestContext, Type controllerType)
    {
        if (controllerType == null)
        {
            return null;
        }
 
        return _container.GetInstance(controllerType) as Controller;
    }
}

We have to introduce out controller factory to MVC when web application starts.

var factory = new StructuremapControllerFactory(container);
ControllerBuilder.Current.SetControllerFactory(factory);   

Notice that previously initialized Structuremap container is provided to controller factory.

Now we can write controllers like we are doing it right now in ASP.NET Core MVC using framework-level dependency injection.

public class MyDemoController : Controller
{
    private readonly ILogger _logger;
    private readonly IAlertService _alertService;
    
    public MyDemoController(ILogger logger, IAlertService alertService)
    {
        _logger = logger;
        _alertService = alertService;
    }
 
    // Controller actions follow
}

But this not the end of the show – we have some famous components that doesn’t go through dependency injection.

Using built-in service locator

Now let’s focus on our old friends like role provider, membership providers and others that don’t support dependency injection. For these components we have to go with built-in service locator.

[See võiks ehk sinine olla?] Service locator is static class we can use everywhere in our code to resolve dependencies. I don’t like this pattern much because resolving of dependencies happens inside class that needs these and therefore class has one more responsibility.

ASP.NET MVC provides us with DependencyResolver class. By it’s nature it is typical service locator. Here’s how we can use it to return list of roles in classic ASP.NET roles provider.

public class MyRoleProvider : RoleProvider
{
    // ...
 
    public override string[] GetAllRoles()
    {
        var repo = DependencyResolver.Current.GetService<IRoleRepository>();
        var roles = from r in repo.ListAll()
                    select r.Name;
        return roles.ToArray();
    }
 
    // ...
}

No constructor injection, no property injection – just a primitive call to static scope.

Getting DependencyResolver work with Structuremap is a little bit tricky. We have to write Structuremap based dependency resolver that DependencyResolver can internally use when detecting instances. We don’t have to invent the wheel as there’s excellent blog post Configuring MVC 4 with StructureMap by Bia Securities blog. We need StructureMapDependencyResolver and StructureMapDependencyScope classes.

public class StructureMapDependencyResolver : StructureMapDependencyScope, IDependencyResolver
{
    public StructureMapDependencyResolver(IContainer container) : base(container)
    {
    }
 
    public IDependencyScope BeginScope()
    {
        var child = Container.GetNestedContainer();
        return new StructureMapDependencyResolver(child);
    }
}
 
public class StructureMapDependencyScope : ServiceLocatorImplBase, IDependencyScope
{
    protected readonly IContainer Container;        
 
    public StructureMapDependencyScope(IContainer container)
    {
        if (container == null)
        {
            throw new ArgumentNullException(nameof(container));
        }
        
        Container = container;
    }        
 
    public override object GetService(Type serviceType)
    {
        if (serviceType == null)
        {
            return null;
        }
 
        return serviceType.IsAbstract || serviceType.IsInterface
            ? Container.TryGetInstance(serviceType)
            : Container.GetInstance(serviceType);
    }
 
    public IEnumerable<object> GetServices(Type serviceType)
    {
        return Container.GetAllInstances(serviceType).Cast<object>();
    }
 
    protected override IEnumerable<object> DoGetAllInstances(Type serviceType)
    {
        return Container.GetAllInstances(serviceType).Cast<object>();
    }
 
    protected override object DoGetInstance(Type serviceType, string key)
    {
        if (string.IsNullOrEmpty(key))
        {
            return serviceType.IsAbstract || serviceType.IsInterface
                ? Container.TryGetInstance(serviceType)
                : Container.GetInstance(serviceType);
        }
        return Container.GetInstance(serviceType, key);
    }
 
    private bool disposedValue;
    protected virtual void Dispose(bool disposing)
    {
        if (!disposedValue)
        {
            if (disposing)
            {
                if(Container != null)
                {
                    Container.Dispose();
                }
            }
 
            disposedValue = true;
        }
    }
 
    public void Dispose()
    {
        Dispose(disposing: true);
        GC.SuppressFinalize(this);
    }
}

Finally we have to introduce our custom dependency resolver to ASP.NET MVC. We will do it in Application_Start method of Application class.

var mvcDependencyResolver = new StructureMapDependencyResolver(_container);
DependencyResolver.SetResolver(mvcDependencyResolver);
 
var factory = new StructuremapControllerFactory(_container);
ControllerBuilder.Current.SetControllerFactory(factory); 

Now we have dependencies available also in components that doesn’t support dependency injection.

Conclusion

Although I don’t like service locator much it’s sometimes the only option in classic ASP.NET MVC to resolve dependencies in components that doesn’t support dependency injection. Good thing is that we can still use DI/IoC container we like and some containers come with special libraries or extension methods that attach them automatically to ASP.NET MVC components. Making sure that dependency injection is done properly is mandatory step before moving system over to ASP.NET Core.

The post Using Structuremap in legacy ASP.NET MVC applications appeared first on Gunnar Peipman - Programming Blog.

Visual Studio Codespaces (previously known as Visual Studio Online) is online IDE for building and running applications on cloud. It seems to be excellent choice for those who want development environment hosted in cloud. It’s accessible over internet and it doesn’t need powerful machine.

On tooling side there are three options:

1. Online editor – imitation of Visual Studio Code running in browser
2. Visual Studio – there’s experimental support for codespaces in Visual Studio 2019
3. Visual Studio Code – there’s extension available to work with codespaces

Although I’m focusing here on Visual Studio Code as it is cross-platform and doesn’t have high demands on hardware, I still show you how online editor looks like. Maybe some of my readers find it to be enough for development works they do.

I tried to set up Visual Studio Codespaces for building ASP.NET Core web applications using Visual Studio Code on my Surface Go 2.

Creating Codespace

Creating a codespace through browser was not possible. UI found only one Azure subscription for my account and sadly it was one of those where I cannot use any services without confirmation by subscription owner. I didn’t found any option to get rid of this issue.

I managed to create Codespace using Visual Studio Code and it was possible there to select correct Azure subscription, resource group etc. I got few errors but on second attempt my codespace was created successfully.

Here are the steps in glance:

1. Have a Git repository accessible from internet
2. Create Azure subscription if you don’t have one
3. Install Visual Studio Code if you don’t have it
4. Install Codespaces and Azure Account extensions
5. Open Remote Explorer and click on “Create new Codespace…”
6. You are asked for the following information:
• Azure subscription
• Resource group name
• Region
• Codespaces plan name
• Default instance type
• Source code repository URL
• Auto-suspend Setting
7. Visual Studio Code will create codespace and connect to it
8. Use dotnet new command to create application if your repository is empty

Full guide to set up codespaces support in Visual Studio Code is provided in Microsoft document Visual Studio Codespaces VS Code How-to.

If you started with empty repository then you need also .gitignore file. For this you can install .gitignore Generator extension by Piotr Palarz. Open command palette and launch the following command: Generate .gitignore File. .gitignore file is automatically added to root folder of your project.

If everything went well then you should see your new codespace in browser too – there’s new card on Visual Studio Codespaces page.

Before getting to ASP.NET Core there few things to configure.

Configuring codespaces

Codespaces have two important settings that affect billing:

1. Instance type – how much resources has the environment that runs your application
2. Suspend – how long is the idle time after what codespace is suspended

Here is the selection of instance types today (2020-08-07).

* – Month is defined here as working month or 160 hours. To make calculations easier I rounded monthly numbers up.

Good thing is that resources are provided by Azure environment and we don’t have to care about how hardware is updated over time.

Suspending is important thing. I know some people who develop mainly on cloud hosted virtual machines and their constant fear was forgetting virtual machine running in the end of the working day. Virtual machine that is running has resources assigned to it and it means that billing is ticking all the time. Currently it is possible to set time of day when virtual machine is automatically shut down. Suspend in Visual Studio Codespaces is similar feature but implemented different way. 30 minutes should be good enbough suspend timeout. If there has been no action for 30 minutes then codespace is suspended and it doesn’t consume resources anymore.

Building ASP.NET Core applications

Although I wasn’t yet able to set things up the way I want I was still able to get something done and this something seems right now good enough to build some simpler applications.

It’s possible to use dotnet utility to create projects, add controllers, views etc. Also it’s possible to use Visual Studio Code and create these files from scratch. In both cases it takes some time to get used with it if you are heavy user of Visual Studio. But it’s not a lot of suffer – few hours and things will go smooth.

Here are some issues I ran into:

• I didn’t found a way to run ASP.NET Core application on debugger through Visual Studio Code. I was able to run my web application through terminal using dotnet run command.
• Very often port forwarding is not set up by Visual Studio Code automatically.

Port forwarding means that if application you are running on codespace uses ports then these ports are forwarded to your machine where Visual Studio Code runs. For web application it means you can use forwarded port to see web application running in browser on your local machine.

If port forwarding doesn’t work then you can add forwarded ports manually:

1. Go to Remote Explorer in Visual Studio Code
2. In Codespace details section right click on “Forwarded Ports”
3. Select “Forward Port”
4. Write port number (NB! Field for port number is opened in upper part of windows) and press Enter
5. Repeat this procedure for all ports you want to be forwarded

When port forwarding is set up then it’s time to open browser and connect to web application.

Web application runs on Visual Studio Codespaces and port forwarding makes it available for local machine. Nice!

Conclusion

Visual Studio Codespaces is nice service for building applications on cloud hosted service. It’s accessible though browser but those who want can also connect to codespaces using Visual Studio or Visual Studio code. Running applications on cloud means that codespaces can be used also from machines with limited resources like tablets. Port forwarding makes it possible to run web applications on codespaces and use these with browser running on local machine. Although tooling seems a little bit raw it still works. I think it’s time to start experimenting with Visual Studio Codespaces to see how it fits to everyday’s coding life.

References

• Visual Studio Codespaces
• Visual Studio Codespaces dashboard
• Pricing
• Documentation

The post Building ASP.NET Core applications on Visual Studio Codespaces and Visual Studio Code appeared first on Gunnar Peipman - Programming Blog.

Recently I bought Surface Go 2 tablet with Intel M3 CPU and LTE. Surface Go 2 is hybrid device being either tablet or small laptop. It is lightweight and therefore not the option for resource demanding workloads. Consider it more as an complementary device that is hyper-portable and supports you well if you are moving around. But Surface Go 2 is not yet another tablet – it’s more. Different from competiting offers it is not running limited or mobile edition of operating system but full Windows 10. I found Surface Go 2 to be killer device supporting my daily activities – be it work or private life.

Why Surface Go 2?

I found Surface Go 2 to be excellent device for days when I have to move around and participate in meetings. The device is kind of small but it weights only ~500g. It’s easy to take it out from bag and open it. After logging in by face recognition everything is ready for work or fun.

Don’t expect it to be your next dev box – it just doesn’t have enough resources to carry out these duties although some simpler stuff you can probably do on it. I plan separate blkog post for developers to explain how to write code on Surface Go 2.

There are some things I really like about Surface Go 2. First is the always connected concept.

Always connected. With latest microprocessors together with high performance modem technology initiative from Qualcomm and Microsoft offers always on and always connected PCs and devices. In this concept, a laptop or device running on Microsoft operating system works like a Smartphone. Instead of standby mode in the conventional PCs for power management, these devices will be active (connected standby mode) all the time and connected to a wireless network.

What is always on always connected PC, RF Page

I open machine, log in and it’s ready for action. I don’t even think about internet connection availability anymore. I just open machine and I’m connected.

Another cool thing is switching between tablet and laptop mode. It’s smooth – just attach keyboard and the machine is like small laptop. Detach keyboard and it’s tablet. As a tablet it is more convenient than Surface Book. Surface Book has bigger screen but it’s not so convenient to keep in hand if you want to chill on sofa and chat with friends.

Switch to full Windows 10. By default Surface Go 2 comes with Window 10 S. It means you can only install and run Windows Store applications. You can turn off S-mode and then it will be regular Windows 10 Home edition. You can upgrade it to Windows 10 Pro for few bucks.

Participating in meetings

I’m long time user of OneNote and it plays very well with touch display and Surface Pen. I can write notes, take screenshots, draw on screen etc. I can also tools like Microsoft Whiteboard to illustrate my ideas by making temporary drawings using Surface Pen.

As it’s full Windows I have whole Microsoft Office installed and if needed I can also make presentations using PowerPoint. Nice thing is that OneNote syncs meeting notes automatically to cloud and when I’m later back at my dev box all meeting notes are already there waiting for me.

Of course, Teams and Skype work also very well on Surface Go 2. Front camera is good enough for meetings and picture is very clear.

Office classics

It’s perhaps the most repeated usage of Surface Go 2 and other similar devices but let’s repeat it over. What I’m doing between meetings? I usually read news, reply to e-mails, have a chat and work with documents, spreadsheets and presentations. This is what I call office classics.

Again small weight and small dimensions make things comfortable for me. I order coffee, open Surface Go 2 and continue with whatever I worked on during previous pause.

The device is always connected, e-mails come and go, documents synchronize to cloud and I don’t even think about if there’s free WiFi available in coffee.

You need keyboard for work! Although Windows 10 supports touch experiences it’s still kind of limited and not fully ready for serious work. I bought also keyboard for my Surface Go 2 and it was right decision. If touch featues fail then I connect keyboard to Surface Go 2 and work goes on.

Word, Excel, PowerPoint, OneNote – main office programs I use – work all fine on Surface Go 2. They can be inconvenient to use with on-screen keyboard because on-screen keyboard takes also some screen space and on 10″ screen every pixel matters. As long as you are using physical keyboard all Office apps work very well.

Do I need desktop editions? Not necessarily. If you don’t use any features of Office applications that are only available with desktop editions you can go with online edition of Office. Remember – you are always connected. But if you need to edit Office files in your machine you need desktop edition of Office.

Travelling with Surface Go 2

I also had one trip where I used Surface Go 2 as trip advisor. Two important things I used on my trip:

1. OneNote – I prepared notebook with important information about sites and landmarks I planned to visit. One page for each attraction.
2. Google My Maps – It’s service to create your own maps based on Google Maps. You can add pinpoints on map, define layers etc. It’s really useful if you need to stay at same city for some days.

Before leaving home I opened my OneNote notebook and Google My Maps in Surface Go 2. The machine was ready to support my wanderings. Whenever I need to find out how to go somewhere I can just take Surface Go 2 out from my lightweight backbag, turn it on and check where to go and which way to go. It takes just few seconds on touch screen to find this information out.

OneNote has served me as a travel planner and travel guide for many years. So far, so good. You can find out more about it from my post series OneNote as personal travel guide.

Tip for beginner travelers. Roaming prices can be extremely high and it usually depends on where you are coming from and where are you going. If roaming is expensive (for me when I leave European Union) then I usually buy local SIM card with enough data transfer limits as it comes way cheaper that paying ~10 EUR/MB.

Of course, you can also vote for offline mode. In this case try out at home if OneNote has all pages downloaded and your maps solution – whatever it is – also works with no internet connection.

Kids will love this tablet

Although tablets space is mostly dominated by Apple and Android, there’s no point to underestimate Windows 10 tablets. Although Windows 10 store has not so many games available as competing stores you can still find games they like – some of them are even free.

My kid loves puzzles and Candy Crush games.

But gaming is not everything. There’s actually more.

I bought fast microSD card (128MB) to have some media with me if kid wants to watch cartoons or kids movies. As a metal head who respects the scene I also have some metal videos and songs with me that my kid loves. You can make just copy of media files to have them available offline but if you don’t afraid of making hands dirty with some IT-stuff, then you can also use some media server like Plex.

Kids love cartoons, but it’s passive entertainment. Let’s add some real action too.

It’s time to open my bag again and take out the next miracle – Surface Pen. Again there’s not many programs supporting screen pens but those we have are actually almost perfect. There are Fresh Paint, Microsoft Whiteboard and OneNote that I mainly use with Surface Pen. But there are also other programs by third-parties you may like.

Let’s take Fresh Paint. My kid loves this program. It’s like advanced version of Paint targeting touch screens and also Surface Pen. Of course, you can also draw without Surface Pen using fingers.

It’s easy to use for kids and they usually get started with it fast. Some things to do with kids on Fresh Paint:

1. Draw pictures – this is what Fresh Paint is created for. It’s a little harder than using paper and pencils but it’s cool. You can dry the paint immediately or undo changes. These are things that doesn’t happen with second in real world.
2. Color pictures – there are tons of coloring pictures available at internet for free. You can download the ones your kids like and then import these to Fresh Paint.
3. Draw together – my kid loves to draw with me. I have to come out with interesting ideas and then the action can start. Sometimes time flies fast even for me.
4. Easier to hold and share – if you save your kids best drawings to OneDrive or some similar cloud storage then the drawings are safely backed up and it’s easy to share those drawings with other family members.

There’s more to talk about kids and having fun with computers but let’s stop here as this topic needs separate blog posts.

Connecting other hardware

If you need to use other hardware with Surface Go 2 there are three options:

1. Connect it directly to machine (USB-C, headphones)
2. Use Bluetooth
3. Use USB-C hub

As much as possible I try to go with Bluetooth as it means no additional wires. Imagine wires and wires and wires when trying to work in first class wagon of train. It’s easy to find earphones with bluetooth support these days, Microsoft also sells Surface Mouse with bluetooth support and there are many other peripherals that support it.

But not everything is easy to do over Bluetooth. If I want to watch movies or metal lives at hotel then I want to connect my Surface Go 2 to TV. Often hotels let turn off all wireless features that TV set has. So, here we are… we need some cabling. Also there are peripherals that come only with USB connection.

For USB devices and HDMI screens I bought USB-C hub similar to Satechi USB-C Slim Multi-Port with Ethernet Adapter.

You can find also more powerful ones with more different ports. Some hubs come even with LAN cable port. Using USB-C hub I can connect smart card reader to Surface Go 2 and use Estonian ID-card to authenticate myself at official sites or give digital signatures to documents.

Developing software on Surface Go 2

As I mentioned before – Surface Go 2 is not your next portable powerhouse machine. It’s still possible to do something and you actually have some options like Remote Desktop to some more powerful machine, using VS Code to code on remote machine or building something smaller on Surface Go 2 with locally installed tooling. Forget about Visual Studio, of course.

For short, the options are:

1. Simple apps can be developed on Surface Go 2 without any remoting
2. For more complex apps you can remote over SSH to some more powerful dev box running on Azure or in your in-prem environment
3. If your company allows then you can also have VPN connection to work network and use Remote Desktop to connect to your dev box

I don’t stop on development issues longer as I plan another post to introduce developing options on Surface Go 2.

Should I buy Surface Go 2

Before giving my advice I want to emphasize that my review covers only Surface Go 2 with Intel M3 CPU and LTE. I don’t have any idea how other Surface Go 2 devices would work for me as I have never used them. So, be warned!

As a software architect my answer is boring and not useful – it depends. If you can live without it and you don’t miss any of the positive features it has or you don’t want to deal with negative ones then go with what you currently have. You loose absolutely nothing.

If you found some of scenarios here interesting and mandatory to have then, of course, consider buying one tablet. I warn you about one thing – it takes a week to get used with so limited hardware resources and small battery but after a week you should feel like home with this device. For now it’s one month for me to move around with Surface Go 2 and I have only few times missed my main dev machine.

Where to buy?

If you have some good local hardware shop near you then you can check there. But you can also order it from some of Amazons across the globe. Here are few links that my biggest reader base can find useful:

• Surface Go 2 @ Amazon.de
• Surface Go 2 @ Amazon.com (US)

The post Surface Go 2 review appeared first on Gunnar Peipman - Programming Blog.

It wasn’t any of the classic problems. Classic ones have one of these solutions:

1. Open solution in Windows Explorer, close Visual Studio and delete .vs folder.
2. Close IIS Express. Go to user folder in Windows Explorer and delete IIS Express subfolder.
3. Open command prompt, type netstat -aof | findstr :<application port here>

If you have virtualization components enabled like it is in my dev box then make sure your application doesn’t try to use some reserved port.

Open command prompt and run the following command:

netsh interface ipv4 show excludedportrange protocol=tcp

On my dev box I got the following long list of port ranges:

Web application I tried to run was using port 56442 and it fell into one of port exclusion ranges.

Solution

Open properties page of your web application and make it use some available port that is not in port exclusion range.

After changing port click also on Create Virtual Directory button.

Now you are good to go. No need to restart IIS Express or Visual Studio. Just change port, let IIS Express create new virtual directory and run your application.

The post IIS Express error: Failed to register URL for site. The process cannot access the file because it is being used by another process. appeared first on Gunnar Peipman - Programming Blog.

The story of Notepad.Extensions.Logging

When checking Twitter I found a funny tweet by @yaakov_h where he announced NuGet package for .NET Core logger that targets Notepad. He got inspiration for this from tweet by @steveklabnik.

And guess what? Besides GitHub repository for Notepad.Extensions.Logging there’s also NuGet package available!

It’s a real deal – version 1.0.0, you see? :)

Notepad logger in action

Let’s get hands dirty with this little piece of art. Just create new ASP.NET Core web application and add Notepad.Extensions.Logging NuGet package. After this modify ConfigureServices() method in Startup like in following code fragment.

public void ConfigureServices(IServiceCollection services)
{
    services.AddControllersWithViews();
 
    services.AddLogging(lb => lb.AddNotepad());
}

To try things out I made my HomeController log some messages.

public class HomeController : Controller
{
    private readonly ILogger<HomeController> _logger;
 
    public HomeController(ILogger<HomeController> logger)
    {
        _logger = logger;
    }
 
    public IActionResult Index()
    {
        _logger.LogInformation("Home.Index opened");
        return View();
    }
 
    public IActionResult Privacy()
    {
        _logger.LogInformation("Home.Privacy opened");
        return View();
    }
 
    public override void OnActionExecuted(ActionExecutedContext context)
    {
        base.OnActionExecuted(context);
        _logger.LogInformation("Action executed");
    }
}

I didn’t made it work with NuGet package for some reason. I had to clone Notepad.Extensions.Logging repository to my machine and add it as project reference to web application. Then after few runs messages started appear to Notepad. Here’s the short video I made of this experiment.

So, it works after all!

Should I really use it?

Although this solution is funny it may still come handy when debugging something and there are things to dig out from logs frequently. It’s possible to disable all other log targets for debugging and skip logging related disk I/O as communication with Notepad goes directly through memory.

After using Notepad.Logging.Extensions few hours I actually started to like it. It’s weird but feels somehow convenient. Is Notepad the best choice for text editor? Who knows. But it’s not very hard to extend Notepad.Logging.Extensions to use some more advanced text editor.

Anyway, happy weekend and cheers!

The post Logging to Notepad window from ASP.NET Core appeared first on Gunnar Peipman - Programming Blog.

What is Static Web App service?

Static Web App is hosting service for static web applications built on JavaScript. It’s practically Azure App Service for static applications that is easy to set up and use.

Static Web App service is connected to GitHub repository where source code of web application is hosted. Using GitHub actions it builds and publishes static application either to production or staging environment. Production environment is always based on some concrete branch. Staging environment has changing URL and it is based on pull requests. Currently there’s only one active staging environment allowed at time.

Static Web App service works almost like regular App Service integration with Azure DevOps. Maybe bit too general the following image illustrates the new service well.

As you may notice then Azure Functions are also supported. There’s one limitation – those functions must be written in JavaScript as this is the only type of functions supported right now.

Where Static Web App fits on Azure?

Nice thing is that Static Web App service fills one empty slot in row of app hosting and deploying models on Azure.

Those who remember old days on Azure probably want to remind me of compute service. I didn’t put it to table as other services are good replacement for it.

Setting up Static Web App service

Before creating service we need GitHub repository to what our service is connected. If you want to try things out then please create one GitHub repository before proceeding further. You will need it at next step already. After creating repository make sure that master branch is published and available.

We can create Static Web App service in Azure portal. At the time of writing this blog post not all Azure regions are supported as the service is in preview.

Notice how we have to specify GitHub repository and branch. This is from where live site deployments are made.

Next step is to configure build parameters for static web application. API location can be left empty if web application doesn’t have Azure Functions.

Tricky thing is app artifact location. This is the folder where application puts distribution files. To talk more in ASP.NET developers language then this is the folder where application is published after build. Make sure you don’t have this folder in repository.

Now we can click Review + create and after few minutes we have Static Web App service set up for us. If everything went well then we will see out Static Web App service running.

Workflow file points to GitHub actions definition that was created automatically with service. If you need to change build related setting later then this can be done by modifying workflow file.

Publishing static application

I tried Static Web App service with Angular application. I cloned my test repository to my machine, installed Angular CLI and created new Angular 9 application.

mkdir StaticWebAppTest
git clone https://github.com/gpeipman/MyStaticSite.git
npm install -g @angular/cli
ng new StaticWebAppTest

If your connection or disk is slow then go and grab some coffee – it takes time until all those node.js modules get installed.

When application is created we can build it and push to repository.

ng build --prod
git add .
git commit –m "default angular app"
git push

If everything went well then it’s time to head over to GitHub and see under actions if our GitHub workflow is running. This workflow was automatically created by Static Web App service and it deploys application to production environment. After successful deployment we will see it under Environment section of service.

Click on Browse link to see static application running on Azure.

What we just did? We pushed Angular application to GitHub repository and it got automatically published to our Static Web App service. It was just a commit to repository and after this things just happened. Nice!

Publishing to staging environment

It’s usual that new features are merged to master branch through pull requests. For Static Web App service pull requests are staging environment deployments. There can be only one stating environment right now but it will probably change in future.

Let’s modify default page title in src/app/app.component.html file. I added word staging to title and created pull request. You can create new branch from master, make a change and push it to GitHub from command-line. But you can skip it too. It’s possible to modify the file directly in GitHub and create new branch with pull request there.

When pull request is created out Static Web App workflow runs again. Take a good look on the following screenshot. When GitHub actions are running it’s shown on pull request page. After successful build there’s link to staging environment published to pull request page as bot comment.

We can see staging information also in Azure portal if staging environment exists.

When we open staging environment in browser we can see a change we just made.

After merging pull request the staging environment is automatically removed by Azure.

Configuring GitHub action

To change build definiton we can open yml file shown in Static Web App overview. This file is available also in our source code repository. Example of build definition is here.

name: Azure Static Web Apps CI/CD

on:
  push:
    branches:
    - master
  pull_request:
    types: [opened, synchronize, reopened, closed]     branches:
    - master

jobs:
  build_and_deploy_job:
    if: github.event_name == 'push' || (github.event_name == 'pull_request' && github.event.action != 'closed')     runs-on: ubuntu-latest
    name: Build and Deploy Job
    steps:
    - uses: actions/checkout@v1
    - name: Build And Deploy
      id: builddeploy
      uses: Azure/static-web-apps-deploy@v0.0.1-preview
      with:
        azure_static_web_apps_api_token: ${{ secrets.AZURE_STATIC_WEB_APPS_API_TOKEN_AMBITIOUS_SMOKE_092E9AF03 }}         repo_token: ${{ secrets.GITHUB_TOKEN }} # Used for Github integrations (i.e. PR comments)
        action: 'upload'
        ###### Repository/Build Configurations - These values can be configured to match you app requirements. ######
        app_location: '/' # App source code path
        api_location: '' # Api source code path - optional
        app_artifact_location: 'dist/MyStaticSite' # Built app content directory - optional
        ###### End of Repository/Build Configurations ######
   close_pull_request_job:
    if: github.event_name == 'pull_request' && github.event.action == 'closed'     runs-on: ubuntu-latest
    name: Close Pull Request Job
    steps:
    - name: Close Pull Request
      id: closepullrequest
      uses: Azure/static-web-apps-deploy@v0.0.1-preview
      with:
        azure_static_web_apps_api_token: ${{ secrets.AZURE_STATIC_WEB_APPS_API_TOKEN_AMBITIOUS_SMOKE_092E9AF03 }}         action: 'close'

Internally Microsoft is using their build system Oryx that is created to automatically compile applications to runnable artifacts in Microsoft services.

Wrapping up

Azure Static Web App fits well between Azure App Service and Azure Static Website offerings. There’s integration with GitHub using GitHub actions. Production environment is based on monitoring given source code repository branch. Staging environment come and go based on creation and merge of pull requests. Static Web App is platform agnostic. It doesn’t care about operating system and code editor or IDE that developers use. If GitHub actions are able to build static web application then it’s enough for Static Web App service to work. Although there’s only one staging environment supported right now, it will probably change in future when I think about applications in active development.

References

• Azure Static Web Apps (official home page)
• Azure Static Web Apps documentation
• Microsoft Oryx (official home page)
• Using Angular in Visual Studio Code (VS Code documentation)
• Introducing App Service Static Web Apps (Microsoft Tech Community)

The post Introducing Azure Static Web App service appeared first on Gunnar Peipman - Programming Blog.

Today Xamarin applications need separate project for each target platform. .NET 5 – coming this autumn – lays excellent base for multi-targeting as .NET Core, Mono and Xamarin will all base on same base class library (BCL) and SDK toolchain. Besides desktop and mobile, .NET MAUI will support also new foldable devices like Microsoft Neo. First previews of .NET MAUI will be available later this year when Microsoft focuses to .NET 6 that is scheduled to release with .NET MAUI at November 2021.

Developing .NET MAUI applications

Image of .NET MAUI solution here shows how there is one project targeting multiple platforms. There will be new Platforms folder in .NET MAUI projects and under this we can see separate subfolder for each target platform. Screenshot here shows BluetoothAdapter class that is platform dependent. There will also be support for platform specific resources.

Supported platforms will be defined in project file using .NET 5 monikers like net5.0-ios, net5.0 and net5.0-android.

As .NET MAUI is advancement of Xamarin Forms then application pages use good old XAML and C#. Besides code-behind, modern patterns like Model-View-ViewModel (MVVM) and Model-View-Update (MVU) are supported too. MVU is new UI pattern enabling developers to write fluent C# UI-s on .NET MAUI.

Transition to .NET MAUI should be almost painless as .NET MAUI comes with controls and API-s already present in Xamarin Forms.

Second front: Blazor WebAssembly

Yesterday Microsoft also launched Blazor WebAssembly to general availability. At January during .NET Conf: Focus on Blazor online conference Microsoft demonstrated some very interesting experiments with Blazor:

• Blazor + Electron
• Blazor + WebWindow
• Mobile Blazor Bindings 

Electron and WebWindow are able to take Blazor applications across platforms and devices. Mobile bindings enable use of native API-s to Blazor WebAssembly applications. Although Blazor tooling targets web applications, these experiments show clearly that Blazor is able to jump out from its box.

What makes Blazor front interesting is the fact that it may give us different tooling for multi-platform applications. .NET MAUI will be for native multi-platform applications and some Blazor multi-plaform offering will be for applications built using web technologies.

As there’s time a little bit over year to launch of .NET MAUI it will be interesting to see where Blazor evolves and if there will be some connection point for these two multi-platform worlds.

.NET MAUI roadmap

The current roadmap spans from this to next autumn.

• Late 2020 – Preview 1
• Early 2021 – Preview 2 and Preview 3
• Mid 2021 – Preview 4 and Preview 5
• September 2021 – Release Candidate
• November 2021 – General Availability

References

• Introducing .NET Multi-platform App UI (.NET Blog)
• .NET MAUI @ GitHub
• .NET MAUI official roadmap
• Model-View-Update (MVU) – How Does It Work? (Thomas Bandt)
• Build session: The Journey to One .NET (Scott Hanselman, Scott Hunter)

The post Xamarin Forms turns to .NET MAUI – single project and code-base dev experience appeared first on Gunnar Peipman - Programming Blog.

What is Azure Front Door?

To understand what is Azure Front Door let’s jump to documentation. Azure Front Door enables you to define, manage, and monitor the global routing for your web traffic by optimizing for best performance and instant global failover for high availability. With Front Door, you can transform your global (multi-region) consumer and enterprise applications into robust, high-performance personalized modern applications, APIs, and content that reaches a global audience with Azure.

Azure Front Door is Layer 7 network service having the following nice features:

• SSL offload and application acceleration at the edge close to end users
• Global HTTP load balancing with instant failover
• Actionable insights about your users and back ends
• Web Application Firewall (WAF) and DDoS Protection
• Central control plane for traffic orchestration

Important thing to understand is the fact that Azure Front Door is not bound to any specific region. By example, Azure Front Door service can be used for high-availability across multiple Azure regions like shown on the following diagram.

This image is taken from Azure Front Door documentation page
Run a web application in multiple Azure regions for high availability.

One interesting thing is that Azure Front Door works similar to Azure CDN. It uses same Point-of-Presence (PoP) servers as Azure CDN meaning that your content will be close to visitors all over the world.

Hosting WordPress behind Azure Front Door

When it comes to hosting WordPress on Azure there are some options to consider.

• Azure App Service – together with Azure SQL for MySQL it can come pretty expensive. Still you have to understand that these services are stable and highly scalable. Azure engineers are the ones who keep those services alive and you don’t need any task force on your side.
• Virtual Machines – if you want to control almost everything and keep costs down then you can go with small Linux virtual machines on Azure. It means more work for you as virtual machines are your own responsibility. Also you are in judge on managing all other services around them.

Azure App Service with database is very straight-forward to understand if we are talking about getting things running. Virtual machines are different story. Here’s one possible idea how VM-s can be set up.

NginX serves as caching reverse proxy before WordPress machines. It’s the only VM that has unrestricted public access. All otger VM-s are no accessible from public internet. Blob storage and Azure CDN can be used for all static assets in wp-content folder to keep load away from small VM-s.

Our Azure Front Door and WAF cloud architecture for WordPress VM-s solution is shown on the following diagram.

Notice that there’s no more NginX and load balancing is done by Azure Front Door.

Setting up Azure Front Door

Load balancing with NginX is great but there’s one issue – it’s regional and not global. Setting up our own NginX to every region of Azure is out of question. It will be expensive and I’m sure that it needs close monitoring even after all pieces of puzzle start work together as one system. This is where Azure Front Door comes to help.

Azure Front Door service is set up with three steps:

1. Frontend/domains – these are domains that later have CNAME record to Azure Front End end-point.
2. Backend pools – pools on backend servers where requests are sent. It can be just one server but it can be also cluster of servers. For simple WordPress blog one backend pool is enough. I have one backend pool and two WordPress servers in pool.
3. Routing rules – here you can decide which URL ends in which backend pool. When you create service then default rule is created automatically. You can add more rules later if needed.

I don’t go through all steps as they are documented very well by Microsoft. There are some things to consider when setting up the service.

• Custom domains are supported but you can add one after service is created for you. For custom domain you need also certificate. If you don’t have one you can let Azure Front Door set up one for you. If you have certificate in Azure Key Vault then you must add Azure Front Door service as a user of your key vault.
• Backend pool servers running WordPress doesn’t necessarily need caching plugins to be activated. If you need to use one then make sure that they share page cache. Otherwise your pages get easily out of sync. Making WordPress output caches share disk space is not easy thing to do and I finally gave up.
• Load balancing – if you don’t have previous experiences with load balancers then leave these settings as they are. These are good defaults for most of scenarios. Read carefully about healt probes later in this post to avoid some dark surprises.
• Caching – I enabled caching and made it ignore query strings. I can access backend pool machines directly and I don’t want requests from public space to be able to ask too much from server. Ignoring query strings helps avoid different attacks but be aware – purging of content is up to you.
• Allow Azure Front Door IP-s in your firewall if you are using virtual network. Just add IP range 147.243.0.0/16 to list of allowed IP-s to your firewall. Without this Azure Front Door cannot check for servers health and server content from backend pool.

When everything is set up and configured it’s time see the results.

First results

As we can now push our content close to our visitors there are some visible benefits we will see pretty soon after Azure Front Door is in action. Take a look at the following global ping charts comparing situation before and after enabling Azure Front Door.

It’s rougly taken a 3.5 times improvement in ping times. So, depending where your WordPress site is geographically hosted, you just made a favor to your readers in all other regions in the world.

For mobile devices my blog PageSpeed Insights score went up from 66% to 74% after moving to Azure Front Door. It is for pages served from PoP servers cache. I don’t know exactly how much this number affects SEO but Google said years ago that page opening speed will matter in future (Speed is now a landing page factor for Google Search and Ads).

Configuring Azure Web Application Firewall

Web Application Firewall (WAF) is Azure offering for web applications. WAF protects web applications from all kind of attacks, including DDoS attacks. WAF works either in protection or detection mode. Detection mode means that WAF only logs suspicious things but doesn’t involve itself. In prevention mode WAF takes action when dangerous request is coming in.

WAF is able to handle attack like cross-site scripting, Java attacks, local file inclusion, PHP injection attacks, remote command execution, remote file inclusion, session fixation, SQL injection attacks and protocol attacks.

Azure Web Application Firewall (WAF) on Azure Front Door. This image is taken from
Azure Front Door documentation page Azure Web Application Firewall on Azure Front Door.

I was first a little suspicious about WAF as it comes with its own price that is not so easy to estimate. This far my NginX was able to survive all anomalies with ease and I didn’t saw much point to enable WAF. I have set of rules defined for WordPress and this has been good enough security net for me this far. But I decided to give WAF a try and I had some good surprises.

The following chart illustrates requests to Azure Front Door end-point. Blue line shows requests sent to backend pool – WordPress cluster. Red line shows all requests that came in to Azure Front Door end-point and dark blue line is the number of requests handled by WAF.

From numbers you see that caching has great impact on how much requests land in backend pool. From web server logs I see that WAF stops almost all weird requests to WordPress. I’m not sure how it’s done but it seems to be really effective on protecting web applications.

Spam stopped immediately! It was normal that Akismet stopped ~100 spam comments per day before lockdown. During lockdown spammers activated and the number of spam doubled up. After enabling WAF there was only one spam message per 48h.

I was actually blown away as I didn’t expect WAF to be so effective.

Be careful with health probes

When I got Azure Front Door up and running I was blown away of load coming to machines in my WordPress cluster. I mean behing NginX the cluster was mostly calm and there was almost no load on WordPress VM-s. I checked web server logs and found out that there are many requests coming in from Azure Front Door PoP servers.

Well… by default health probe requests go to address “/” and interval is 30 seconds. No big deal, you want to say. But it is because all PoP servers use these settings to poll your backend pool. There’s a blog post Beware the non-obvious costs of Azure Front Door by Brett McKenzie where he calculated that healt proble pings came in from ~80 servers across the globe. By documentation there are 129 PoP locations, so I think Brett didn’t saw the full fury of the service.

Solution is simple – make health checks as small as possible. It works well if you set up some empty or one byte file and increase polling period. If your WordPress site is not mission critial then polling interval can be more than 30 seconds. Blog post Real World Cloud Migrations: Azure Front Door for global HTTP and path based load-balancing by Scott Hanselman goes with 255 seconds – it’s a maximum interval.

This issue came to me as a real surprise. I wasn’t able to believe that PoP servers come to poll your site one by one. I first thought that it’s a Front Door service that makes health probe polls and broadcasts results to PoP servers but no. Okay, we can live with this if we don’t let poll requests to anything big or resource consuming. Still for me it’s the annoying fact to live with.

Expenses

All kind of load balancing on Azure is pricy if we are talking about sites that doesn’t generate a big buck. For simple blogs like I have Azure Front Door would be total overkill on pricing. But for mission critical applications I would use it together with WAF as they work actually well.

So, how much Azure Front Door adds to monthly bill?

In my case it would be around 45 EUR/month.

For commercial site this number is actually small, almost nothing. For private blog it’s too much – you know, it’s 40 discounted beers per month, ooops, lets’s get back to NginX… :) After moving back to NginX it took few hours and Akismet started catching spam again. But during WAF times it was soooo quiet.

Wrapping up

Azure Front Door is great service for commercial web sites with more complex topology, fault tolerance or geo-distribution. It may feel pricy for private sites but for commercial sites making normal money it’s nothing. Just imagine other ways to build up something like this on Azure. It would be hell load of complex work and I’m sure it will be more expensive that going with Azure Front Door and WAF. I was actually surprised how easy it was to get Azure Front Door + WAF up and running. It took just few hours but it was my first time to set it up. WAF works well and stops most of malicious requests. I think for global and mission critical sites Azure Front Door with WAF is perfect choice.

References

• Azure Front Door homepage
• What is Azure Front Door? (Official documentation)
• Azure Web Application Firewall on Azure Front Door (Official documentation)
• Run a web application in multiple Azure regions for high availability (Official documentation)
• Hosting WordPress on Azure (Gunnar Peipman)

The post Hosting WordPress behind Azure Front Door appeared first on Gunnar Peipman - Programming Blog.

What is Micro-ORM?

Micro-ORMs are lightweight object-relational mappers (ORM). They have thin communication interface that makes it easy to query objects from database and – in some cases – to get them easily back to database too. Micro-ORMs doesn’t generate SQL based on object queries like full-blown ORM-s do. Also they usually don’t have change tracking and any other advanced features. As SQL is not generated for us we have to write it manually.

Manually written SQL and lack of powerful features of full-blown ORM-s means one thing – querying of database is easy task to do but updating of object graphs can be challenging.

Introducing Dapper

Dapper is perhaps most popular micro-ORM. It is worked out by Stack Exchange and one of the most popular sites it is running is Stack Overflow. Yeah, the same Stack Overflow we all know and from where tons code come to our systems thaks to Copy-Paste Driven Development.

I like Dapper because it is simple and damn easy to use and understand. It is implemented as set of extension methods to ADO.NET database connection and it sets itself on our way minimally. There are no interfaces to implement, no instances to create and take care of – only simple extension methods to use on objects we have to create anyway.

Here’s the example of list method for invoices.

public async Task<IEnumerable<Invoice>> ListInvoices(int page, int pageSize)
{
    using (var connection = _provider.GetDbConnection())
    {
        var parameters = new { Skip = (page - 1)*pageSize, Take = pageSize };
 
        var query = "select * from Invoices order by CURRENT_TIMESTAMP ";
        query += "OFFSET @Skip ROWS ";
        query += "FETCH NEXT @Take ROWS ONLY";
 
        return await connection.QueryAsync<Invoice>(query, parameters);
    }
}

QueryAsync() method is extension method by Dapper. It takes query and parameters object to build command, execute it and return objects of given type. There are also other methods for returning single object or results from multiple queries that were sent to server with one batch.

Querying database using Dapper

I wrote simple CRUD application on ASP.NET Core to demonstrate how to use Dapper. To keep code clean and SQL in one place I went with query classes similar to ones I demonstrated in my blog post Implementing repository querying interface in EF Core DbContextImplementing repository querying interface in EF Core DbContext.

Let’s take a look at class for querying invoices. Notice GetInvoiceById() method that loads invoice rows from database only if user wants it to do so.

public class SqlServerInvoiceQueries : IInvoiceQueries
{
    private readonly SqlServerConnectionProvider _provider;
 
    public SqlServerInvoiceQueries(SqlServerConnectionProvider provider)
    {
        _provider = provider;
    }
 
    public async Task<Invoice> GetInvoiceById(int id, bool includeRows)
    {
        using(var connection = _provider.GetDbConnection())
        {
            var invoice = await connection.QueryFirstAsync<Invoice>("select * from invoices where id=" + id);
            if(invoice == null)
            {
                return null;
            }
 
            if(includeRows)
            {
                var query = "select * from InvoiceLines where InvoiceId=" + id;
                invoice.InvoiceLines = (await connection.QueryAsync<InvoiceLine>(query)).ToList();
            }
 
            return invoice;
        }
    }
 
    public async Task<IEnumerable<Invoice>> ListInvoices(int page, int pageSize)
    {
        using (var connection = _provider.GetDbConnection())
        {
            var parameters = new { Skip = (page - 1)*pageSize, Take = pageSize };
 
            var query = "select * from Invoices order by CURRENT_TIMESTAMP ";
            query += "OFFSET @Skip ROWS ";
            query += "FETCH NEXT @Take ROWS ONLY";
 
            return await connection.QueryAsync<Invoice>(query, parameters);
        }
    }
}

Queries classed need database connection provider – custom class to provide correct connection to query classes. For ASP.NET Core applications I register connection provider and query classes with framework-level dependency injection. Here is my MSSQL connection provider.

public class SqlServerConnectionProvider
{
    private readonly string _connectionString;
 
    public SqlServerConnectionProvider(string connectionString)
    {
        _connectionString = connectionString;
    }
 
    public IDbConnection GetDbConnection()
    {
        return new SqlConnection(_connectionString);
    }
}

Queries are plain SQL with parameters. Dapper takes away some pain on building parameters and adding these to commands. If our queries grow long, ugly and complex then we can move them to separate files or resource strings to keep query classes clean.

We can inject these query classes to ASP.NET Core controllers and call their methods to get data.

public class HomeController : Controller
{
    private readonly IInvoiceQueries _invoiceQueries;
 
    public HomeController(IInvoiceQueries invoiceQueries)
    {
        _invoiceQueries = invoiceQueries;
    }
 
    public async Task<IActionResult> Index(int page = 1)
    {
        page = Math.Max(1, page);
 
        var invoices = await _invoiceQueries.ListInvoices(page, 10);
 
        return View(invoices);
    }
 
    // More actions follow
}

For very primitive applications I don’t usually even bother to go with query classes. I need to get some simple objects from simple database tables and save them back. Be careful, of course, and don’t use this approach if there’s chance that application will grow. Example of this minimalistic approach can be found from my GitHub repository gpeipman/DapperDemo.

Modifying data using Dapper

When we want to modify data then things get complex pretty fast. Things are easy until we work with primitive entities. But dealing with object graphs is different story.

Let’s take sample entities from one of my demos.

public class Invoice
{
    public int Id { get; set; }
    public DateTime Date { get; set; }
    public DateTime DueDate { get; set; }
    public string Customer { get; set; }
    public string InvoiceNo { get; set; }
 
    public IList<InvoiceLine> InvoiceLines { get; set; }
 
    public Invoice()
    {
        InvoiceLines = new List<InvoiceLine>();
    }
}
 
public class InvoiceLine
{
    public int Id { get; set; }
    public string LineItem { get; set; }
    public decimal Amount { get; set; }
    public string Unit { get; set; }
    public decimal UnitPrice { get; set; }
    public int VatPercent { get; set; }
    public decimal Total { get { return Amount * UnitPrice * (1 + 20 / 100); } set { } }
}

Suppose we have a form where we can create or modify invoice and lines it has. It’s all done dynamically in browser and when user clicks save button then invoice with rows is sent back to server.

Here are ASP.NET Core controller actions to update invoice.

public class HomeController : Controller
{
    private readonly IInvoiceQueries _invoiceQueries;
    private readonly IInvoiceRepository _invoiceRepository;
 
    public HomeController(IInvoiceQueries invoiceQueries,
                          IInvoiceRepository invoiceRepository)
    {
        _invoiceQueries = invoiceQueries;
        _invoiceRepository = invoiceRepository;
    }
 
    public void Index() { }
 
    // Some actions here
 
    public async Task<IActionResult> Edit(int id)
    {
        var invoice = await _invoiceQueries.GetInvoiceById(id, true);
        if (invoice == null)
        {
            return NotFound();
        }
 
        return View(invoice);
    }
 
    [HttpPost]
    public async Task<IActionResult> Edit(Invoice invoice)
    {
        if(!ModelState.IsValid)
        {
            return View(nameof(this.Edit), invoice);
        }
 
        await _invoiceRepository.Save(invoice);
 
        return RedirectToAction(nameof(this.Index));
    }
 
    // More actions follow
}

As insert and delete parts are actually simple ones I leave them out. But update is not so easy when written manually without using full-blown ORM. It consists of four steps that are run in database transaction:

1. Update invoice in database
2. Update existing invoice lines in database
3. Insert new invoice lines
4. Delete removed invoice lines

Just to show how it looks in code here is the example of Update() method of my sample invoice repository.

private async Task Update(Invoice invoice)
{
    using (var connection = await GetOpenConnection())
    using (var transaction = connection.BeginTransaction())
    {
        try
        {
            var query = "UPDATE Invoices SET Date=@Date, DueDate=@DueDate ";
            query += "WHERE Id=@Id";
            await connection.ExecuteAsync(query, invoice, transaction);
            var lineIds = new List<int>();
 
            lineIds.AddRange(invoice.InvoiceLines.Where(l => l.Id != 0).Select(l => l.Id));
 
            foreach (var line in invoice.InvoiceLines)
            {
                if (line.Id == 0)
                {
                    query = "INSERT INTO InvoiceLines (LineItem, Amount, Unit, UnitPrice, VatPercent, InvoiceId)";
                    query += "VALUES (@LineItem, @Amount, @Unit, @UnitPrice, @VatPercent," + invoice.Id + "); ";
                    query += "SELECT CAST(SCOPE_IDENTITY() AS INT)";
 
                    var id = await connection.QueryFirstAsync<int>(query, line, transaction);
                    lineIds.Add(id);
                }
                else
                {
                    query = "UPDATE InvoiceLines SET LineItem=@LineItem,Amount=@Amount,Unit=@Unit,";
                    query += "UnitPrice=@UnitPrice,VatPercent=@VatPercent ";
                    query += "WHERE Id=@Id";
 
                    await connection.ExecuteAsync(query, line, transaction);
                }                        
            }
 
            if (lineIds.Count > 0)
            {
                query = "DELETE FROM InvoiceLines WHERE InvoiceId=" + invoice.Id + " AND ";
                query += "Id NOT IN(" + string.Join(',', lineIds) + ")";
 
                await connection.ExecuteAsync(query, transaction: transaction);
            }
 
            transaction.Commit();
        }
        catch
        {
            transaction.Rollback();
 
            throw;
        }
    }
}

I’m sure we don’t want to write code like this and this is why I don’t consider Dapper or any other micro-ORM as a best choice for modifying data.

Taking best from the both worlds – CQRS

Command Query Responsibility Segregation (CQRS) is pattern first introduced by Greg Young. It’s about separating operations that query data from those that modify system state. On queries side we have data querying – it’s read-only and we are using Dapper. On commands side we modify system state and there we can use EF Core, NHibernate and other full-blown ORM-s.

CQRS illustrated. Image is taken from CQRS page by Martin Fowler.

CQRS is not must-be. It’s like any other pattern – think before you use it. It doesn’t solve all your problems and it’s not a silver bullet. In his CQRS article Martin Fowler warns: “For some situations, this separation can be valuable, but beware that for most systems CQRS adds risky complexity.”

Wrapping up

Using Dapper we can write SQL to execute directly against the database server we are using. It is easy to use and there are just some methods we need to call to get objects out from database. As we saw from examples then querying for data is easy and straightforward but modifying data can be very challenging when it comes to object graphs. Because of this we may want to go with CQRS pattern to have separate interfaces and models for querying and modifying data. CQRS is not a silver bullet and we have to think if we want to apply it in our system or not. For applications with simple object graphs we can go with Dapper based repositories without making things too complex.

The post Using Dapper in ASP.NET Core applications appeared first on Gunnar Peipman - Programming Blog.

Why future results?

Future results decrease traffic between application and database server. Most of database servers support processing of multiple queries sent as one batch resulting in returning multiple result sets with one batch.

Suppose we have a dashboard showing some charts, tables and gauges. We can send queries to database one by one. After sending query to server we have to wait when results are sent back and then we can execute next query. But our dashboard turns out to be an excellent use case for future results because all queries we need to execute are not dependent on other queries results.

As future results are used to query data from database you should consider using stateless session of NHibernate. Stateless session performs better as it doesn’t have change tracking enabled.

How to use future results?

Using future results is actually easy. We build queries like usually but the last call in row is to ToFuture() or ToFutureValue() method. ToFuture() and ToFutureValue() methods put queries on hold until data is asked.

Let’s start with simple ASP.NET Core model for dashboard.

public class DashboardModel
{
    public IList<Task> MyPendingTasks { get; set; }
    public IList<Task> MyOverDueTasks { get; set; }
    public IList<WorkLog> MyWorkToday { get; set; }
}

We need three database queries to get values to all properties of this model. Here are sample queries that are all delayed until data is actually asked in code.

var myPendingTasks = _nhSession.Query<ProjectTask>
                               .Fetch(t => t.Project)
                               .Where(t => t.AssignedTo.InternalName == userName &&
                                           t.Deadline > DateTime.Now &&
                                           t.Status != ProjectTaskStatusEnum.Closed &&
                                           t.Status != ProjectTaskStatusEnum.WaitingForApproval)
                               .Take(10)
                               .ToFuture();
                                            
var myOverDueTasks = _nhSession.Query<ProjectTask>
                               .Fetch(t => t.Project)
                               .Where(t => t.AssignedTo.InternalName == userName &&
                                           t.Deadline < DateTime.Now &&
                                           t.Status != ProjectTaskStatusEnum.Closed &&
                                           t.Status != ProjectTaskStatusEnum.WaitingForApproval)
                               .Take(10)
                               .ToFuture();
 
var myWork = _nhSession.Query<WorkLog>
                       .Fetch(w => w.Task)
                       .ThenFetch(w => w.Project)
                       .Where(w => w.User.InternalName == userName &&
                                   w.Date == DateTime.Now.Date)
                       .ToFuture();
 
var model = new DashboardModel();
model.MyPendingTasks = (await myPendingTasks.GetEnumerableAsync()).ToList();
model.MyOverDueTasks = (await myOverDueTasks.GetEnumerableAsync()).ToList();
model.MyWorkToday = (await myWork.GetEnumerableAsync()).ToList();

Asking of data happens when model.MyPendingTasks is assigned. Here’s the query that was sent to database server when data was asked first time from some of those delayed queries. NB! I left out fields list from FROM clauses to keep queries more readable.

select 
    ... 
from 
    ProjectTasks projecttas0_ 
    inner join Users user1_ on 
        projecttas0_.AssignedToId=user1_.Id 
    left outer join Projects project2_ on 
        projecttas0_.ProjectId=project2_.Id 
where 
    user1_.InternalName=@p0 and 
    projecttas0_.Deadline>@p1 and 
    (projecttas0_.Status<>@p2 or projecttas0_.Status is null) and 
    (projecttas0_.Status<>@p3 or projecttas0_.Status is null) 
ORDER BY 
    CURRENT_TIMESTAMP 
OFFSET 0 ROWS FETCH FIRST @p4 ROWS ONLY;
 
select
    ...
from 
    ProjectTasks projecttas0_ 
    inner join Users user1_ on 
        projecttas0_.AssignedToId=user1_.Id 
    left outer join Projects project2_ on 
        projecttas0_.ProjectId=project2_.Id 
where 
    user1_.InternalName=@p5 and 
    projecttas0_.Deadline<@p6 and 
    (projecttas0_.Status<>@p7 or projecttas0_.Status is null) and 
    (projecttas0_.Status<>@p8 or projecttas0_.Status is null) 
ORDER BY 
    CURRENT_TIMESTAMP 
OFFSET 0 ROWS FETCH FIRST @p9 ROWS ONLY;
 
select
    ...
from 
    WorkLogs worklog0_ 
    inner join Users user1_ on 
        worklog0_.UserId=user1_.Id 
    left outer join ProjectTasks projecttas2_ on 
        worklog0_.TaskId=projecttas2_.Id 
    left outer join Projects project3_ on 
        projecttas2_.ProjectId=project3_.Id 
where 
    user1_.InternalName=@p10 and 
    worklog0_.[Date]=@p11;

We can clearly see that it was batch of multiple queries that was sent to database server with one shot.

NB! If you want to mimic DbContext from Entity Framework Core then please take a look at my blog post NHibernate on ASP.NET Core.

Using Count() with future results

Using Count() function is not very straightforward but it’s there and it’s easy to use. I mentioned previously extension method called ToFutureValue(). This method helps us turn queries to count queries.

var futureCount = _nhSession.Query<WorkLog>
                            .Fetch(w => w.Task)
                            .ThenFetch(w => w.Project)
                            .Where(w => w.User.InternalName == userName &&
                                        w.Date == DateTime.Now.Date)
                            .ToFutureValue(v => v.Count());
 
var count = await futureCount.GetValueAsync();

Here is the query sent to database.

select 
    cast(count(*) as INT) as col_0_0_ 
from 
    WorkLogs worklog0_ 
    inner join Users user1_ on
        worklog0_.UserId=user1_.Id 
where 
    user1_.InternalName=@p0 and 
    worklog0_.[Date]=@p1

Same way we can use also other aggregate functions but also LINQ methods like Any(), FirstOrDefault(), etc.

NB! If you prefer to write queries using criteria API or if you don’t have any other option than criteria API then future results are also supported there. With criteria API you can also use query batches. Query batches let you define sets of queries that are sent to database together. Future results are gathered by NHibernate and they are all executed as soon as data is actually asked from some future query.

Wrapping up

Future results in NHibernate lead to decreased database traffic as all delayed queries are sent to database with one shot and results are also read back with one shot. It may put some additional load to client application but in these cases I suggest to use stateless sessions as they come with less overhead and no change tracking. Future results are not silver bullets and your mileage may vary. Like always – if you make any performance improvements to your application, make sure you measure the effect.

The post Optimize database traffic with future results in NHibernate appeared first on Gunnar Peipman - Programming Blog.

Let’s suppose we have customer entity that has customer type property defined as enum.

public class Customer
{
    public Guid Id { get; set; }
    public string Name { get; set; }
    public CustomerTypeEnum Type { get; set; }
}

Let’s define enum and make it use DisplayAttribute and resource file.

public enum CustomerTypeEnum
{
    [Display(Name = "Companies")]
    PrivateSector,
 
    [Display(Name = "PublicSector", ResourceType = typeof(Resources.Common))]
    PublicSector,
            
    Internal
}

There are three different cases together now:

1. Enum member with just a name
2. Enum member with Display attribute and static name
3. Enum member with Display attribute and resource file

My resource file is here. Important thing: set resource modifier to Public (this option is visible when resource window is wide enough).

I add now simple edit view and use Html.GetEnumSelectList<T>() extension method to fill select list with enum members. Notice how I added first empty selection (Select type …) as the only member of select list.

@model Customer
@{
    ViewData["Title"] = "Edit";
}
 
<h1>Edit</h1>
 
<form asp-action="Edit" class="row">
    <div class="col-4">
        <div asp-validation-summary="All" class="text-danger"></div>
        <input type="hidden" asp-for="Id" />
 
        <div class="form-group">
            <label asp-for="Name" class="control-label"></label>
            <input asp-for="Name" class="form-control" />
            <span asp-validation-for="Name" class="text-danger"></span>
        </div>
        <div class="form-group">
            <label asp-for="Type" class="control-label"></label>
            <select asp-for="Type" 
                    class="form-control" 
                    asp-items="Html.GetEnumSelectList<CustomerTypeEnum>()">
                <option>Select type ...</option>
            </select>
            <span asp-validation-for="Type" class="text-danger"></span>
        </div>
 
        <div class="form-group">
            <input type="submit" value="Save" class="btn btn-primary" />
        </div>
    </div>
</form>
 
<div>
    <a asp-action="Index">Back to List</a>
</div>

When we run application and move to edit form we can see that select list is filled with enum members and ASP.NET Core respects DisplayAttribute with static name and resource files.

If you don’t have control over enum (enum is defined in already built assembly) then you can’t apply Display attribute to enum and you need some other solution to get values you need.

Wrapping up

ASP.NET Core has built-in Html.GetEnumSelectList<T>() method we can use to turn enum members to select list items. The method is intelligent enough to support Display attribute and therefore we can use custom names for enum members and even translations from resource files. Well, until we have full control over enum. If enum comes from external library then we will get the names it has and if we want to change something then we have to find some other solution.

The post Displaying enum as select list in ASP.NET Core appeared first on Gunnar Peipman - Programming Blog.

NB! At the time of writing this post .NET Core 5.0 and EF Core 5.0 are both in Preview and things may change in future versions of EF Core 5.0.

I created new ASP.NET Core web application project with latest Visual Studio 2019 Preview and connected EF Core to one of my existing databases. I added just few entities and primitive DbContext to try out how ToQueryString() extension method works.

public class Customer
{
    public Guid Id { get; set; }
    public string Name { get; set; }
}
 
public class Project
{
    public Guid Id { get; set; }
    public string Title { get; set; }
    public string ProjectKey { get; set; }
 
    public Customer Customer { get; set; }
}
 
public class MyDbContext : DbContext
{
    public MyDbContext(DbContextOptions<MyDbContext> options) : base(options)
    {
    }
 
    public DbSet<Customer> Customers { get; set; }
    public DbSet<Project> Projects { get; set; }
}

I injected my DbContext to HomeController and in Index() action I wrote a query to see what ToQueryString() method returns. I was a bit clever when writing the query – notice how I used variable called prefix in LINQ expression.

public class HomeController : Controller
{
    private readonly MyDbContext _dataContext;
 
    public HomeController(MyDbContext dataContext)
    {
        _dataContext = dataContext;
    }
 
    public IActionResult Index()
    {
        var prefix = "A";
        var query = _dataContext.Projects
                                .Include(p => p.Customer)
                                .Where(p => p.Customer.Name.StartsWith(prefix))
                                .OrderBy(p => p.Customer.Name)
                                .ThenBy(p => p.Title)
                                .Select(p => new { Project = p.Title, Customer = p.Customer.Name });
 
        var queryString = query.ToQueryString();
        var result = query.FirstOrDefault();
        return View();
    }
}

When running the code in debug mode and checking the value of queryString variable we can see nice formatted SQL in Text Visualizer window.

The best thing is – we can take this SQL with copy and paste to SQL Server Management Studio (SSMS) or some other database tool to investigate the query closer. It’s ready to run without any modifications.

When ToQueryString() doesn’t work

There’s a little gotcha with ToQueryString() method – we cannot use it with methods that immediately run SQL query. It gives us valid query for ToList() and ToListAsync() methods but if we call FirstOrDefault() or FirstOrDefaultAsync() method on query then these methods add some more SQL that we cannot see with ToQueryString() method.

Let’s see what SQL is generated when we apply FirstOrDefault() method to query above. The actual query that is sent to database server is here.

SELECT TOP(1) [p].[Title] AS [Project], [c].[Name] AS [Customer]
FROM [Projects] AS [p]
LEFT JOIN [Customers] AS [c] ON [p].[CustomerId] = [c].[Id]
WHERE (@__prefix_0 = N'') OR ([c].[Name] IS NOT NULL AND (LEFT
([c].[Name], LEN(@__prefix_0)) = @__prefix_0))
ORDER BY [c].[Name], [p].[Title]

It’s almost the same but there’s TOP(1) after SELECT. The query gets even more different when we apply Count() method instead of FirstOrDefault().

SELECT COUNT(*)
FROM [Projects] AS [p]
LEFT JOIN [Customers] AS [c] ON [p].[CustomerId] = [c].[Id]
WHERE (@__prefix_0 = N'') OR ([c].[Name] IS NOT NULL AND (LEFT
([c].[Name], LEN(@__prefix_0)) = @__prefix_0))

For methods like FirstOrDefault() and Count() we need EF Core query logging. I will cover it in some of my future posts.

Wrapping up

ToQueryString() method helps us to see database queries generated by EF Core. It’s new feature in coming EF Core 5.0. We can use this method to get main part of SQL based on LINQ query we wrote. We cannot use ToQueryString() method to find out how query is modified by methods that send query to database – FirstOrDefault(), Count(), etc. For these methods we have to use EF Core logging that is not covered here. ToQueryString() method gives us SQL without making actual request to database server. The SQL we get is ready to run in database management tools for further investigation of generated query.

The post EF Core 5.0: Using ToQueryString() method to translate LINQ query to SQL appeared first on Gunnar Peipman - Programming Blog.

NB! It is recommended to use action method arguments instead of request query parameters in your controllers. If you have good reason to work with query strings directly then this post will help you.

Let’s start with quick’n’dirty example where query string is hardcoded.

[Fact]
public async Task ViewPdf_returns_view_if_application_exists_and_html_was_asked()
{
    var model = new ApplicationReviewModel();
    _controller.Request.QueryString = new QueryString("?showhtml=1&showdoc=0");
    
    _applicationServiceMock.Setup(a => a.GetDocumentReview(It.IsAny<Guid>(), It.IsAny<string>()))
                           .ReturnsAsync(() => model);
 
    var result = await _controller.ViewPdf(Guid.NewGuid()) as ViewResult;
 
    Assert.Same(model, result.Model);
    Assert.Equal("ApplicationPdf", result.ViewName);
}

But what if we don’t have primitive query strings and we don’t want to take care of formatting and encoding of query parameters?

Building query string using QueryCollection

To build more complex query strings we can use QueryCollection class. It’s a little bit unconveniet beast but it has everything we need to build up query strings from values we have.

Building query string using QueryCollection class needs three steps:

1. Create instance of StringValues class. It’s a class that takes either string or array of strings to its constructor. This class holds values for query string parameter.
2. Create Dictionary<string,StringValues> for query parameters. Add query parameter name and instance of StringValues to dictionary. Repeat it for all query parameters.
3. Create instance of QueryCollection and use dictionary as contructor argument.

Here’s one unit test to illustrate building of query string using StringValues, dictionary and QueryCollection. Notice QueryCollection is assigned to Query property of Request.

[Fact]
public async Task ViewPdf_returns_view_if_document_exists_and_html_was_asked()
{
    var model = new ApplicationReviewModel();
    var dictionary = new Dictionary<string, StringValues>();
    dictionary.Add("showhtml", new StringValues("1"));
    dictionary.Add("showtoc", new StringValues("0"));
 
    _controller.Request.Query = new QueryCollection(dictionary);
    
    _applicationServiceMock.Setup(a => a.GetDocumentReview(It.IsAny<Guid>(), It.IsAny<string>()))
                           .ReturnsAsync(() => model);
 
    var result = await _controller.ViewPdf(Guid.NewGuid()) as ViewResult;
 
    Assert.Same(model, result.Model);
    Assert.Equal("ApplicationPdf", result.ViewName);
}

Let’s modify this test a little bit and add query parameter with multiple values.

[Fact]
public async Task ViewPdf_returns_view_if_document_exists_and_html_was_asked()
{
    var model = new ApplicationReviewModel();
    var dictionary = new Dictionary<string, StringValues>();
    dictionary.Add("showhtml", new StringValues("1"));
    dictionary.Add("showtoc", new StringValues("0"));
    dictionary.Add("showpages", new StringValues(new[] { "1", "3" }));
 
    _controller.Request.Query = new QueryCollection(dictionary);
    
    _applicationServiceMock.Setup(a => a.GetDocumentReview(It.IsAny<Guid>(), It.IsAny<string>()))
                           .ReturnsAsync(() => model);
 
    var result = await _controller.ViewPdf(Guid.NewGuid()) as ViewResult;
 
    Assert.Same(model, result.Model);
    Assert.Equal("ApplicationPdf", result.ViewName);
}

Here’s the resulting query string in red box.

If query parameter has value that needs encoding then encoding is done automatically. We don’t have to worry about it. We just add parameters and their values and ASP.NET Core takes care of everything else.

Wrapping up

When unit testing ASP.NET Core controller actions that read query strings from request we can use QueryString class. It’s easy but we have to provide strictly correct query string. When using StringValues, dictionary and QueryCollection class we have to write more code. Luckily we don’t have to write ugly or too long code and all dirty work on building the final query string is done by ASP.NET Core.

The post Using query strings in ASP.NET Core unit tests appeared first on Gunnar Peipman - Programming Blog.

Azure pipelines with database update

Suppose we have solution with SQL Server database project and we want to use schema compare feature to update production database when new version of application is deployed. We don’t want to update database directly from Visual Studio but using Azure DevOps release pipelines. Here’s the pipeline I’m using in one of my projects. Blue blocks are steps in build pipeline and orange blocks are steps in release pipeline of Azure DevOps.

I don’t cover all steps of building Azure DevOps build and release pipelines as these topics are already covered thousands of times by different pages in internet.

Comparing schemas

SQL Server database projects make database schema versioned. We have history of database structure changes in Github or some other source code repository. It’s easy to take a look at history and see what changes and when were made to database. But there’s more.

If we change development database we can use schema compare feature of Visual Studio to compare changes between database and schema project.

Same way we can compare schema project to production database and if needed we can update database with one button click. That’s nice but it’s manual work and doesn’t fit well into automated release topic.

How to build database project on Azure DevOps

We can build database projects on Azure DevOps. As a result we get DACPAC file with database structure. If everything is build using MSBuild then things should be easy. But I had a problematic situation – I can’t build database project together with other projects as all other projects use .NET Core 3.0 and .NET Standard 2.1. .NET Core tooling today doesn’t support build database projects. It doesn’t mean we cannot build database projects at all when .NET Core projects are involved.

On Azure DevOps I solved the problem by adding MSBuild task to build pipeline. .NET Core tooling skips database project and MSBuild task skips .NET Core projects. So, both types of projects are together and at same time separated.

I configured it to build only schema project.

Next thing I had to do was to copy resulting DACPAC file to build artifacts drop folder. I used File Copy task for this.

Although file location in drop folder is far from perfect, the DACPAC file is there and available to release pipeline.

Deploying changes to Azure SQL

I have release pipeline that is run manually. It updates database and deploys new versions of web applications to Azure App Service. Updating database is first step in the release pipeline. There’s special pipeline step by Microsoft to update Azure SQL database. Just add it to pipeline, fill configuration parameters and you are good to go.

Database update is done based on DACPAC file built in previous section. All we have to do is to organize DACPAC file to Azure SQL Database deployment step and magic happens automatically.

Wrapping up

Visual Studio database projects are one of my main tools since Visual Studio 2010. Over times tooling has changed and improved. Today we can use database projects also in Azure DevOps build and release pipelines to automatically update Azure SQL databases. Although most of new web applications are built on ASP.NET Core, I was still able to use MSBuild on Azure DevOps to make database project build independent from web applications. Deploying update to live database is matter of just one database deployment task that is easy to set up and configure.

References

• Visual Studio database projects (Gunnar Peipman)
• Using Visual Studio database projects in real life (Gunnar Peipman)

The post Updating SQL Azure database using Visual Studio database project and Azure DevOps appeared first on Gunnar Peipman - Programming Blog.

Getting started

Before getting to code we need Azure portal account and some configuration parameters:

• TenantId – ID of Azure AD tenant. Open Azure AD settings in Azure portal to get tenant ID.
• ClientId and ClientSecret – these are for service principal (or account) we want to use for accessing Azure services. To set up service principal please jump to Azure documentation page How to: Use the portal to create an Azure AD application and service principal that can access resources.
• SubscriptionId – ID of Azure subscription where we want to create storage accounts. Open Subscriptions in Azure portal to get subscription ID.
• ResourceGroup – name of resource group under what storage accounts are created. Make sure the resource group exists already or create new one if you need.

With settings ready it’s time to get into code.

Fluent syntax for Azure blob storage

There are few important things to know before implementing class to create storage accounts. Fluent syntax for Azure services uses IAzure interface as a bridge head to row of fluent method calls. When building IAzure instance we need to provide all configuration parameters mentioned above.

Here’s the example. Azure.Configure() method starts the row of fluent configuring methods to build up client class we will use later.

var principalLogIn = new ServicePrincipalLoginInformation();
principalLogIn.ClientId = ClientId;
principalLogIn.ClientSecret = ClientSecret;
 
var environment = AzureEnvironment.AzureGlobalCloud;
var credentials = new AzureCredentials(principalLogIn, TenantId, environment);
 
_azure = Azure.Configure()
              .WithLogLevel(HttpLoggingDelegatingHandler.Level.Basic)
              .Authenticate(credentials)
              .WithSubscription(SubscriptionId);

To create blob storage account we will use initialized Azure client like shown in the following example.

await _azure.StorageAccounts.Define(accountName)
                            .WithRegion(Region.EuropeWest)
                            .WithExistingResourceGroup(ResourceGroup)
                            .WithAccessFromAllNetworks()
                            .WithGeneralPurposeAccountKindV2()
                            .WithOnlyHttpsTraffic()
                            .WithSku(StorageAccountSkuType.Standard_LRS)
                            .CreateAsync();

Although fluent methods shown above are just few of those provided, notice how new account is defined using clear and readable fluent methods that specify important aspects of account. Finally CreateAsync() method is called and then new storage account is created.

Similar fluent API-s are available also for some other Azure services and in big part they follow the same idea as shown here with Azure storage account.

Creating storage account and blob containers

It’s time to get to code and build something real. Let’s start with NuGet packages we need in our project:

• Microsoft.Azure.Management.Fluent
• Microsoft.Azure.Management.Storage.Fluent
• WindowsAzure.Storage (if you want to manage blobs in containers)

To try fluent storage API-s out, it doesn’t matter much what kind of application to create – be it console or web application or maybe Azure Function. I decided to go with web application as I’m building demo tenants manager application for some of my blog posts.

When new tenant is created we need to create also storage account for it and add blob containers that multitenant application expects to exist. Tenant can be created using long spaghetti code but I don’t like chaos and therefore I want more intelligent approach. Let’s try to write simple client class for creating storage accounts for tenants.

I start with simple interface.

public interface IStorageManager
{
    Task CreateAccountWithFolders(string name, params string[] container);
}

CreateAccountWithFolders() method takes new storage account name and array of container names that must be created with account. For this blog post this minimalistic interface is enough.

Let’s add a class called AzureBlobStorageManager to our solution. Here are namespaces used in this class.

using System;
using System.Threading.Tasks;
using Microsoft.Azure.Management.Fluent;
using Microsoft.Azure.Management.ResourceManager.Fluent;
using Microsoft.Azure.Management.ResourceManager.Fluent.Authentication;
using Microsoft.Azure.Management.ResourceManager.Fluent.Core;
using Microsoft.Azure.Management.Storage.Fluent;
using Microsoft.Azure.Management.Storage.Fluent.Models;

Class to create Azure storage accounts is here. Notice you need all configuration parameter values now to make the class actually work.

public class AzureBlobStorageManager : IStorageManager
{
    private const string TenantId = "4dcea3b0-b401-4b25-af70-971068b99d1c";
    private const string ClientId = "9cce9aed-69eb-45af-9bed-a5d7e403019f";
    private const string ClientSecret = "74d99bc5-e239-4308-ba15-e3e2acdb7fcf";
    private const string SubscriptionId = "6ca601d2-417f-4fa4-ae19-cd465102f8de";
    private const string ResourceGroup = "TestResourceGroup";
 
    private readonly IAzure _azure;
 
    public AzureBlobStorageManager()
    {
        var principalLogIn = new ServicePrincipalLoginInformation();
        principalLogIn.ClientId = ClientId;
        principalLogIn.ClientSecret = ClientSecret;
 
        var environment = AzureEnvironment.AzureGlobalCloud;
        var credentials = new AzureCredentials(principalLogIn, TenantId, environment);
 
        _azure = Azure.Configure()
                      .WithLogLevel(HttpLoggingDelegatingHandler.Level.Basic)
                      .Authenticate(credentials)
                      .WithSubscription(SubscriptionId);
    }
 
    public async Task CreateAccountWithFolders(string accountName, params string[] containerNames)
    {
        var availabilityResult = await _azure.StorageAccounts.CheckNameAvailabilityAsync(accountName);
        if(!availabilityResult.IsAvailable.Value)
        {
            throw new Exception("Account name is not available");
        }
 
        var account = await _azure.StorageAccounts.Define(accountName)
                                          .WithRegion(Region.EuropeWest)
                                          .WithExistingResourceGroup(ResourceGroup)
                                          .WithAccessFromAllNetworks()
                                          .WithGeneralPurposeAccountKindV2()
                                          .WithOnlyHttpsTraffic()
                                          .WithSku(StorageAccountSkuType.Standard_LRS)
                                          .CreateAsync();
 
        var containers = account.Manager.BlobContainers;
 
        foreach (var containerName in containerNames)
        {
            await containers.DefineContainer(containerName)
                            .WithExistingBlobService(ResourceGroup, accountName)
                            .WithPublicAccess(PublicAccess.None)
                            .CreateAsync();
        }
    }
}

For web applications we can register this class in framework-level dependency injection to inject it to controllers. This is how ConfigureServices() method of my Starpup class looks like.

public void ConfigureServices(IServiceCollection services)
{
    services.AddControllersWithViews();
    services.AddScoped<IStorageManager, AzureBlobStorageManager>();
}

And here is the minimalistic example of controller that makes use of IStorageManager.

public class HomeController : Controller
{
    private readonly IStorageManager _storageManager;
 
    public HomeController(IStorageManager storageManager)
    {
        _storageManager = storageManager;
    }
 
    // ...
 
    [HttpPost]
    public async Task<IActionResult> Edit(string account)
    {
        await _storageManager.CreateAccountWithFolders(account, "images", "documents");
        return View();
    }
 
    // ...
}

Using the code above I created new storage account with two blob containers – documents and images.

In practice we don’t get away so easily as we need to read out storage connection string after account is created and probably we also need SAS key for another services that access files on Azure storage. But with simple client class shown here you have good starting point for your own Azure storage management client.

Wrapping up

Although it was just an experiment to see how fluent API for Azure storage works, it was good find. When creating Azure storage accounts in code it’s important to have a good overview of features that new account will have. With fluent API-s I was able to write the code that looks like specification defining new account to be created. Good news is that there are similar fluent API-s available for other Azure services too.

The post Creating storage accounts and blob containers using Azure fluent API-s appeared first on Gunnar Peipman - Programming Blog.

Why MSSQL data and log files on Azure blob storage?

There are multiple reasons why one may want to keep MSSQL data and log files on Azure blob storage:

• Ownership and maintenance costs – Azure blob storage is stable and reliable cloud storage service and those who don’t want to build up their own storage solution may decide to benefit from Azure blob storage as MSSQL data files location.
• High-availability – it’s possible to configure multiple MSSQL virtual machines to use same data files and when one machine crashes then the other takes over withing few minutes. There’s no need to restore data as the latest state is already available on cloud.
• SaaS solution – SaaS providers who host MSSQL on virtual machines may find it more convenient for maintenance if data and log files are on Azure blob storage.

Now let’s try to set up a database to see how data and log files on blob storage work.

Generating shared access signature (SAS) key

First there must Azure storage account with at least one blob storage container where data and log files are held. We need shared access signature (SAS) key for MSSQL to be able to communicate with blob storage. SAS key can be generated from Azure portal.

SAS key can also be generated using Azure Storage Explorer – the convenient tool for managing Azure storage.
It’s free and I’m using it practically every day. Of course, those who are strong on PowerShell can also use PowerShell.

Creating cloud credential

Before creating databases and data files we need to create credential for MSSQL. It’s done with the following simple command.

CREATE CREDENTIAL [https://example.blob.core.windows.net/data]
WITH IDENTITY='SHARED ACCESS SIGNATURE',
SECRET = '<your SAS key>'

Make sure you also use correct URL to blob storage container where data files will be held.

Creating database to Azure blob storage

Creating database on Azure blob storage is simple. We use familiar CREATE DATABASE command with file locations.

CREATE DATABASE test
ON  
( 
    NAME = test_data,  
    FILENAME = 'https://sample.blob.core.windows.net/data/TestData.mdf' 
)  
LOG ON  
( 
    NAME = test_log,  
    FILENAME =  'https://testdb.blob.core.windows.net/data/TestLog.ldf'
)

It’s also possible to add new database file to existing database for tables we want to be available on cloud. Here are files of my demo database for my first linked servers blog post Querying MySQL from SQL Server using linked server. Database file OnBlobStorage1 is held on Azure blog storage.

Let’s see now how things work out.

My experience

There are not many concerns when MSSQL is hosted in same data center. There are fast cable connections between servers and services are maintained by well trained personnel. I wanted to try out a hybrid scenario – in-prem MSSQL and data file on cloud. My main concern was performance. Considering that MSSQL is located in Estonia and data file is located in Amsterdam – it’s approximately 1500 km over the air or 2000 km on the land – then what are the penalties?

Experiments with small databases went well. I didn’t noticed any delays that should affect web application consuming the database. I also tried things out with database of little bit over 2GB. I hosted data files and logs to cloud just to see how things works out. After getting data files to cloud I started application on my dev box to see what happens.

Few notes about those charts:

1. Incoming data (ingress) made high peak when I uploaded data files to blob storage.
2. Outgoing data (egress) made high peak when I took database online and started web application.
3. When browsing around in web application there were some small peaks on egress chart – usually around 20MB.

If MSSQL is configured correctly then there should not be much traffic between MSSQL and Azure blob storage. Of course, if data is added, updated or deleted then there will be additional traffic. Anyway I didn’t noticed any lags or other references to possible bottlenecks with data files on Azure.

Wrapping up

Although in my mind I was ready for surprises like huge latency and unexpected traffic between MSSQL and Azure blob storage, my experiment went very smooth and well. My local MSSQL has enough resources to run 2GB database and probably this is why there was not much traffic. Also I didn’t faced any bad delays although I made experiments on regular home connection that is not in perfect shape all the time. When planning to use Azure blob storage for MSSQL make some homework on validate if service works well for you and if costs doesn’t grow too high.

The post MSSQL data and log files on Azure blob storage appeared first on Gunnar Peipman - Programming Blog.

Using data from MSSQL linked servers

Linked servers are not directly supported by Entity Framework Core at LINQ level. There are few options how to handle data in linked servers:

• Custom SQL – we can write custom SQL queries for DbContext. On positive side we have full control over all SQL we want to run. On negative side we have custom SQL and we have to go around DbSets and create all kind of nasty hacks.
• Custom views – we can hide querying from linked servers to views and have DbSets like we usually do. It needs more work on database side but we don’t have to hack DbContext.

In this post I don’t stop on custom SQL approach as this is same as running custom SQL queries through DbContext and it’s well covered in many other public sources. Also I don’t prefer this approach as too much database gets into application code.

NB! This blog post doesn’t cover data modification in linked server. It seems like simple topic but there are actually many complexities to get over. In my practice data from linked servers is only for displaying it as otherwise there are good chances that direct changes to data will conflict with business rules set by other systems.

Sample customer tables

All the following code is based on my blog post Querying MySQL from SQL Server using linked server. I had two customers databases – one on MSSQL and other on MySQL. MySQL database was linked to MSSQL. The following screenshot shows customers table in both databases. Some fields are matching and some fields are different.

You can find guidance about setting up linked server from my blog post Querying MySQL from SQL Server using linked server.

Creating view with data from local and linked server

Using SQL Server Management Studio (SSMS) it’s easy to write a query to mix data from both customer tables shown above.

SELECT
    c.FirstName,
    c.LastName,
    crm_c.credit_rating as CreditRating
FROM 
    Customers c
    LEFT JOIN MYSQLCRM...customers crm_c ON
        c.ssn = crm_c.ssn
ORDER BY
    crm_c.credit_rating,
    c.LastName,
    c.FirstName

We take all fields from customers table on MSSQL and ask credit rating field from customers table on MySQL. It works like charm if link between databases is set up and configured appropriately.

For EF Core we want to have DbSet so we don’t need to mess with custom SQL in our code. The closer we will stay to DbSet pattern the better it is. Happily EF Core doesn’t care if data is coming from table or view. Let’s take the SQL query shown above and save it as view to MSSQL.

CREATE VIEW [dbo].[VW_CUSTOMERS]
AS
    SELECT        
        c.Id,
        c.FirstName,
        c.LastName,
        c.Ssn,
        c.BillingAddress,
        crm_c.credit_rating as CreditRating
    FROM
        dbo.Customers c
        LEFT JOIN MYSQLCRM...customers crm_c ON
            c.Ssn = crm_c.ssn
GO

We can open it in SSMS and see table with results.

There’s one thing left to do – we have to make sure that deletes to VW_CUSTOMERS view doesn’t affect linked database. For this we can use INSTEAD OF DELETE trigger. This trigger is run when rows are deleted from view and it replaces default delete behavior.

CREATE TRIGGER [dbo].[VW_CUSTOMERS_DELETE]
   ON [dbo].[VW_CUSTOMERS]
   INSTEAD OF DELETE
AS 
BEGIN
 
    SET NOCOUNT ON
 
    DELETE FROM Customers WHERE Id IN (SELECT Id FROM deleted)
 
END

What happens without this trigger? Without this trigger MSSQL detects the situation it cannot handle – modification to multiple tables. The result is error and cancelled delete operation.

Using linked server table from DbContext

To demonstrate using data from linked server with EF Core I created simple customer class based on VW_CUSTOMERS view.

public class SalesCustomer
{
    public int Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string Ssn { get; set; }
    public string BillingAddress { get; set; }
    public int? CreditRating { get; set; }
}

We also need DbContext class to communicate with database. It looks almost like any other DbContext but there’s one trick – CreditRating property is marked as read-only. Of course, to make DbContext use VW_CUSTOMERS view instead of looking for SalesCustomer table we have to call ToTable() method on SalesCustomer entity.

public class SalesDbContext : DbContext
{
    public SalesDbContext(DbContextOptions<SalesDbContext> options) : base(options)
    {
    }
 
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<SalesCustomer>().ToTable("VW_CUSTOMERS");
        modelBuilder.Entity<SalesCustomer>()
                    .Property(p => p.CreditRating)
                    .ValueGeneratedOnAddOrUpdate();
 
        base.OnModelCreating(modelBuilder);
    }
 
    public DbSet<SalesCustomer> Customers { get; set; }
}

We can create more properties based on fields in linked tables but we have to mark them all as read-only to avoid conflicting changes to databases owned by other systems.

Wrapping up

Although MSSQL linked servers are not something we are using everyday and EF Core doesn’t have out-of-box tooling for it, we can still work out views and triggers to make mixed data from local and linked server visible as a table for EF Core. We marked customer entity properties that come from linked server as read-only to avoid making changes to linked table. This is usual requirement if linked table belongs to some other system that has its own business rules. We left all complex stuff to database and on EF Core we are using views as tables.

The post Using data from MSSQL linked server with EF Core appeared first on Gunnar Peipman - Programming Blog.

What is linked server?

Linked server in MSSQL is some other database server connected to given one, making it possible to query and manipulate data in other databases. By example, we can link some MySQL database to MSSQL and use it almost like any other database on MSSQL.

 
Image is taken from MSSQL 2019 documentation page
Linked Servers (Database Engine)

Although communication with linked servers goes through OLE DB providers, there is also OLE DB provider for ODBC and we can use it if our external database doesn’t have OLE DB provider.

NB! Linked server is available for whole SQL Server instance. It means that all SQL Server databases can use linked server to retrieve data.

Linking MySQL to SQL Server

Adding linked server and configuring connection setting is not always easy and straightforward.

To get MySQL linked to SQL Server I needed to create ODBC DSN for MySQL (I named it as MySQLCrm). Before going to next steps, make sure that ODBC data source works.

Follow these steps to link MySQL to SQL Server:

1. Run SQL Server Management Studio (SSMS)
2. Connect to your server
3. Expand Server Objects node from tree at left
4. Right-click on Linked Servers
5. Select New Linked Server…

You should see the following dialog (or bit different but the idea remains the same).

NB! Pay extra attention to what you insert to this dialog. With this set of data I made link work. I tried different values and if something is one millimeter wrong then connection fails. It’s damn sensitive dialog.

Connection string to MySQL database should be like shown here:

DRIVER=(MySQL ODBC 8.0 Unicode Driver); SERVER=localhost; PORT=3306; DATABASE=crmlinked; USER=crmuser; PASSWORD=crm_user_password; OPTION=3;

Also pay attention to OPTION=3 – without this I got only errors back when connecting to linked server.

Try to save by clicking OK and see if you can browse to linked server. If you get errors then right-click on server and select properties. Keeping the dialog open, move to Server Options page. Set RPC and RPC Out settings to True.

I’m still not very sure what are these options doing but some of those who had issues with link to MySQL made it work after setting RPC-s to true.

To make querying actually work, we need one more little change that affects whole OLE DB provider and therefore all connections using it. Open Providers node under Linked Servers, right-click on MSDASQL (this is OLE DB provider for ODBC data sources) and select properties.

Check the box before Level Zero only and click OK to save changes.

Querying data from linked server

Querying linked databases is actually easy. Here’s the customers table from crmlinked database in MySQL. This database is linked to my SQL Server.

Syntax for querying linked server is a little bit different from what we usually write on SQL Server. We need to use four-part names: server.database.schema.table. As there’s no schemas on MySQL and connection string specifies database name, then we can leave these out like shown here.

select * from MYSQLCRM...customers

Running this query from SSMS gives the following output. It’s the same data that is in MySQL customers table.

Of course, we can also write more complex queries. Everything that ODBC can handle is okay.

Mixing data from local and linked server

Tables from linked server are not totally isolated from local database tables and views. We can also mix data from local and linked server.

To demonstrate mixed query over local and linked tables let’s write simple query to get all customers from local table and their credit ratings from linked table.

SELECT
    c.FirstName,
    c.LastName,
    crm_c.credit_rating as CreditRating
FROM 
    Customers c
    LEFT JOIN MYSQLCRM...customers crm_c ON
        c.ssn = crm_c.ssn
ORDER BY
    crm_c.credit_rating,
    c.LastName,
    c.FirstName

Running this query gives us the following output.

As Mark is not present in MySQL database (suppose he is new customer in e-shop and sales department doesn’t have him yet in their CRM system) then he doesn’t have credit rating available. Credit ratings for John and Mary are coming from MySQL in current case.

Using OPENQUERY() to execute query in linked server

The examples above make all data processing on SQL Server. It can be very unoptimal if there’s a lot of data in linked server’s tables. We may want – or usually want – to process some data in linked server before SQL Server starts local processing. For this we have OPENQUERY().

Here’s the example of using OPENQUERY() function in mixed query. We have to specify linked server name and SQL query to run in linked server when calling OPENQUERY(). The query in red is executed in MySQL server and results are read to SQL Server for further processing.

SELECT
    c.FirstName,
    c.LastName,
    crm_c.credit_rating as CreditRating
FROM 
    Customers c
    LEFT JOIN OPENQUERY(MYSQLCRM, '
            SELECT 
                c.credit_rating
            FROM 
                customers p
                left join loyalty_points lp on
                    c.customer_id = lp.customer_id
            WHERE
                lp.points > 1000
        ') crm_c ON
        c.ssn = crm_c.ssn
ORDER BY
    crm_c.credit_rating,
    c.LastName,
    c.FirstName 

OPENQUERY() is great way to optimize and speed up mixed queries by running more complex queries over linked server data in linked server.

Wrapping up

Linked servers is powerful feature of SQL Server making it easy for us to use data from external servers. There are two ways to write queries using data from linked servers – direct queries that do all processing on SQL Server and OPENQUERY() that lets us do some processing in remote server. Linked server is integration and therefore using it needs extra care. Planning and performance measuring are must-be activities when planning to use linked server.

The post Querying MySQL from SQL Server using linked server appeared first on Gunnar Peipman - Programming Blog.

NB! My goal here is to stay strictly with Entity Framework Core or some other mapper that supports LINQ queries. Replacing ORM is usually extreme case and during my 20+ years in software development I have seen only few cases when it was done. If you prefer NHibernate over EF Core then check out my post NHibernate on ASP.NET Core to see how imitate DbContext with NHibernate.

“Repositorifying” DbContext

Making DbContext similar to typical unit of work that contains repository instances is not easy. One weakness of repository pattern is that it can be easily abused as a querying methods store. I have seen repositories that are thousands of lines long thanks to querying methods. But hey, they are still easy to use in unit tests as they implement interfaces we can mock. DbContext is different beast. It can easily be injected to repositories and other classes but it’s not easy to inject querying part of repositories there.

It would be nice to have something like shown here.

public class SalesDbContext : DbContext
{
    public SalesDbContext(DbContextOptions<SalesDbContext> options) : base(options)
    {
    }
 
    public DbSet<Customer> Customers { get; set; }
 
    // Other DbSets
}

public void DoSomething()
{
    var debtors = dbContext.Customers.ListDebtors();
 
    // ...
 
    var date = DateTime.Now.AddDays(-14);
    var newCustomers = dbContext.Customers.ListNewRegistrations(date);
}

There are some obstacles on our way that are hard – if not impossible – to remove with simple tricks. Let’s briefly explore our options:

• DbSet<T> is not for inheriting– internals of instantiating DbSet<T> are complex and luckily these complexities are hidden from us. Still these complexities are good enough reason for product group to not support extending of DbSet<T>. Let’s agree with them and abandon the idea of using digital violence to get our will.
• Extension methods for DbSet<T> – we can build extension methods for given DbSet<T> and host these methods in some static class in same namespace. It’s the easy way out but it comes with price – we can’t easily mock extension methods. We have to use type isolation and there is limited number of tools with this feature.
• Querying methods in DbContext – another easy way out but again there’s price to pay. Piling all querying methods to DbContext will bloat this class. It can get even worse if querying methods call each other. I wouldn’t go with this approach.
• Injecting querying interfaces to DbContext class – creates chicken-egg situation because DbContext needs querying classes to be injected and query classes need DbContext to be injected.

Of approaches mentioned above only to are worth to experiment: extension methods for DBSet<T> and querying classes that are injected to DbContext.

Extension methods for DbSet<T>

Extension methods are easiest ones to implement. They work until we stay with pure LINQ. In case of multiple database types and custom SQL queries this approach doesn’t work. Let’s write extension methods for DbSet<Customer>.

public static class SalesDbContextCustomersExtensions
{
    public static IQueryable<Customer> ListDebtors(this DbSet<Customer> customers)
    {
        return customers.Where(c => c.Balance < 0);
    }
 
    public static IQueryable<Customer> ListNewRegistrations(this DbSet<Customer> customers, DateTime fromDate)
    {
        return customers.Where(c => c.Created >= fromDate);
    }
}

We have separate class with extension methods and we don’t bloat DbContext with queries. Still these extension methods are easy to use.

public void DoSomething()
{
    var debtors = dbContext.Customers.ListDebtors();
 
    // ...
 
    var date = DateTime.Now.AddDays(-14);
    var newCustomers = dbContext.Customers.ListNewRegistrations(date);
}

Those who want to isolate direct access to DbSets can’t use extension methods as marking DbSets protected restricts external access to them.

If you want to go with extension methods then here few more ideas what you can do in data layer: Implementing Query Specification pattern in Entity Framework Core and Readable fluent queries with Entity Framework Core.

Injecting querying classes to DbContext

If extension methods are not an option then we have to introduce querying classes that are injected to DbContext. Like mentioned above there’s a chicken and egg problem, but it’s not hard to solve. Let’s start with intrface and class for querying customers. We use interface because we probably want to use fake version of querying class when writing unit tests.

public interface ICustomerQueries
{
    void SetDbContext(SalesDbContext dbContext);
 
    IQueryable<Customer> ListDebtors();
    IQueryable<Customer> ListNewRegistrations(DateTime date);
}
 
public class CustomerQueries : ICustomerQueries
{
    private SalesDbContext _dbContext; 
 
    public virtual IQueryable<Customer> ListDebtors()
    {
        return _dbContext.Customers.Where(c => c.Balance < 0);
    }
 
    public virtual IQueryable<Customer> ListNewRegistrations(DateTime date)
    {
        return _dbContext.Customers.Where(c => c.Created >= date);
    }
 
    public void SetDbContext(SalesDbContext dbContext)
    {
        _dbContext = dbContext;
    }
}

Notice two things. SetDbContext() method – we have to assign DbContext when instance of ICustomerQueries is injected to DbContext. Otherwise there’s no connection between these two classes. Customer queries class has virtual methods. We can keep there pure LINQ methods and extend this class if we need let’s say SqlServerCustomerQueries class that overrides some methods to use SQL Server specific SQL commands.

Here’s our DbContext after changes.

public class SalesDbContext : DbContext
{
    public SalesDbContext(DbContextOptions<SalesDbContext> options, 
                          ICustomerQueries customerQueries) : base(options)
    {
        CustomerQueries = customerQueries;
        customerQueries.SetDbContext(this);
    }
 
    public DbSet<Customer> Customers { get; set; }
 
    // Other DbSets
 
    public ICustomerQueries CustomerQueries { get; private set; }
 
    // Other queries
}

In assigns itself to DbContext property of all injected querying classes. It’s easy but still something we have to remember to do when introducing new quering class for some DbSet<T>.  Our previous dummy demo method looks now just a little bit different than before.

public void DoSomething()
{
    var debtors = dbContext.CustomerQueries.ListDebtors();
 
    // ...
 
    var date = DateTime.Now.AddDays(-14);
    var newCustomers = dbContext.CustomerQueries
                                .ListNewRegistrations(date);
}

For ASP.NET Core we have to add querying interface and class to dependency injection.

services.AddScoped<ICustomerQueries, CustomerQueries>();

Now we have a bit inconveniet DbContext that actually acts as an unit of work with local instances of repositories.

Supporting database-specific commands

As I previously mentioned we need sometimes database-specific commands. These commands can contain SQL that is understood only by one supported database. It’s clear we cannot run these commands against other types of databases.

Database-specific commands are usual in applications that have reporting. Some ideas about how to implement common reporting with Entity Framework Core are given in my posts Execute raw SQL commands in Entity Framework Core and DataSet and DataTable based ad-hoc reporting with ASP.NET Core.

This is customer queries class we created above with one little change – DbContext is now available for inheriting classes.

public class CustomerQueries : ICustomerQueries
{
    protected SalesDbContext dbContext { get; private set; }
 
    public virtual IQueryable<Customer> ListDebtors()
    {
        return dbContext.Customers.Where(c => c.Balance < 0);
    }
 
    public virtual IQueryable<Customer> ListNewRegistrations(DateTime date)
    {
        return dbContext.Customers.Where(c => c.Created >= date);
    }
 
    public void SetDbContext(SalesDbContext context)
    {
        dbContext = context;
    }
}

We marked querying methods as virtual and there was a reason to do so. Suppose ListDeptors() method is tricky and we need custom SQL for this when using MySQL.

public class MySqlCustomerQueries : CustomerQueries
{
    public override IQueryable<Customer> ListDebtors()
    {
        return dbContext.Customers.FromSql("MySQL specific SQL");
    }
}

Same way we can also build specialized classes for SQL Server, Oracle, Postgre etc.

Mocking querying classes in unit tests

Querying classes we inject using their interfaces can be easily mocked in unit tests. Suppose we have CustomersService class with kind method to zero all debts.

public class CustomersService : ICustomersService 
{
    private readonly SalesDbContext _salesDbContext;
 
    public CustomersService(SalesDbContext salesDbContext)
    {
        _salesDbContext = salesDbContext;
    }
 
    public void ClearDebts()
    {
        var debtors = _salesDbContext.CustomerQueries.ListDebtors();
 
        foreach(var debtor in debtors)
        {
            debtor.Balance = 0;
        }
 
        _salesDbContext.SaveChanges();
    }
}

Using xUnit and Moq we can write the following test class to check if ClearDebts() sets all debts to zero.

public class CustomerServiceTests
{
    private Mock<ICustomerQueries> _customerQueriesMock;
    private SalesDbContext _dbContext;
 
    public CustomerServiceTests()
    {
        _customerQueriesMock = new Mock<ICustomerQueries>();
 
        var options = new DbContextOptionsBuilder<SalesDbContext>()
                                .UseInMemoryDatabase(Guid.NewGuid().ToString())
                                .Options;
 
        _dbContext = new SalesDbContext(options, _customerQueriesMock.Object);
    }
 
    [Fact]
    public void ClearDebths_should_set_debtors_balance_to_zero()
    {
        var service = new CustomersService(_dbContext);
        var debtors = new List<Customer>
        {
            new Customer { Id = 1, Name = "John Doe", Balance = -20 },
            new Customer { Id = 2, Name = "Jane Doe", Balance = -5 }
        };
        _customerQueriesMock.Setup(c => c.ListDebtors())
                            .Returns(debtors.AsQueryable());
 
        service.ClearDebts();
 
        Assert.Equal(0, debtors[0].Balance);
        Assert.Equal(0, debtors[1].Balance);
    }
}

In real world we should have more tests but for illustration this one is okay. We can create class-level mocks also for other querying classes we need in DbContext. Those who don’t like mocks can use stub classes. It’s easy because querying classes have interfaces.

How this approach works in practice?

After getting to point where I was kind of okay with the solution, of course I was eager to try it out on some real project. Luckily I have one application under development that is perfect candidate for test-drive. It’s very proccess-centric having complex service classes that make heavy querying.

Some characteristics to understand the system I’m using as lab rat:

• two web application projects
• one project with backgroud services
• shared projects for business logic and data layers
• database with ~40 tables (3 tables with many fields)
• ten service classes (few of them really big)

I’m using DbContext in applications through interface meaning that application see only what they need to see. I can restrict direct access to DbSet<T> properties and introduce generic Get, Save and Delete methods if needed. Querying classes form nice layer that handles all queries and I can build additional layers behind these if demands on querying happen to grow.

Leaving out the inconvenience on assigning DbContext to querying classes after they are injected to DbContext, I’m okay with my solution. Also it makes unit tests smaller and cleaner. I can easily mock querying objects and I don’t have to write code to insert correct test data to DbContext to make queries return the results that tests expect.

Wrapping up

Getting repositories querying interface to DbContext the way that we may have multiple implementations wasn’t very easy task. To be flexible we had to create querying classes, inject these to DbContext and assign DbContext to their data context property. Even if can safely extend DbSet<T> the problem will be the same – DbSet<T> needs to know DbContext. We created flexible query classes. There’s base class that hosts all pure LINQ-based querying methods. If we don’t have anything database specific then we can use these base classes. If we have something database-specific then we can extend database-specific querying classes from base class and override methods that need custom SQL. We have now DbContext that works as unit of wort containing set of repository instances.

The post Implementing repository querying interface in EF Core DbContext appeared first on Gunnar Peipman - Programming Blog.