Scaffolding ASP.NET Core 2 with CatFactory

• GitHub Repository

Introduction

What is CatFactory?

CatFactory is a scaffolding engine for .NET Core built with C#.

How does it Works?

The concept behind CatFactory is to import an existing database from SQL Server instance and then to scaffold a target technology.

We can also replace the database from SQL Server instance with an in-memory database.

The flow to import an existing database is:

1. Create Database Factory
2. Import Database
3. Create instance of Project (Entity Framework Core, Dapper, etc)
4. Build Features (One feature per schema)
5. Scaffold objects, these methods read all objects from database and create instances for code builders

Currently, the following technologies are supported:

• Entity Framework Core
• ASP.NET Core
• Dapper

This package is the core for child packages, additional packages have created with this naming convention: CatFactory.PackageName.

• CatFactory.SqlServer
• CatFactory.PostgreSql
• CatFactory.NetCore
• CatFactory.EntityFrameworkCore
• CatFactory.AspNetCore
• CatFactory.Dapper
• CatFactory.TypeScript

Concepts Behind CatFactory

• Database Type Map
• Project Selection
• Event Handlers to Scaffold
• Database Object Model
• Import Bag

Read more on: Concepts behind CatFactory.

Workshop

One of things I don't like about dapper is to have all definitions for queries in strings or string builders... I prefer to have an object that builds queries and in that way reduce code lines quantity but I don't know if that concept breaks Dapper philosophy, I really weant to know about that because scaffold high quality code is fundamental in CatFactory, so I don't want to add an implementation that breaks the main concept behind an ORM...

Anyway I have invested some time to research about how can I solve query building for repositories and I have no found any object that allows to build a query from object's definition, there are frameworks that provide CRUD functions but something like LINQ there isn't, as I know of course if I wrong about this point please let me know in comments.

So I'll provide a draft for query building and take your time to let me know your feedback and then please answer 2 questions:

1. This implementation breaks Dapper concept?
2. What do you think about to have metadata for entities in Dapper?

Query Builder Draft

Select all

var query = QueryBuilder
    .Select<Shipper>();

// Output:
// select [ShipperID], [CompanyName], [Phone] from [dbo].[Shipper]

Select by key

var query = QueryBuilder
    .Select<Shipper>()
    .Where("ShipperID", QueryOperator.Equals, 1);

// Output:
// select [ShipperID], [CompanyName], [Phone] from [dbo].[Shipper] where [ShipperID] = 1

Insert

var query = QueryBuilder
    .Insert<Shipper>(identity: "ShipperID");

// Output:
// insert into [dbo].[Shipper] ([CompanyName], [Phone]) values (@companyName, @phone)
// select @shipperID = @@identity

Update

var query = QueryBuilder
    .Update<Shipper>(key: new string[] { "ShipperID" });

// Output:
// update [dbo].[Shipper] set [CompanyName] = @companyName, [Phone] = @phone where [ShipperID] = @shipperID

Delete

var query = QueryBuilder
    .Delete<Shipper>(key: new string[] { "ShipperID" });

// Output:
// delete from [dbo].[Shipper] where [ShipperID] = @shipperID

Select by

// Search by
var query = QueryBuilder
    .Select<Shipper>()
    .Where("CompanyName", QueryOperator.Like, "%a%")
    .And("Phone", QueryOperator.Like, "%a%");

// Output:
// select [ShipperID], [CompanyName], [Phone] from [Shipper] where [CompanyName] like '%a%' and [Phone] like '%a%'

Shipper is an entity for this example, I have found the following issues with this solution:

• There isn't information for schema (e.g. dbo, Production, Purchasing, Sales)
• There isn't a way to know if one table with name "Order Details" is mapped to entity with Name OrderDetail

