(untagged)

Scaffolding Entity Framework Core 2 with CatFactory

HHerzl

0.00/5 (No votes)

30 Aug 2020

Scaffolding Entity Framework Core 2 with CatFactory

In this article, I'll provide a draft for query building, show the steps to scaffold Entity Framework Core with CatFactory, and do a code review where we'll review the output code for one entity to understand the design.

Introduction

How Does It Work?

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:

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

Currently, the following technologies are supported:

This package is the core for child packages, additional packages have been 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 the 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 want 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 not 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 am wrong about this point, please let me know in the comments below.

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

Does this implementation break Dapper concept?
What do you think about having 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 solved 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",
     Length = 50
    },
    new Column
    {
     Name = "Phone",
     Type = "varchar",
     Length = 25
    }
   }
  };
 }
}

In that way, we can have all "metadata" for all entities and get that definition to build queries in a 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 you think about this implementation, makes sense?

According to feedback from developers and to provide a better experience for users, I'm working on some improvements to get a cleaner 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.PostgreSql
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, most 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 the 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 provides a simple way to scaffold Entity Framework Core.

StringBuilder 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#.

Let's 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 used:

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:

CodeLine
CommentLine
EmptyLine
PreprocessorDirectiveLine
ReturnLine
TodoLine

Let's 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 let's 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?

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

Prerequisites

Skills

OOP
AOP
ORM
C#
Domain Driven Design

Software

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

Using the Code

Please follow these steps to scaffold Entity Framework Core with CatFactory:

Step 01 - Create Sample Database

Take a look at the sample database to understand each component in the architecture. In this database, there are four 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 Production and Sales schemas.

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

Tables

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 find 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 suffix 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-build58	Provides import feature for SQL Server databases
`CatFactory.EntityFrameworkCore`	1.0.0-beta-sun-build50	Provides scaffold for Entity Framework Core

Save all changes and build the project.

Step 03 - Add Code to Scaffold

With the installed packages on project, add the following code in Main method:

// Create database factory
var databaseFactory = new SqlServerDatabaseFactory
{
    DatabaseImportSettings = new DatabaseImportSettings
    {
        ConnectionString = "server=(local);database=OnlineStore;integrated security=yes;",
        Exclusions =
        {
            "dbo.sysdiagrams"
        }
    }
};

// Import database
var database = await databaseFactory.ImportAsync();

// Create instance of Entity Framework Core project
var project = EntityFrameworkCoreProject
    .CreateForV2x("OnlineStore.Domain", database, @"C:\Projects\OnlineStore.Domain");

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

    settings.AddConfigurationForUniquesInFluentAPI = true;
    settings.AddConfigurationForForeignKeysInFluentAPI = true;
    settings.DeclareNavigationProperties = true;
});

project.Selection("Sales.OrderHeader", settings =>
{
    settings.EntitiesWithDataContracts = true;
    settings.AddConfigurationForForeignKeysInFluentAPI = true;
    settings.DeclareNavigationProperties = true;
});

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

// Scaffolding =^^=
project
    .ScaffoldDomain();

Extension methods for project instance
Name	Description
`ScaffoldDomain`	Scaffold code using Domain Driven Design

Code Review

We'll review the output code for one entity to understand the design:

Code for OrderHeader class:

using System;
using OnlineStore.Domain.Models;
using OnlineStore.Domain.Models.HumanResources;
using System.Collections.ObjectModel;

namespace OnlineStore.Domain.Models.Sales
{
 public partial class OrderHeader : IAuditEntity
 {
  public OrderHeader()
  {
  }

  public OrderHeader(long? id)
  {
   Id = id;
  }

  public long? Id { get; set; }

  public short? OrderStatusID { get; set; }

  public int? CustomerID { get; set; }

  public int? EmployeeID { get; set; }

  public int? ShipperID { get; set; }

  public DateTime? OrderDate { get; set; }

  public decimal? Total { get; set; }

  public string CurrencyID { get; set; }

  public Guid? PaymentMethodID { get; set; }

  public int? DetailsCount { get; set; }

