A JSON to JSON Template Engine

I came across a feature request, where it was needed to have a command line tool that is able to send variours JSON messages over the wire. Think of a JSON structure that represents the message template and that must be filled with different data values every time you want to send a message. Because the message template and the data are passed as JSON strings to the command line tool, all of this is not strongly typed but rather JSON strings.

So I thought of having a JSON structure that is a template having placeholders. These placeholders need to be filled with concrete values from a second JSON object. I thouht of a format for the placeholders and decided that it would be best if the placeholders are defined as valid JSON as well, so I can parse them easily.

Think of the following example:

{
    "Salution": <Fill from data field Contact.Salutation, if this is not present leave it null>,
    "FirstName": <Fill from data field Contact.FirstName>,
    "LastName": <Fill from data field Contact.LastName>,
    "Sex": <Fill from data filed Contact.Sex, if this is not present fill with value 'No information'>,
    "IsConfirmed": <Fill from data field IsConfirmed>
    "MessageType":"NewContactAdded",
    "Metadata" :
    {
        "CreatedOn": <Generate DateTime.Now>,
        "CreatedBy": <Fill from data field User>,
        "CorrelationId": <Generate Guid.NewGuid()>
    }
}

As a template this looks like this:

{
    "Salution":{ "Path":"Contact.Salutation", "Optional":"true" },
    "FirstName": { "Path":"Contact.FirstName" },
    "LastName": { "Path":"Contact.LastName" },
    "Sex": { "Path":"Contact.Sex", "Or":{ "Expression":"No information" } },
    "IsConfirmed": { "Path":"IsConfirmed" }
    "MessageType":"NewContactAdded",
    "Metadata" :
    {
        "CreatedOn": { "Expression":"DateTime.Now" },
        "CreatedBy": { "Path":"User" },
        "CorrelationId": { "Expression":"Guid.NewGuid()" }
    }
}

Given the following data

{
    "User":"trichling",
    "IsConfirmed":"true",
    "Contact":
    {
        "FirstName":"Peter",
        "LastName":"Pingel"
    }
}

will expand to

{
  "Salution": null,
  "FirstName": "Peter",
  "LastName": "Pingel",
  "Sex": "No information",
  "IsConfirmed": "true",
  "MessageType": "NewContactAdded",
  "Metadata": {
    "CreatedOn": "2016-05-06T12:17:17.9187797+02:00",
    "CreatedBy": "trichling",
    "CorrelationId": "2c59a1ee-be98-43b8-a046-d9ecb20a33aa"
  }
}

The Salutation was omitted in the data, but it is marked as optional, so a missing value will be ignored. First- and LastName are filled from a nested object in the data structure. The property Sex is also omitted, hence the default expression defined in the Or-part of the placeholder is used. The MessageType is a constant string, so it will be added as is to the output. The Metadata complex property is made up of a timestamp which is generated via an expression placeholder, as well as the CorrelationId.

Is this even useful? Can anyone think of different / broader use cases for this? Does anyone know a solution to the problem that already exists? I have searched the web but i could not find anything. That made me wonder: am I really the first person who thought about this (which I consider very unlikely), or does the whole use case make no sense?

I implemented a working solution that at least served my needs. Might anyone else feel this is useful? Please enlight me :)

If someone is interested, I am happy to share the source code - if not, I will hide in shame :)

Manage Views with EF Code First + Migration Support

I had to search around for a while to find a solution to the problem of adding a view to an Entity Framework Code First Context. I wanted my solution to work with a view that does not yet exist. The view must be created (and removed if necessary) by a migration. I am fine with the fact that the view has the same name as the property in the context.

You can find an explanation about how to use pre-existing views in a context, as well as ideas on how to add a view via migrations. What was missing for me was a complete example that combines both aspects. So here is mine :-)

We start with a simple User class,

public class User
{
    public Guid Id { get; set; }
    public string Login { get; set; }
    public bool IsAdmin { get; set; }
}

and add this to a very simple context

public class ViewContext : DbContext
{

    public ViewContext()
        : base("ViewContext")
    {

    }
    
    public DbSet<User> Users { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<User>()
            .HasKey(u => u.Id)
            .Property(u => u.Id)
                .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);