The above points can be solve if there is any information for table and entity (C# class), something like metadata, we can have an interface with name IEntity like this:

public interface IEntity
{
	Table ToTable();
}

Then create a class with name Shipper and implement interface:

public class Shipper : IEntity
{
 public int? ShipperID { get; set; }
 
 public string CompanyName { get; set; }
 
 public string Phone { get; set; }
 
 public Table ToTable()
  => new Table
  {
   Schema = "dbo",
   Name = "Shipper",
   Identity = new Identity("ShipperID", 1, 1),
   PrimaryKey = new PrimaryKey("ShipperID")
   Columns = new List<Column>
   {
    new Column
    {
     Name = "ShipperID",
     Type = "int"
    },
    new Column
    {
     Name = "CompanyName",
     Type = "varchar",
     Lenght = 50
    },
    new Column
    {
     Name = "Phone",
     Type = "varchar",
     Length = 25
    }
   }
  };
 }
}

In that way we can have all "metadata" for all entities and get that definitions to build queries in dynamic way, so we can reduce code lines in our repositories.

The definition for Table, Columns, Identity and PrimaryKey already exists in CatFactory, so we can reuse those definitions for this purpose :)

Please let me know what do you think about this implementation, make sense?

According to feedback from developers and to provide a better experience for users, I'm working on some improvements to get a more clean way to work with CatFactory:

Working with database

// Import from existing database
var database = SqlServerDatabaseFactory.Import("YourConnectionStringHere");

// Read all tables
foreach (var table in database.Tables)
{
    // Check primary key on table's definition
    if (table.PrimaryKey == null)
    {
        continue;
    }
    
    // Check identity on table's definition
    if (table.Identity != null)
    {
        var identityName = table.Identity.Name;
    }
    
    // Read all columns
    foreach (var column in table.Columns)
    {
        // Get equivalent CLR type from column type
        var clrType = database.ResolveType(column).GetClrType();
    }
}

Packages

• CatFactory
• CatFactory.SqlServer
• CatFactory.NetCore
• CatFactory.EntityFrameworkCore
• CatFactory.AspNetCore
• CatFactory.Dapper
• CatFactory.TypeScript

You can check the download statistics for CatFactory packages in NuGet Gallery.

Background

Generate code is a common task in software developer, the most of developers write a "code generator" in their lives.

Using Entity Framework 6.x, I worked with EF wizard and it's a great tool even with limitations like:

• Not scaffolding for Fluent API
• Not scaffolding for Repositories
• Not scaffolding for Unit of Work
• Custom scaffolding is so complex or in some cases impossible

With Entity Framework Core I worked with command line to scaffold from existing database, EF Core team provided a great tool with command line but there are still the same limitations above.

So, CatFactory pretends to solve those limitations and provide a simple way to scaffold Entity Framework Core.

StringBuilder it was used to scaffold a class or interface in older versions of CatFactory but some years ago there was a change about how to scaffold a definition (class or interface), CatFactory allows to define the structure for class or interface in a simple and clear way, then use an instance of CodeBuilder to scaffold in C#.

Lets start with scaffold a class in C#:

var definition = new CSharpClassDefinition
{
    Namespace = "OnlineStore.DomainDrivenDesign",
    AccessModifier = AccessModifier.Public,
    Name = "StockItem",
    Properties =
    {
        new PropertyDefinition(AccessModifier.Public, "string", "GivenName")
        {
            IsAutomatic = true
        },
        new PropertyDefinition(AccessModifier.Public, "string", "MiddleName")
        {
            IsAutomatic = true
        },
        new PropertyDefinition(AccessModifier.Public, "string", "Surname")
        {
            IsAutomatic = true
        },
        new PropertyDefinition(AccessModifier.Public, "string", "FullName")
        {
            IsReadOnly = true,
            GetBody =
            {
                new CodeLine(" return GivenName + (string.IsNullOrEmpty(MiddleName) ? \"\" : \" \" + MiddleName) + \" \" + Surname)")
            }
        }
    }
};

CSharpCodeBuilder.CreateFiles("C:\\Temp", string.Empty, true, definition);

This is the output code:

namespace OnlineStore.DomainDrivenDesign
{
	public class StockItem
	{
		public string GivenName { get; set; }

