Tuesday, December 10, 2019

Multi-Tenancy is Hard: ASP.Net Boilerplate Makes it Easy

If you're liable to start a new web project that even might need multi-tenancy, you should probably use ASP.Net Boilerplate (ABP). As I've blogged about previously, ABP will save weeks of dev time on new websites, even without multi-tenancy. However, as soon as you bring on a second customer, I'd estimate you'll eliminate over a month of development time (extrapolating from my 2 ABP project data points, solid math).


But what even is multi-tenancy? What are typical solutions? And how does ABP save so much dev time? Fortunately, I just released a new episode of Code Hour to answer these questions:



If you don't have 35 minutes to invest right now (less at chipmunk speed, even less if you stop after ~6 minutes when I switch to live coding) then let me tl;dr (tl;dw? 😜):

Multi tenancy is a software architecture in which a single application is shared between multiple customers. Each customer only sees their own data and is completely unaware that there are other customers.

There are several ways to approach the problem, as described in ABP's Multi-Tenancy Documentation.

1. Multiple Deployment - Multiple Database


This is the less work up-front approach. There's no need for a framework, you just deploy your app multiple times, once per tenant. This offers the best performance (because tenants can be scaled independently) and best data isolation (e.g. database backups will never contain other customers data).

In exchange it requires the highest maintenance cost and most challenging deployments. The maintenance challenge is you'll need to pay for an app and database for each customer, and if you're passing those costs on, it could be detrimental to smaller customers. The deployment risk is you'll have to be extremely structured in deployments of app and database script to all environments and carefully consolidate error logs.

But there are three other common solutions, and in these scenarios ABP brings huge benefits to the table:

2. Single Deployment - Single Database


This is my favorite approach because it's simplest and least expensive to maintain. In this solution each database table contains a foreign key to a tenants table. All database queries must filter to retrieve items for the current user's tenant and insert records with the current users tenant. When doing it by hand it would be a pain to apply these filters to every single query. Enter ABP.

By inheriting from the IMustHaveTenant interface, ABP give all entities a foreign key to a Tenants table. Then, silently in the background, ABP figures out the tenant of the currently logged in user and for all queries only returns the records from that tenant. If a user creates an entity with IMustHaveTenant, then ABP additionally automatically sets the correct foreign key. No code is required and all database queries pick up this filter (just like the soft delete I described in Be a Hero On Day 1).

The downside to this approach is that one tenant with a lot of data could affect the performance of other tenants, and some users might worry about security since all data lives in the same database. So there's another approach ABP provides:

3. Single Deployment - Multiple Database


When a user from the host (host = a singleton tenant that can create other tenants) creates a tenant, they can specify a connection string specific to that tenant. ABP even offers a cool solution to data migrations that I explain in the video (at ~12:55). But the end result is much better data isolation, great performance, but still a potentially high price tag since you could be paying for one database per customer.


4. Single Deployment - Hybrid Databases


ABP offers the best of the last two solutions by allowing some tenants to live in shared database instances and others to live in their own databases. This offers data isolation and performance to tenants that need it (or will pay for it), and value for tenants that don't (or won't).

What's awesome about ABP is that it works identically from a code perspective for all of the above multi-tenancy approaches. The only difference is whether a tenant's connection string property is provided or not. The filtering, permissions, and migrations are otherwise all identical.

Summary


If you've interested in more details (such as how to disable tenant filtering) please check out the video (and like and subscribe and all that). Also, hit me up on twitter or in the comments if you have any questions, comments, or threats.

Thursday, September 12, 2019

Stored Procedures in ASP.Net Boilerplate

Using stored procedures in ASP.Net Boilerplate is a little trickier than you might imagine.  There's the problem of getting them into the database with EF Code-First migrations.  Then there's the issue of how to call them through the repository pattern while keeping things unit testable.

And did you know there are three different way to call stored procedures?  Which you use depends on on whether they return an existing entity, return nothing, or return something else entirely.  That last option is the trickiest.

Fortunately I just released Episode 23 of Code Hour, that lays it all out:



All the code is available in this tidy little sproc pull request.

See also: the official ASP.Net boilerplate documentation on stored procedures.

Monday, August 26, 2019

3 Ways To Refactor EF Linq Queries w/o Killing Perf

Extracting a method from an Entity Framework LINQ query can quietly kill performance.  Here are three easy solutions including: Expressions, Extension Methods, and LinqKit.

Embed from Getty Images

Enumeration<Problem>