  public long? ReferenceOrderID { get; set; }

  public string Comments { get; set; }

  public string CreationUser { get; set; }

  public DateTime? CreationDateTime { get; set; }

  public string LastUpdateUser { get; set; }

  public DateTime? LastUpdateDateTime { get; set; }

  public byte[] Timestamp { get; set; }

  public Currency CurrencyFk { get; set; }

  public Customer CustomerFk { get; set; }

  public Employee EmployeeFk { get; set; }

  public OrderStatus OrderStatusFk { get; set; }

  public PaymentMethod PaymentMethodFk { get; set; }

  public Shipper ShipperFk { get; set; }

  public Collection<OrderDetail> OrderDetailList { get; set; }

 }
}

Code for OnlineStoreDbContext class:

using System;
using Microsoft.EntityFrameworkCore;
using OnlineStore.Domain.Models;
using OnlineStore.Domain.Configurations;
using OnlineStore.Domain.Models.HumanResources;
using OnlineStore.Domain.Models.Sales;
using OnlineStore.Domain.Models.Warehouse;
using OnlineStore.Domain.Configurations.HumanResources;
using OnlineStore.Domain.Configurations.Sales;
using OnlineStore.Domain.Configurations.Warehouse;

namespace OnlineStore.Domain
{
 public class OnlineStoreDbContext : DbContext
 {
  public OnlineStoreDbContext(DbContextOptions<OnlineStoreDbContext> options)
   : base(options)
  {
  }

  public DbSet<ChangeLog> ChangeLog { get; set; }

  public DbSet<ChangeLogExclusion> ChangeLogExclusion { get; set; }

  public DbSet<Country> Country { get; set; }

  public DbSet<CountryCurrency> CountryCurrency { get; set; }

  public DbSet<Currency> Currency { get; set; }

  public DbSet<EventLog> EventLog { get; set; }

  public DbSet<Employee> Employee { get; set; }

  public DbSet<EmployeeAddress> EmployeeAddress { get; set; }

  public DbSet<EmployeeEmail> EmployeeEmail { get; set; }

  public DbSet<Customer> Customer { get; set; }

  public DbSet<CustomerAddress> CustomerAddress { get; set; }

  public DbSet<CustomerEmail> CustomerEmail { get; set; }

  public DbSet<OrderDetail> OrderDetail { get; set; }

  public DbSet<OrderHeader> OrderHeader { get; set; }

  public DbSet<OrderStatus> OrderStatus { get; set; }

  public DbSet<PaymentMethod> PaymentMethod { get; set; }

  public DbSet<Shipper> Shipper { get; set; }

  public DbSet<Location> Location { get; set; }

  public DbSet<Product> Product { get; set; }

  public DbSet<ProductCategory> ProductCategory { get; set; }

  public DbSet<ProductInventory> ProductInventory { get; set; }

  public DbSet<ProductUnitPriceHistory> ProductUnitPriceHistory { get; set; }

  public DbSet<EmployeeInfo> EmployeeInfo { get; set; }

  public DbSet<OrderSummary> OrderSummary { get; set; }