		public string MiddleName { get; set; }

		public string Surname { get; set; }

		public string FullName
			=> GivenName + (string.IsNullOrEmpty(MiddleName) ? "" : " " + MiddleName) + " " + Surname;

	}
}

To create an object definition like class or interface, these types can be use:

• EventDefinition
• FieldDefinition
• ClassConstructorDefinition
• FinalizerDefinition
• IndexerDefinition
• PropertyDefinition
• MethodDefinition

Types like ClassConstructorDefinition, FinalizerDefinition, IndexerDefinition, PropertyDefinition and MethodDefinition can have code blocks, these blocks are arrays of ILine.

ILine interface allows to represent a code line inside of code block, there are different types for lines:

1. CodeLine
2. CommentLine
3. EmptyLine
4. PreprocessorDirectiveLine
5. ReturnLine
6. TodoLine

Lets create a class with methods:

var classDefinition = new CSharpClassDefinition
{
 Namespace = "OnlineStore.BusinessLayer",
 AccessModifier = AccessModifier.Public,
 Name = "WarehouseService",
 Fields =
 {
  new FieldDefinition("OnlineStoreDbContext", "DbContext")
  {
   IsReadOnly = true
  }
 },
 Constructors =
 {
  new ClassConstructorDefinition
  {
   AccessModifier = AccessModifier.Public,
   Parameters =
   {
    new ParameterDefinition("OnlineStoreDbContext", "dbContext")
   },
   Lines =
   {
    new CodeLine("DbContext = dbContext;")
   }
  }
 },
 Methods =
 {
  new MethodDefinition
  {
   AccessModifier = AccessModifier.Public,
   Type = "IListResponse<StockItem>",
   Name = "GetStockItems",
   Lines =
   {
    new TodoLine(" Add filters"),
    new CodeLine("return DbContext.StockItems.ToList();")
   }
  }
 }
};

CSharpCodeBuilder.CreateFiles("C:\\Temp", string.Empty, true, definition);

This is the output code:

namespace OnlineStore.BusinessLayer
{
 public class WarehouseService
 {
  private readonly OnlineStoreDbContext DbContext;

  public WarehouseService(OnlineStoreDbContext dbContext)
  {
   DbContext = dbContext;
  }

  public IListResponse<StockItem> GetStockItems()
  {
   // todo:  Add filters
   return DbContext.StockItems.ToList();
  }
 }
}

Now lets refact an interface from class:

var interfaceDefinition = classDefinition.RefactInterface();

CSharpCodeBuilder.CreateFiles(@"C:\Temp", string.Empty, true, interfaceDefinition);

This is the output code:

public interface IWarehouseService
{
	IListResponse<StockItem> GetStockItems();
}

I know some developers can reject this design alleging there is a lot of code to scaffold a simple class with 4 properties but keep in mind CatFactory's way looks like a "clear" transcription of definitions.

CatFactory.NetCore uses the model from CatFactory to allow scaffold C# code, so the question is: What is CatFactory.Dapper package?

Is a package that allows to scaffold Dapper using scaffolding engine provided by CatFactory.

Prerequisites

Skills

• OOP
• AOP
• ORM
• C#
• Design Patterns (Repository and Unit of Work)

Software Prerequisites

• .NET Core
• Visual Studio 2017 or VS Code
• Access to existing SQL Server instance

Using the code

Please follow these steps to scaffold ASP.NET Core and Entity Framework Core with CatFactory:

Step 01 - Create sample database

Take a look for sample database to understand each component in architecture. In this database there are 4 schemas: Dbo, HumanResources, Warehouse and Sales.

Each schema represents a division on store company, keep this in mind because all code is designed following this aspect; at this moment this code only implements features for Warehouse and Sales schemas.

All tables have a primary key with one column and have columns for creation, last update and concurrency token.