Last week I was shocked to discover that refactoring Entity Framework LINQ queries for readability or reusability by extracting a method can quietly swap a query out of SQL and into in-memory processing and kill performance.

Here's a simplified version of my problem.

private async Task<List<User>> GetUsersMatching(IMainFilterDto filter, string prefix)
{
   var usersQuery = Users.Where(u =>
      (filter.StartDate == null || u.CreationTime > filter.StartDate) &&
      (filter.EndDate == null || u.CreationTime <= filter.EndDate) &&
      u.Name.StartsWith(prefix));
   return await usersQuery.ToListAsync();
}


I had a site-wide filtering object supplied by the front-end, but then I needed to do something else specific to the task at hand like the .StartsWith().

Then elsewhere I needed something very similar:

private async Task<List<User>> GetUsersWithoutRoles(IMainFilterDto filter)
{
       var usersQuery = Users.Include(i => i.Roles).Where(u =>
              (filter.StartDate == null || u.CreationTime > filter.StartDate) &&
              (filter.EndDate == null || u.CreationTime <= filter.EndDate) &&
              !u.Roles.Any()
              );

       return usersQuery.ToList();
}

Uch.  The common code between the two isn't DRY and feels awful.  If I ever needed to change it, perhaps by replacing > with >= I'd have to track down all the places with that code.  I was tempted to extract it like this:

private bool ApplyMainFilter(IMainFilterDto filter, User u)
{
       return (filter.StartDate == null || u.CreationTime > filter.StartDate) &&
              (filter.EndDate == null || u.CreationTime <= filter.EndDate);
}

And use it like this:

private async Task<List<User>> GetUsersWithoutRoles(IMainFilterDto filter)
{
    var usersQuery = Users.Where(u =>
        ApplyMainFilter(filter, u) &&
        u.Name.StartsWith(prefix));

That certainly reads better.  And when I tested it, it returns the exact same results.  Sadly, when I ran it through LINQPad the original query (where filter has a non-null start date but null end date) turns from this:

SELECT [stuff]
FROM
[Users] AS [u]
WHERE ([u].[CreationTime] > @__filter_StartDate_0) AND (([u].[Name] LIKE @__prefix_1 + N'%' AND (LEFT([u].[Name], LEN(@__prefix_1)) = @__prefix_1)) OR (@__prefix_1 = N''))

into:

SELECT [stuff]
FROM
 [Users] AS [u]WHERE ([u].[Name] LIKE @__prefix_1 + N'%' AND (LEFT([u].[Name], LEN(@__prefix_1)) = @__prefix_1)) OR (@__prefix_1 = N'')

It dropped out all the code in ApplyMainFilter()!  That may not look terrible in this simple example, but imagine more complex scenarios.  It could result in a lot more records returning from the database.  It could create a network bottleneck or put excess strain on the middleware.

Worst of all it could prevent the database from doing what it does best: use indexes to optimize query execution.  This could mean bypassing existing indexes, preventing query optimization with future indexes, or reducing the effectiveness of performance recommendations in e.g. the Azure SQL database by hiding the problem from the database entirely.

Incidentally, if you'd prefer to see a video of the problem and solutions, check out Episode 22 of Code Hour:



return solution[0]

The solution turned out to be fairly easy once I identified the problem.  Understanding how Entity Framework works internally helped.  It's all about expression trees, which I've written about before (ok, I wrote that 11 years ago, but the fundamentals it describes are still solid).

Anticipating all the possible ways someone might pass arbitrary C# language into a where clause and turning it all into SQL is a hard problem.  I needed to give Entity Framework a hand.  One way to do that is to return a fully parseable expression tree like Expression<Func<User, bool>> rather than just a bool or a Func<User, bool>.  It looked like this:

private Expression<Func<User, bool>> GetMainFilterQuery(IMainFilterDto filter)
{
    return u => (filter.StartDate == null || u.CreationTime > filter.StartDate) &&
        (filter.EndDate == null || u.CreationTime <= filter.EndDate);

}

Executed like this:

private async Task<List<User>> GetUsersMatching(IMainFilterDto filter, string prefix)
{
       var usersQuery = Users
              .Where(GetMainFilterQuery(filter))

              .Where(u => u.Name.StartsWith(prefix));

Isn't that an aesthetically pleasing solution?  It's reusable, reads well, and converts to SQL.

But Wait, There's More

But, if you're up for reading further I thought I'd present one more more interesting option.  If you're into flow style API's then an extension method approach may be perfect:

public static class QueryUtils
{
    public static IQueryable AppendMainFilterQuery(
        this IQueryable existingQuery, IMainFilterDto filter)
    {
        return existingQuery.Where(u => (
            filter.StartDate == null ||  u.CreationTime > filter.StartDate) &&
            (filter.EndDate == null || u.CreationTime <= filter.EndDate));
    }
}

Which is a little harder to read, but allows this:

private async Task<List<User>> GetUsersMatching(IMainFilterDto filter, string prefix)
{
    var usersQuery = Users
        .Where(u => u.Name.StartsWith(prefix))
        .AppendMainFilterQuery(filter);

That reads nicely, is reusable, and like the 1st solution keeps the SQL exactly how it was initially.

OR LinqKit?

I ran all this by a smart co-worker who recommended I check out LinqKit in case I ever needed to do anything more complicated.  Among other things LinqKit allows you to build up expressions across multiple methods.  For instance if I needed an OR clause instead of an AND clause it might look like this:

private ExpressionStarter<User> GetMainFilterPredicate(IMainFilterDto filter)
{
    var predicate = PredicateBuilder.New<User>().Start(u =>
        (filter.StartDate == null || u.CreationTime > filter.StartDate) &&
        (filter.EndDate == null || u.CreationTime <= filter.EndDate));
    return predicate;
}

private Task<List<User>> GetUsersMatching(IMainFilterDto filter, string prefix)
{
    var predicate = GetMainFilterPredicate(filter);
    predicate = predicate.Or(u => u.Name.StartsWith(prefix));
    return Users.Where(predicate).ToListAsync();
}

Pretty nifty.

Summary

I like the 1st approach if I don't need anything more complex, but regardless, identifying how not to refactor LINQ queries is the important part.  If you have any other creative solutions please share in the comments or hit me up on twitter.

Thursday, July 25, 2019

Fighting File Downloads and Dinosaurs with NSwag (via ASP.Net Boilerplate)

Technically it was the dinosaurs, approximately 240 million years ago, that first solved downloading files from web servers.  So doing it with a modern tech stack with an auto-generated client-side proxy should be easy, right?

Embed from Getty Images

Sadly, I've lived with this embarrassing hack to a rudimentary problem for months because a confluence of technologies that make my life easy for common actions make it hard for infrequent ones.  And sometimes, when life is hard, you give up and write something godawful to teach life a lesson.  Make it take the lemons back.

This week, I won round 2 by solving the problem correctly.  Pure joy, I'm tellin' ya.  I just had to share.

Fellow humanoids: prepare to rejoice.

The Problem


My tech stack looks like this:

  • ASP.Net Core - for back end
  • Angular 7 - for front end (it requires a custom CORS policy, more on that later)
  • Swashbuckle - exposes a dynamically generated swagger json file 
  • NSwag - consumes the swagger file and generates a client proxy

It happens to look like that because I use this excellent framework called ASAP.Net Boilerplate (also check out this amazing ASP.Net Boilerplate Overview, then subscribe, the guy who produced it must be a genius).  But whatever, you should totally use that stack anyway because those four technologies were preordained by the gods as a path to eternal bliss.  That's a fact, the Buddha said it, go look it up.

Also, the API client proxy that NSwag generates is totes amazing -- saves a huge amount of time and energy.  Unless, it turns out, you're trying to download a dynamically generated Excel file in TypeScript on button click and trigger a download.

A Naive Solution


After a brief web search one couldn't be blamed for nuggetting (a real word, apparently, but not what you think) EPPlus and writing an ASP.Net controller like this:

[Route("api/[controller]")]
public class ProductFilesController : AbpController
{
    [HttpPost]
    [Route("{filename}.xlsx")]
    public ActionResult Download(string fileName)
    {
        var fileMemoryStream = GenerateReportAndWriteToMemoryStream();
        return File(fileMemoryStream,
            "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
            fileName + ".xlsx");
    }

    private byte[] GenerateReportAndWriteToMemoryStream()
    {
        using (ExcelPackage package = new ExcelPackage())
        {
            ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("Data");
            worksheet.Cells[1, 1].Value = "Hello World";
            return package.GetAsByteArray();
        }
    }
}

I took the approach above and naively expected Swashbuckle to generate a reasonable swagger.json file.  It generated this:

"/api/ProductFiles/{filename}.xlsx": {
    "post": {
        "tags": ["ProductFiles"],
            "operationId": "ApiProductFilesByFilename}.xlsxPost",
            "consumes": [],
            "produces": [],
            "parameters": [{
                "name": "fileName",
                "in": "path",
                "required": true,
                "type": "string"
            }],
            "responses": {
            "200": {
                "description": "Success"
            }
        }
    }
},

See the problem?  You're clearly smarter than me.  I ran NSwag and it generated this:

export class ApiServiceProxy {
    productFiles(fileName: string): Observable<void> {

Oh no.  No, Observable of void, is not going to work.  It needs to return something, anything.  Clearly I needed to be more explicit about the return type in the controller:

public ActionResult<FileContentResult> Download(string fileName) { ... }

And Swagger?

"/api/ProductFiles/{filename}.xlsx": {
    "post": {
        "tags": ["ProductFiles"],
            "operationId": "ApiProductFilesByFilename}.xlsxPost",
            "consumes": [],
            "produces": ["text/plain", "application/json", "text/json"],
            ...
            "200": {
                "description": "Success",
                    "schema": {
                    "$ref": "#/definitions/FileContentResult"
                }
            }

Perfect!  Swagger says a FileContentResult is the result and NSwag generates the exact code I was hoping for.  Everything looks peachy ... until you run it and the server says:

System.ArgumentException: Invalid type parameter 'Microsoft.AspNetCore.Mvc.FileContentResult' specified for 'ActionResult'.

Gah!  And what about specifying FileContentResult as the return type?  Fail.  It's back to void.



Ohai ProducesResponseType attribute.

[HttpPost]
[Route("{filename}.xlsx")]
[ProducesResponseType(typeof(FileContentResult), (int)HttpStatusCode.OK)]
public ActionResult Download(string fileName)

Swagger, do you like me now?  Yes.  NSwag?  Yes!  Serverside runtime you love me right?  Yup.  Finally NSwag you'll give me back that sweet FileContentResult if I'm friendly and sweet?

ERROR SyntaxError: Unexpected token P in JSON at position 0

inside the blobToText() function?!

NOOOOOOOOOOOOOOOOOO
😡😡😡😡😡😡😡😡😡😡😡😡😡😡😡😡😡😡😡😡😡😡😡😡😡
OOOOOOOOOOOOOOOOOO!

I Give Up


It was a disaster.  blobToText()?  Grr.  At some point while fighting it I was even getting these red herring CORS errors that I can't reproduce now that I spent hours fighting.  All I know is if you see CORS errors don't bother with [EnableCors], just read the logs closely it's probably something else.

That was about six months ago.  It's taken me that long to calm down.  To everyone I've interacted with since, I do apologize for the perpetual yelling.

At the time I solved it by adding a hidden form tag, an ngNoForm, a target="_blank", and a bunch of hidden inputs.  I don't know how I slept at night.

But I was actually pretty close and with persistence found the path to enlightenment.

Less Complaining, More Solution


Ok, ok, I've dragged this on long enough.  On a good googlefu day I stumbled on the solution of telling Swashbuckle to map all instances of FileContentResult with "file" in startup.cs:

services.AddSwaggerGen(options =>
{
    options.MapType(() => new Schema
    {
        Type = "file"
    });

That generates this swagger file:

"/api/ProductFiles/{filename}.xlsx": {
    "post": {
        "tags": ["ProductFiles"],
            "operationId": "ApiProductFilesByFilename}.xlsxPost",
            "consumes": [],
            "produces": ["text/plain", "application/json", "text/json"],
            "parameters": [{
                "name": "fileName",
                "in": "path",
                "required": true,
                "type": "string"
            }],
            "responses": {
            "200": {
                "description": "Success",
                    "schema": {
                    "type": "file"
                }
            }
        }
    }
}

Type: file, yes of course.  Solved problems are always so simple.  Which NSwag turns into this function:
productFiles(fileName: string): Observable<FileResponse> {

Which allows me to write this fancy little thang:
public download() { const fileName = moment().format('YYYY-MM-DD'); this.apiServiceProxy.productFiles(fileName) .subscribe(fileResponse => { const a = document.createElement('a'); a.href = URL.createObjectURL(fileResponse.data); a.download = fileName + '.xlsx'; a.click(); }); }
So pretty, right?!  And it even works!!  

What's even awesomer is if you add additional parameters like 

public ActionResult Download(string fileName, [FromBody]ProductFileParamsDto paramsDto)

Then NSwag generates a ProductFileParamsDto and makes it a parameter.

Fantabulous.  All the code is available in a nice tidy pull request for perusal.

Conclusion


I really think this issue is why the dinosaurs left.  But now hopefully, with some luck, you won't share their fate.