  protected override void OnModelCreating(ModelBuilder modelBuilder)
  {
   // Apply all configurations for tables
   
   // Schema 'dbo'
   modelBuilder
    .ApplyConfiguration(new ChangeLogConfiguration())
    .ApplyConfiguration(new ChangeLogExclusionConfiguration())
    .ApplyConfiguration(new CountryConfiguration())
    .ApplyConfiguration(new CountryCurrencyConfiguration())
    .ApplyConfiguration(new CurrencyConfiguration())
    .ApplyConfiguration(new EventLogConfiguration())
   ;
   
   // Schema 'HumanResources'
   modelBuilder
    .ApplyConfiguration(new EmployeeConfiguration())
    .ApplyConfiguration(new EmployeeAddressConfiguration())
    .ApplyConfiguration(new EmployeeEmailConfiguration())
   ;
   
   // Schema 'Sales'
   modelBuilder
    .ApplyConfiguration(new CustomerConfiguration())
    .ApplyConfiguration(new CustomerAddressConfiguration())
    .ApplyConfiguration(new CustomerEmailConfiguration())
    .ApplyConfiguration(new OrderDetailConfiguration())
    .ApplyConfiguration(new OrderHeaderConfiguration())
    .ApplyConfiguration(new OrderStatusConfiguration())
    .ApplyConfiguration(new PaymentMethodConfiguration())
    .ApplyConfiguration(new ShipperConfiguration())
   ;
   
   // Schema 'Warehouse'
   modelBuilder
    .ApplyConfiguration(new LocationConfiguration())
    .ApplyConfiguration(new ProductConfiguration())
    .ApplyConfiguration(new ProductCategoryConfiguration())
    .ApplyConfiguration(new ProductInventoryConfiguration())
    .ApplyConfiguration(new ProductUnitPriceHistoryConfiguration())
   ;
   
   // Apply all configurations for views
   
   // Schema 'HumanResources'
   modelBuilder
    .ApplyConfiguration(new EmployeeInfoConfiguration())
   ;
   
   // Schema 'Sales'
   modelBuilder
    .ApplyConfiguration(new OrderSummaryConfiguration())
   ;
   
   base.OnModelCreating(modelBuilder);
  }
 }
}

Code for OrderHeaderConfiguration class:

using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata.Builders;
using OnlineStore.Domain.Models.Sales;

namespace OnlineStore.Domain.Configurations.Sales
{
 internal class OrderHeaderConfiguration : IEntityTypeConfiguration<OrderHeader>
 {
  public void Configure(EntityTypeBuilder<OrderHeader> builder)
  {
   // Set configuration for entity
   builder.ToTable("OrderHeader", "Sales");
   
   // Set key for entity
   builder.HasKey(p => p.Id);
   
   // Set identity for entity (auto increment)
   builder.Property(p => p.Id).UseSqlServerIdentityColumn();
   
   // Set configuration for columns
   builder
    .Property(p => p.Id)
    .HasColumnName("ID")
    .HasColumnType("bigint")
    .IsRequired()
    ;
   
   builder
    .Property(p => p.OrderStatusID)
    .HasColumnType("smallint")
    .IsRequired()
    ;
   
   builder
    .Property(p => p.CustomerID)
    .HasColumnType("int")
    .IsRequired()
    ;
   
   builder
    .Property(p => p.EmployeeID)
    .HasColumnType("int")
    ;
   
   builder
    .Property(p => p.ShipperID)
    .HasColumnType("int")
    ;
   
   builder
    .Property(p => p.OrderDate)
    .HasColumnType("datetime")
    .IsRequired()
    ;
   
   builder
    .Property(p => p.Total)
    .HasColumnType("decimal(12, 4)")
    .IsRequired()
    ;
   
   builder
    .Property(p => p.CurrencyID)
    .HasColumnType("varchar")
    .HasMaxLength(10)
    ;
   
   builder
    .Property(p => p.PaymentMethodID)
    .HasColumnType("uniqueidentifier")
    ;
   
   builder
    .Property(p => p.DetailsCount)
    .HasColumnType("int")
    .IsRequired()
    ;
   
   builder
    .Property(p => p.ReferenceOrderID)
    .HasColumnType("bigint")
    ;
   
   builder
    .Property(p => p.Comments)
    .HasColumnType("varchar(max)")
    ;
   
   builder
    .Property(p => p.CreationUser)
    .HasColumnType("varchar")
    .HasMaxLength(25)
    .IsRequired()
    ;
   
   builder
    .Property(p => p.CreationDateTime)
    .HasColumnType("datetime")
    .IsRequired()
    ;
   
   builder
    .Property(p => p.LastUpdateUser)
    .HasColumnType("varchar")
    .HasMaxLength(25)
    ;
   
   builder
    .Property(p => p.LastUpdateDateTime)
    .HasColumnType("datetime")
    ;
   
   builder
    .Property(p => p.Timestamp)
    .HasColumnType("timestamp(8)")
    ;
   
   // Set concurrency token for entity
   builder
    .Property(p => p.Timestamp)
    .ValueGeneratedOnAddOrUpdate()
    .IsConcurrencyToken();
   
   // Add configuration for foreign keys
   
   builder
    .HasOne(p => p.CurrencyFk)
    .WithMany(b => b.OrderHeaderList)
    .HasForeignKey(p => p.CurrencyID)
    .HasConstraintName("FK_Sales_OrderHeader_Currency");
   
   builder
    .HasOne(p => p.CustomerFk)
    .WithMany(b => b.OrderHeaderList)
    .HasForeignKey(p => p.CustomerID)
    .HasConstraintName("FK_Sales_OrderHeader_Customer");
   
   builder
    .HasOne(p => p.EmployeeFk)
    .WithMany(b => b.OrderHeaderList)
    .HasForeignKey(p => p.EmployeeID)
    .HasConstraintName("FK_Sales_OrderHeader_Employee");
   
   builder
    .HasOne(p => p.OrderStatusFk)
    .WithMany(b => b.OrderHeaderList)
    .HasForeignKey(p => p.OrderStatusID)
    .HasConstraintName("FK_Sales_OrderHeader_OrderStatus");
   
   builder
    .HasOne(p => p.PaymentMethodFk)
    .WithMany(b => b.OrderHeaderList)
    .HasForeignKey(p => p.PaymentMethodID)
    .HasConstraintName("FK_Sales_OrderHeader_PaymentMethod");
   
   builder
    .HasOne(p => p.ShipperFk)
    .WithMany(b => b.OrderHeaderList)
    .HasForeignKey(p => p.ShipperID)
    .HasConstraintName("FK_Sales_OrderHeader_Shipper");
   
  }
 }
}