Schema	Name
dbo	ChangeLog
dbo	ChangeLogExclusion
dbo	Country
dbo	CountryCurrency
dbo	Currency
dbo	EventLog
HumanResources	Employee
HumanResources	EmployeeAddress
HumanResources	EmployeeEmail
Sales	Customer
Sales	OrderDetail
Sales	OrderHeader
Sales	OrderStatus
Sales	PaymentMethod
Sales	Shipper
Warehouse	Location
Warehouse	Product
Warehouse	ProductCategory
Warehouse	ProductInventory

You can found the scripts for database in this link: OnLine Store Database Scripts on GitHub.

Please remember: This is a sample database, only for demonstration of concepts.

Step 02 - Create Project

Create a console application for .NET Core, in some cases you can add one project to your existing solution but with some name or sufix that indicates it's a project to scaffold, for example: OnLineStore.CatFactory.EntityFrameworkCore.

Add the following packages for your project:

Name	Version	Description
CatFactory.SqlServer	1.0.0-beta-sun-build50	Allos import databases from SQL Server instances
CatFactory.AspNetCore	1.0.0-beta-sun-build48	Scaffolding for ASP.NET Core

Save all changes and build the project.

Step 03 - Add Code To Scaffold

Once we have the packages installed on our project, we can add code in Main method:

// Import database
var database = SqlServerDatabaseFactory
 .Import(SqlServerDatabaseFactory.GetLogger(), "server=(local);database=OnLineStore;integrated security=yes;", "dbo.sysdiagrams");

// Create instance of Entity Framework Core Project
var entityFrameworkProject = new EntityFrameworkCoreProject
{
 Name = "OnLineStore.Core",
 Database = database,
 OutputDirectory = "C:\\Temp\\CatFactory.AspNetCore\\OnLineStore.Core"
};

// Apply settings for project
entityFrameworkProject.GlobalSelection(settings =>
{
 settings.ForceOverwrite = true;
 settings.ConcurrencyToken = "Timestamp";
 settings.AuditEntity = new AuditEntity
 {
  CreationUserColumnName = "CreationUser",
  CreationDateTimeColumnName = "CreationDateTime",
  LastUpdateUserColumnName = "LastUpdateUser",
  LastUpdateDateTimeColumnName = "LastUpdateDateTime"
 };
});

entityFrameworkProject.Selection("Sales.OrderHeader", settings => settings.EntitiesWithDataContracts = true);

// Build features for project, group all entities by schema into a feature
entityFrameworkProject.BuildFeatures();

// Scaffolding =^^=
entityFrameworkProject
 .ScaffoldEntityLayer()
 .ScaffoldDataLayer();

var aspNetCoreProject = entityFrameworkProject
 .CreateAspNetCoreProject("OnLineStore.WebApi", "C:\\Temp\\CatFactory.AspNetCore\\OnLineStore.WebApi", entityFrameworkProject.Database);

// Add event handlers to before and after of scaffold

aspNetCoreProject.ScaffoldingDefinition += (source, args) =>
{
 // Add code to perform operations with code builder instance before to create code file
};

aspNetCoreProject.ScaffoldedDefinition += (source, args) =>
{
 // Add code to perform operations after of create code file
};

aspNetCoreProject.ScaffoldAspNetCore();

Step 04 - Create Web API Project

Code Review

We'll take a look at the scaffold code:

SalesController class:

using System;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;
using OnlineStore.Core.DataLayer.Contracts;
using OnlineStore.Core.DataLayer.Repositories;
using OnlineStore.WebAPI.Responses;
using OnlineStore.WebAPI.Requests;
using OnlineStore.Core.EntityLayer.Sales;
using OnlineStore.Core.DataLayer.DataContracts;

namespace OnlineStore.WebAPI.Controllers
{
 [Route("api/[controller]")]
 [ApiController]
 public class SalesController : ControllerBase
 {
  protected readonly ISalesRepository Repository;
  protected ILogger Logger;

  public SalesController(ISalesRepository repository, ILogger<SalesController> logger)
  {
   Repository = repository;
   Logger = logger;
  }

  // Another methods

