Click here to Skip to main content
14,768,531 members
Articles » Languages » C# » Utilities
Article
Posted 12 Dec 2016

Tagged as

Stats

132.1K views
103 bookmarked

Scaffolding Entity Framework Core 2 with CatFactory

Rate me:
Please Sign up or sign in to vote.
4.87/5 (45 votes)
30 Aug 2020CPOL
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:

  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:

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:

  1. Does this implementation break Dapper concept?
  2. 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:

  1. CodeLine
  2. CommentLine
  3. EmptyLine
  4. PreprocessorDirectiveLine
  5. ReturnLine
  6. 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.

Related Links

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

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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

HHerzl
Software Developer
El Salvador El Salvador
CatFactory Creator.

Full Stack Developer with Experience in C#, Entity Framework Core, ASP.NET Core and Angular.

Comments and Discussions

 
QuestionLooks great Pin
David Schiffer31-Oct-18 9:46
professionalDavid Schiffer31-Oct-18 9:46 
AnswerRe: Looks great Pin
HHerzl7-Nov-18 22:08
MemberHHerzl7-Nov-18 22:08 
SuggestionImprovement and suggestions Pin
Member 965484630-Nov-17 1:23
MemberMember 965484630-Nov-17 1:23 
GeneralRe: Improvement and suggestions Pin
HHerzl18-Dec-17 18:03
MemberHHerzl18-Dec-17 18:03 
PraiseRe: Improvement and suggestions Pin
Member 965484620-Dec-17 8:08
MemberMember 965484620-Dec-17 8:08 
GeneralRe: Improvement and suggestions Pin
Amelia J. Abram27-May-19 0:29
professionalAmelia J. Abram27-May-19 0:29 
BugPrimary keys Pin
Member 965484629-Nov-17 1:25
MemberMember 965484629-Nov-17 1:25 
Hi,
When generating primary keys in configuration for views or tables without keys, you should select only columns where nullable property is false.

Regards.
GeneralRe: Primary keys Pin
HHerzl3-Dec-17 18:39
MemberHHerzl3-Dec-17 18:39 
GeneralRe: Primary keys Pin
Member 96548464-Dec-17 8:11
MemberMember 96548464-Dec-17 8:11 
GeneralRe: Primary keys Pin
HHerzl5-Dec-17 3:19
MemberHHerzl5-Dec-17 3:19 
GeneralRe: Primary keys Pin
HHerzl18-Dec-17 14:26
MemberHHerzl18-Dec-17 14:26 
Bugviews Pin
Member 965484629-Nov-17 0:50
MemberMember 965484629-Nov-17 0:50 
GeneralRe: views Pin
HHerzl3-Dec-17 18:33
MemberHHerzl3-Dec-17 18:33 
GeneralRe: views Pin
Member 96548464-Dec-17 8:13
MemberMember 96548464-Dec-17 8:13 
BugAn other issue Pin
Member 965484628-Nov-17 22:45
MemberMember 965484628-Nov-17 22:45 
GeneralRe: An other issue Pin
HHerzl5-Dec-17 3:14
MemberHHerzl5-Dec-17 3:14 
GeneralRe: An other issue Pin
HHerzl18-Dec-17 14:25
MemberHHerzl18-Dec-17 14:25 
QuestionGreat Job :-) Pin
Member 96548466-Nov-17 0:27
MemberMember 96548466-Nov-17 0:27 
AnswerRe: Great Job :-) Pin
HHerzl7-Nov-17 17:57
MemberHHerzl7-Nov-17 17:57 
AnswerRe: Great Job :-) Pin
HHerzl8-Nov-17 22:47
MemberHHerzl8-Nov-17 22:47 
PraiseRe: Great Job :-) Pin
Member 96548469-Nov-17 0:38
MemberMember 96548469-Nov-17 0:38 
GeneralRe: Great Job :-) Pin
HHerzl20-Nov-17 15:11
MemberHHerzl20-Nov-17 15:11 
PraiseRe: Great Job :-) Pin
Member 965484624-Nov-17 0:54
MemberMember 965484624-Nov-17 0:54 
GeneralRe: Great Job :-) Pin
Member 965484628-Nov-17 22:48
MemberMember 965484628-Nov-17 22:48 
QuestionGreat article & concept, but a little problem Pin
dkurok5-Nov-17 2:58
Memberdkurok5-Nov-17 2:58 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.