Code for OnlineStoreDbContextSalesQueryExtensions class:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.EntityFrameworkCore;
using OnlineStore.Domain.Models;
using OnlineStore.Domain.Models.Sales;
using OnlineStore.Domain.QueryModels;
using OnlineStore.Domain.Models.HumanResources;

namespace OnlineStore.Domain
{
 public static class OnlineStoreDbContextSalesQueryExtensions
 {
  public static IQueryable<Customer> GetCustomers(this OnlineStoreDbContext dbContext)
  {
   // Get query from DbSet
   var query = dbContext.Customer.AsQueryable();
   
   return query;
  }

  public static async Task<Customer> GetCustomerAsync
         (this OnlineStoreDbContext dbContext, Customer entity)
  {
   return await dbContext.Customer.FirstOrDefaultAsync(item => item.Id == entity.Id);
  }

  public static IQueryable<CustomerAddress> GetCustomerAddresses
         (this OnlineStoreDbContext dbContext, int? customerID = null)
  {
   // Get query from DbSet
   var query = dbContext.CustomerAddress.AsQueryable();
   
   // Filter by: 'CustomerID'
   if (customerID.HasValue)
    query = query.Where(item => item.CustomerID == customerID);
   
   return query;
  }

  public static async Task<CustomerAddress> GetCustomerAddressAsync
         (this OnlineStoreDbContext dbContext, CustomerAddress entity)
  {
   return await dbContext.CustomerAddress.FirstOrDefaultAsync(item => item.Id == entity.Id);
  }

  public static IQueryable<CustomerEmail> GetCustomerEmails
         (this OnlineStoreDbContext dbContext, int? customerID = null)
  {
   // Get query from DbSet
   var query = dbContext.CustomerEmail.AsQueryable();
   
   // Filter by: 'CustomerID'
   if (customerID.HasValue)
    query = query.Where(item => item.CustomerID == customerID);
   
   return query;
  }

  public static async Task<CustomerEmail> GetCustomerEmailAsync
         (this OnlineStoreDbContext dbContext, CustomerEmail entity)
  {
   return await dbContext.CustomerEmail.FirstOrDefaultAsync(item => item.Id == entity.Id);
  }