  [HttpGet("OrderHeader")]
  public async Task<IActionResult> GetOrderHeadersAsync([FromQuery]GetOrderHeadersRequest request)
  {
   Logger?.LogDebug("'{0}' has been invoked", nameof(GetOrderHeadersAsync));
   
   var response = new PagedResponse<OrderHeaderDto>();
   
   try
   {
    // Get query from repository
    var query = Repository.GetOrderHeaders(request.CurrencyID, request.CustomerID, request.EmployeeID, request.OrderStatusID, request.PaymentMethodID, request.ShipperID);
   
    // Set paging's information
    response.PageSize = (int)request.PageSize;
    response.PageNumber = (int)request.PageNumber;
    response.ItemsCount = await query.CountAsync();
   
    // Retrieve items by page size and page number, set model for response
    response.Model = await query.Paging(response.PageSize, response.PageNumber).ToListAsync();
   
    Logger?.LogInformation("Page {0} of {1}, Total of rows: {2}.", response.PageNumber, response.PageCount, response.ItemsCount);
   }
   catch (Exception ex)
   {
    response.SetError(Logger, nameof(GetOrderHeadersAsync), ex);
   }
   
   return response.ToHttpResponse();
  }

  [HttpGet("OrderHeader/{id}")]
  public async Task<IActionResult> GetOrderHeaderAsync(long? id)
  {
   Logger?.LogDebug("'{0}' has been invoked", nameof(GetOrderHeaderAsync));
   
   var response = new SingleResponse<OrderHeader>();
   
   try
   {
    // Retrieve entity by id
    var entity = await Repository.GetOrderHeaderAsync(new OrderHeader(id));
   
    if (entity == null)
     return NotFound();
   
    response.Model = entity;
   
    Logger?.LogInformation("The entity was retrieved successfully");
   }
   catch (Exception ex)
   {
    response.SetError(Logger, nameof(GetOrderHeaderAsync), ex);
   }
   
   return response.ToHttpResponse();
  }

  [HttpPost("OrderHeader")]
  public async Task<IActionResult> PostOrderHeaderAsync([FromBody]PostOrderHeaderRequest request)
  {
   Logger?.LogDebug("'{0}' has been invoked", nameof(PostOrderHeaderAsync));
   
   // Validate request model
   if (!ModelState.IsValid)
    return BadRequest(request);
   
   var response = new SingleResponse<OrderHeader>();
   
   try
   {
    var entity = request.ToEntity();
   
    // Add entity to database
    Repository.Add(entity);
   
    await Repository.CommitChangesAsync();
   
    response.Model = entity;
   
    Logger?.LogInformation("The entity was created successfully");
   }
   catch (Exception ex)
   {
    response.SetError(Logger, nameof(PostOrderHeaderAsync), ex);
   }
   
   return response.ToHttpResponse();
  }

  [HttpPut("OrderHeader/{id}")]
  public async Task<IActionResult> PutOrderHeaderAsync(long? id, [FromBody]PutOrderHeaderRequest request)
  {
   Logger?.LogDebug("'{0}' has been invoked", nameof(PutOrderHeaderAsync));
   
   // Validate request model
   if (!ModelState.IsValid)
    return BadRequest(request);
   
   var response = new Response();
   
   try
   {
    // Retrieve entity by id
    var entity = await Repository.GetOrderHeaderAsync(new OrderHeader(id));
   
    if (entity == null)
     return NotFound();
   
    // todo:  Check properties to update
   
    // Apply changes on entity
    entity.OrderStatusID = request.OrderStatusID;
    entity.CustomerID = request.CustomerID;
    entity.EmployeeID = request.EmployeeID;
    entity.ShipperID = request.ShipperID;
    entity.OrderDate = request.OrderDate;
    entity.Total = request.Total;
    entity.CurrencyID = request.CurrencyID;
    entity.PaymentMethodID = request.PaymentMethodID;
    entity.DetailsCount = request.DetailsCount;
    entity.ReferenceOrderID = request.ReferenceOrderID;
    entity.Comments = request.Comments;
   
    // Save changes for entity in database
    Repository.Update(entity);
   
    await Repository.CommitChangesAsync();
   
    Logger?.LogInformation("The entity was updated successfully");
   }
   catch (Exception ex)
   {
    response.SetError(Logger, nameof(PutOrderHeaderAsync), ex);
   }
   
   return response.ToHttpResponse();
  }