        base.OnModelCreating(modelBuilder);
    }
}

Using the Package Manager Console we enable migrations and create an initial migration and apply it to the database:

enable-migrations
add-migration initial
update-database

Now I want to create two views, one that filters on admin users and the other that filters on normal users.

The first thing to to is, to create a new class that reflects the view to be created, adding a DbSet using that class to the context and create a migration for it.

public class AdminUser
{
    public Guid Id { get; set; }
    public string Login { get; set; }
}

public class ViewContext : DbContext
{
    public DbSet<AdminUser> AdminUsers { get; set; }
}

add-migration viewadminusers

The migration created after this change contains a CreateTable-Statement. This is not the desired thing to happen, so this has to be changed. It is possible to use the DbMigrations Sql-Method for this an pass in the SQL as a string. This is an easy solution, but it did not satisfy me. I wanted a solution that looks more like native migrations, like so:

public partial class viewadminusers : DbMigration
{
    public override void Up()
    {
        CreateView("dbo.AdminUsers", "SELECT * FROM Users WHERE IsAdmin = 1");
    }
    
    public override void Down()
    {
        DropView("dbo.AdminUsers");
    }
}

So I digged a little deeper, and found an approach to add new operations to DbMigrations. The example shows how to add check constraints, and it works all the same for views.

First of all we need a POCO class to describe our operation:

public class CreateViewOperation : MigrationOperation
{

    public CreateViewOperation(string name, string sql)
        : base(null)
    {
        this.Name = name;
        this.Sql = sql;
    }

    public string Name { get; set; }
    public string Sql { get; set; }

    public override bool IsDestructiveChange
    {
        get
        {
            return false;
        }
    }
}

The class must derive from MigrationOperation and holds properties for the name and the SQL-Body of the view. To be able to use this Operation in a Migration, we can use extension methods on DbMigration:

public static class CreateViewExtension
{
    public static void CreateView(this DbMigration migration, string name, string sql)
    {
        var createViewOperation = new CreateViewOperation(name, sql);
        ((IDbMigration)migration).AddOperation(createViewOperation);
    }

    public static void DropView(this DbMigration migration, string name)
    {
        var dropViewOperation = new DropViewOperation(name);
        ((IDbMigration)migration).AddOperation(dropViewOperation);
    }
}

With this code in place we can write the migration almost like stated in the above example, the only difference is that one must use the this-prefix in front of the method name which is close enough.

this.CreateView("dbo.AdminUsers", "SELECT * FROM Users WHERE IsAdmin = 1");

The thing that acutally translate the operation to SQL is a subclass of SqlServerMigrationSqlGenerator:

public class CustomSqlServerMigrationGenerator : SqlServerMigrationSqlGenerator
{
    protected override void Generate(MigrationOperation migrationOperation)
    {
        if (migrationOperation is CreateViewOperation)
        { 
            Generate(migrationOperation as CreateViewOperation);
            return;
        }

        base.Generate(migrationOperation);
    }

    protected virtual void Generate(CreateViewOperation createViewOperation)
    {
        using (var writer = Writer())
        {
            writer.WriteLine(
                "EXEC ('CREATE View {0} AS {1}')",
                Name(createViewOperation.Name),
                createViewOperation.Sql
            );
            Statement(writer);
        }
    }
}

The override of the general Generate(MigrationOperation) method checks if the operation is of type CreateViewOperation and hands over to the specialized Generate(CreateViewOperation) method. This method generates the SQL code using a special TextWriter and adds the resulting SQL using the Statement method.

Almost done! The only piece left is to add this SQL-Generator to the migrations configuration.

internal sealed class Configuration : DbMigrationsConfiguration<EFCodeFirstViews.ViewContext>
{
    public Configuration()
    {
        AutomaticMigrationsEnabled = false;
        SetSqlGenerator("System.Data.SqlClient", new CustomSqlServerMigrationGenerator());
    }
}

The SetSqlGenerator method of the DbMigrationsConfigurationType lets you add a SQL generator per provider type.

And this is it. The only thing that has to be done manually is to change the generated migrations file to use the CreateView method instead of create table.

Happy viewing!