  public static IQueryable<OrderDetail> GetOrderDetails
  (this OnlineStoreDbContext dbContext, long? orderHeaderID = null, int? productID = null)
  {
   // Get query from DbSet
   var query = dbContext.OrderDetail.AsQueryable();
   
   // Filter by: 'OrderHeaderID'
   if (orderHeaderID.HasValue)
    query = query.Where(item => item.OrderHeaderID == orderHeaderID);
   
   // Filter by: 'ProductID'
   if (productID.HasValue)
    query = query.Where(item => item.ProductID == productID);
   
   return query;
  }

  public static async Task<OrderDetail> GetOrderDetailAsync
         (this OnlineStoreDbContext dbContext, OrderDetail entity)
  {
   return await dbContext.OrderDetail.FirstOrDefaultAsync(item => item.Id == entity.Id);
  }

  public static async Task<OrderDetail> GetOrderDetailByOrderHeaderIDAndProductIDAsync
         (this OnlineStoreDbContext dbContext, OrderDetail entity)
  {
   return await dbContext.OrderDetail.FirstOrDefaultAsync
          (item => item.OrderHeaderID == entity.OrderHeaderID && 
           item.ProductID == entity.ProductID);
  }

  public static IQueryable<OrderHeaderQueryModel> GetOrderHeaders
         (this OnlineStoreDbContext dbContext, 
          string currencyID = null, int? customerID = null, 
          int? employeeID = null, short? orderStatusID = null, 
          Guid? paymentMethodID = null, int? shipperID = null)
  {
   // Get query from DbSet
   var query = from orderHeader in dbContext.OrderHeader
    join currencyJoin in dbContext.Currency on orderHeader.CurrencyID 
         equals currencyJoin.Id into currencyTemp
     from currency in currencyTemp.DefaultIfEmpty()
    join customer in dbContext.Customer on orderHeader.CustomerID equals customer.Id
    join employeeJoin in dbContext.Employee on orderHeader.EmployeeID 
         equals employeeJoin.Id into employeeTemp
     from employee in employeeTemp.DefaultIfEmpty()
    join orderStatus in dbContext.OrderStatus 
         on orderHeader.OrderStatusID equals orderStatus.Id
    join paymentMethodJoin in dbContext.PaymentMethod on 
         orderHeader.PaymentMethodID equals paymentMethodJoin.Id into paymentMethodTemp
     from paymentMethod in paymentMethodTemp.DefaultIfEmpty()
    join shipperJoin in dbContext.Shipper on orderHeader.ShipperID equals 
         shipperJoin.Id into shipperTemp
     from shipper in shipperTemp.DefaultIfEmpty()
    select new OrderHeaderQueryModel
    {
     Id = orderHeader.Id,
     OrderStatusID = orderHeader.OrderStatusID,
     CustomerID = orderHeader.CustomerID,
     EmployeeID = orderHeader.EmployeeID,
     ShipperID = orderHeader.ShipperID,
     OrderDate = orderHeader.OrderDate,
     Total = orderHeader.Total,
     CurrencyID = orderHeader.CurrencyID,
     PaymentMethodID = orderHeader.PaymentMethodID,
     DetailsCount = orderHeader.DetailsCount,
     ReferenceOrderID = orderHeader.ReferenceOrderID,
     Comments = orderHeader.Comments,
     CreationUser = orderHeader.CreationUser,
     CreationDateTime = orderHeader.CreationDateTime,
     LastUpdateUser = orderHeader.LastUpdateUser,
     LastUpdateDateTime = orderHeader.LastUpdateDateTime,
     Timestamp = orderHeader.Timestamp,
     CurrencyCurrencyName = currency == null ? string.Empty : currency.CurrencyName,
     CurrencyCurrencySymbol = currency == null ? string.Empty : currency.CurrencySymbol,
     CustomerCompanyName = customer == null ? string.Empty : customer.CompanyName,
     CustomerContactName = customer == null ? string.Empty : customer.ContactName,
     EmployeeFirstName = employee == null ? string.Empty : employee.FirstName,
     EmployeeMiddleName = employee == null ? string.Empty : employee.MiddleName,
     EmployeeLastName = employee == null ? string.Empty : employee.LastName,
     EmployeeBirthDate = employee == null ? default(DateTime?) : employee.BirthDate,
     OrderStatusDescription = orderStatus == null ? string.Empty : orderStatus.Description,
     PaymentMethodPaymentMethodName = paymentMethod == null ? 
                                      string.Empty : paymentMethod.PaymentMethodName,
     PaymentMethodPaymentMethodDescription = paymentMethod == null ? 
                  string.Empty : paymentMethod.PaymentMethodDescription,
     ShipperCompanyName = shipper == null ? string.Empty : shipper.CompanyName,
     ShipperContactName = shipper == null ? string.Empty : shipper.ContactName,
    };
   
   // Filter by: 'CurrencyID'
   if (!string.IsNullOrEmpty(currencyID))
    query = query.Where(item => item.CurrencyID == currencyID);
   
   // Filter by: 'CustomerID'
   if (customerID.HasValue)
    query = query.Where(item => item.CustomerID == customerID);
   
   // Filter by: 'EmployeeID'
   if (employeeID.HasValue)
    query = query.Where(item => item.EmployeeID == employeeID);
   
   // Filter by: 'OrderStatusID'
   if (orderStatusID.HasValue)
    query = query.Where(item => item.OrderStatusID == orderStatusID);
   
   // Filter by: 'PaymentMethodID'
   if (paymentMethodID != null)
    query = query.Where(item => item.PaymentMethodID == paymentMethodID);
   
   // Filter by: 'ShipperID'
   if (shipperID.HasValue)
    query = query.Where(item => item.ShipperID == shipperID);
   
   return query;
  }