  [HttpDelete("OrderHeader/{id}")]
  public async Task<IActionResult> DeleteOrderHeaderAsync(long? id)
  {
   Logger?.LogDebug("'{0}' has been invoked", nameof(DeleteOrderHeaderAsync));
   
   var response = new Response();
   
   try
   {
    // Retrieve entity by id
    var entity = await Repository.GetOrderHeaderAsync(new OrderHeader(id));
   
    if (entity == null)
     return NotFound();
   
    // Remove entity from database
    Repository.Remove(entity);
   
    await Repository.CommitChangesAsync();
   
    Logger?.LogInformation("The entity was deleted successfully");
   }
   catch (Exception ex)
   {
    response.SetError(Logger, nameof(DeleteOrderHeaderAsync), ex);
   }
   
   return response.ToHttpResponse();
  }

  // Another methods
 }
}

Let's take a look on GetOrderHeadersRequest class:

using System;

namespace OnlineStore.WebAPI.Requests
{
 public class GetOrderHeadersRequest
 {
  public GetOrderHeadersRequest()
  {
   PageSize = 10;
   PageNumber = 1;
  }

  public int? PageSize { get; set; }

  public int? PageNumber { get; set; }

  public string CurrencyID { get; set; }

  public int? CustomerID { get; set; }

  public int? EmployeeID { get; set; }

  public short? OrderStatusID { get; set; }

  public Guid? PaymentMethodID { get; set; }

  public int? ShipperID { get; set; }

 }
}

The above class contains all paremeteres that GetOrderHeadersAsync method allows, with this design it's more clear for developers instead of have a method with a lot of parameters.

There is a controller per feature, the sample database contains the following schemas: dbo, Production, HumanResources and Sales.

All actions in controllers invoke repository's methods.

This version requires Entity Framework Core, development team is working to allow other ORM such as Dapper.

Setting Up CatFactory for ASP.NET Core

Additionally, there are more settings for Entity Framework Core project instance, we'll take a look on those settings:

Name	Description	Default Value
ProjectName	Sets the name for project
OutputDirectory	Sets the output directory for project
Database	Imported database

Points of Interest

• CatFactory doesn't have command line for nuget because from my point of view, it will be a big trouble to allow set values for all settings because we have a lot of settings for EntityFrameworkCoreProjectSettings, I think at this moment it is simpler to create a console project to generate the code and then developer moves generated files for existing project and makes a code refactor if applies
• CatFactory doesn't have UI now because at the beginning of this project, .NET Core had no standard UI, but we're working on UI for CatFactory, maybe we'll choose Angular =^^=
• Now we are focused on Entity Framework Core, Dapper and Web API
• We're working on continuous updates to provide better help for users

Related Links

• Entity Framework Core 2 for Enterprise

Code Improvements

• Add author's information for output files
• Scaffold unit tests
• Scaffold integration tests
• Add integration for Dapper

Bugs?

If you get any exception with CatFactory packages, please use these links:

• Issue tracker for CatFactory.SqlServer in GitHub
• Issue tracker for CatFactory.AspNetCore in GitHub

I'll appreciate your feedback to improve CatFactory.

Source picture for "CatFactory" concept =^^=

History

• 12^th February, 2018: Initial version
• 26^th February, 2018: Update on scaffold code sample
• 2^nd May, 2018: Update for beta version
• 16^th May, 2018: Update for EF Core 2 and addition of version for Api
• 8^th November, 2018: Refactor for article
• 24^th December, 2018: Refactor for database section
• 19^th May, 2019: Expanding Scaffolding Engine concept
• 12^th April, 2020: Add Get Request model