  public static async Task<OrderHeader> GetOrderHeaderAsync
         (this OnlineStoreDbContext dbContext, OrderHeader entity)
  {
   return await dbContext.OrderHeader
    .Include(p => p.CurrencyFk)
    .Include(p => p.CustomerFk)
    .Include(p => p.EmployeeFk)
    .Include(p => p.OrderStatusFk)
    .Include(p => p.PaymentMethodFk)
    .Include(p => p.ShipperFk)
    .FirstOrDefaultAsync(item => item.Id == entity.Id);
  }

  public static IQueryable<OrderStatus> GetOrderStatuses(this OnlineStoreDbContext dbContext)
  {
   // Get query from DbSet
   var query = dbContext.OrderStatus.AsQueryable();
   
   return query;
  }

  public static async Task<OrderStatus> GetOrderStatusAsync
         (this OnlineStoreDbContext dbContext, OrderStatus entity)
  {
   return await dbContext.OrderStatus.FirstOrDefaultAsync(item => item.Id == entity.Id);
  }

  public static IQueryable<PaymentMethod> 
         GetPaymentMethods(this OnlineStoreDbContext dbContext)
  {
   // Get query from DbSet
   var query = dbContext.PaymentMethod.AsQueryable();
   
   return query;
  }

  public static async Task<PaymentMethod> GetPaymentMethodAsync
         (this OnlineStoreDbContext dbContext, PaymentMethod entity)
  {
   return await dbContext.PaymentMethod.FirstOrDefaultAsync(item => item.Id == entity.Id);
  }

  public static IQueryable<Shipper> GetShippers(this OnlineStoreDbContext dbContext)
  {
   // Get query from DbSet
   var query = dbContext.Shipper.AsQueryable();
   
   return query;
  }

  public static async Task<Shipper> GetShipperAsync
         (this OnlineStoreDbContext dbContext, Shipper entity)
  {
   return await dbContext.Shipper.FirstOrDefaultAsync(item => item.Id == entity.Id);
  }
 }
}

AuditEntity in settings sets the columns for audit, this version of CatFactory supports creation and last update for audit.

ConcurrencyToken sets the column for concurrency, this value will be used in entity's mapping.

Don't forget, the previous settings are about columns, we need to use the name of columns not the properties.

EntitiesWithDataContracts indicates that entities will scaffold with joins in linq, this means CatFactory engine reads all foreign keys and creates a data contract to retrieve information, take a look at the GetOrderHeaders extension method on OnlineStoreDbContextSalesQueryExtensions class, there is a LINQ query with data contract and not a lambda expression as GetShippers method.

Please take a look on all extensions methods, they are async operations.

Setting Up CatFactory for Entity Framework Core

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

Name	Default Value	Description
`ForceOverwrite`	false	Indicates if code builder must overwrite files if they already exist
`SimplifyDataTypes`	true	Indicates if code builder must change from CLR types to native types (e.g., `Int32` => `int`)
`UseAutomaticPropertiesForEntities`	true	Indicates if entities classes will use automatic properties or not, if value is `false`, the entity will contain `private` fields
`EnableDataBindings`	false	Implements `INotifyPropertyChanged` property and add properties with fields for class definition
`UseDataAnnotations`	false	Indicates if mapping in EF Core it will be with data annotations
`DeclareDbSetPropertiesInDbContext`	false	Indicates if `DbContext` class definition must contain declaration of `DbSet`
`DeclareNavigationPropertiesAsVirtual`	false	Indicates if navigation properties must be declared as `virtual`
`NavigationPropertyEnumerableNamespace`	System.Collections. ObjectModel	Sets the namespace for navigation properties types
`NavigationPropertyEnumerableType`	Collection	Sets the type for collection navigation properties
`ConcurrencyToken`		Sets the column name that represents the concurrency token
`EntityInterfaceName`	IEntity	Sets the name for entity interface
`AuditEntity`		Sets the names for audit column: creation and last update (user name and date)
`EntitiesWithDataContracts`	false	Indicates if entities must scaffold as data transfer objects in repositories
`BackingFields`		Sets the name of columns to use fields instead of properties
`InsertExclusions`		Sets the name of columns to ignore for insert action
`UpdateExclusions`		Sets the name of columns to ignore for update action

Also, we can change the namespaces, set the values for output namespaces inside of project instance:

Namespace	Default Value	Description
Entity Layer	`EntityLayer`	Gets or sets the namespace for entity layer
Data Layer	`DataLayer`	Gets or sets the namespace for data layer
Configurations	`Configurations`	Gets or sets the namespace for configurations in data layer
Contracts	`Contracts`	Gets or sets the namespace for contracts in data layer
Data Contracts	`DataContracts`	Gets or sets the namespace for data contracts in data layer
Repositories	`Repositories`	Gets or sets the namespace for repositories in data layer

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 move generated files for existing project and make 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 and Dapper but in future, there will be Web API, Unit Tests and other things. :)
CatFactory has a package for Dapper, at this moment, there isn't an article for that but the way to use is similar for Entity Framework Core; you can install CatFactory.Dapper package from nuget.
We're working in continuous updates to provide better help for user.

Code Improvements

Added support for table functions
Added support for stored procedures
Allowed to overwrite naming convention for project
Added author's information for output files

Bugs?

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

History

12^th December, 2016: Initial version
16^th December, 2016: Addition of script for database
30^th January, 2017: Addition of async operations
12^th March, 2017: Addition of audit entity, concurrency token and entities with data contracts
4^th June, 2017: Addition of backing fields, data bindings and using of MEF for loading entities mapping
19^th September, 2017: Change on article's sections
31^st October, 2017: Update to alpha 3 version
21^st November, 2017: Addition of Trivia
18^th January, 2018: Addition of Workshop
30^th April, 2018: Update package to beta version
16^th May, 2018: Add support for EF Core 2
31^st August, 2020: Add Domain Driven Design Scaffolding

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Scaffolding Entity Framework Core 2 with CatFactory

Introduction

How Does It Work?

Concepts Behind CatFactory

Workshop

Query Builder Draft

Select All

Select by Key

Insert

Update

Delete

Select By

Working With Database

Packages

Background

Prerequisites

Skills

Software

Using the Code

Step 01 - Create Sample Database

Tables

Step 02 - Create Project

Step 03 - Add Code to Scaffold

Code Review

Setting Up CatFactory for Entity Framework Core

Points of Interest

Related Links

Code Improvements

Bugs?

History

License