Click here to Skip to main content
13,633,747 members
Click here to Skip to main content
Add your own
alternative version

Tagged as

Stats

80.3K views
81 bookmarked
Posted 12 Dec 2016
Licenced CPOL

Scaffolding Entity Framework Core 2 with CatFactory

, 15 May 2018
Rate this:
Please Sign up or sign in to vote.
Scaffolding Entity Framework Core 2 with CatFactory

Introduction

Entity Framework Core is the version of Entity Framework for .NET Core, to develop large applications there is a common issue: write code for a lot kind of objects: entities, mappings, contracts, data contracts. CatFactory provides an easy way to scaffold code in order to get focus on complex logic.

CatFactory's History

In 2005 year, I was on my college days and I worked on my final project that included a lot of tables, for those days C# didn't have automatic properties also I worked on store procedures that included a lot of columns, I thought if there was a way to generate all that code because it was repetitive and I wasted time in wrote a lot of code.

For 2006 beggining I've worked for a company and I worked in a prototype to generate code but I didn't have experience and I was a junior developer, so I developed a version in WebForms that didn't allow to save the structure ha,ha,ha that project it was my first project in C# because I came from VB world but I bought a book about Web Services in DotNet and that book used C# code, that was new for me but it got me a very important idea, learn C# and I wrote all first code generation form in C#.

Later, there was a prototype of Entity for SQL, the grandfather of entity framework and I develop a simple ORM because I had table class and other classes such as Column, so after of reviewed Entity for SQL I decided to add the logic to read database and provide a simple way to read the database also of code generation.

For 2008 I built the first ORM based on my code generation engine, in that time it was called F4N1, I worked on an ORM must endure different databases engines such as SQL Server, Sybase and Oracle; so I generated a lot of classes with that engine, for that time the automated unit tests did not exist, I had a webform page that generated that code ha,ha,ha I know it was ugly and crappy but in that time that was my knowledge allowed me

For 2011 I worked on a demo for a person that worked in his company and that person used another tool for code generation, so my code generation engine wasn't use for his work.

For 2012 I worked for a company needed to rebuilt all system with new technologies (ASP.NET MVC and Entity Framework) so I invested time about MVC and EF learning but as usual, there isn't time for that ha,ha,ha and again my code generation it wasn't considered for that upgrade =(

For 2014, I thought to make a nuget package to my code generation but in those days I didn't have the focus to accomplish that feature and always I used my code generation as a private tool, in some cases I shared my tool with some coworkers to generate code and reduce the time for code writing.

For 2016, I decided to create a nuget package and integrates with EF Core, using all experience from 10 years ago :D

Please remember that from the beginning I was continuing improve the way of code generation, my first code was a crap but with the timeline I've improved the design and naming for objects.

Why I named CatFactory? It was I had a cat, her name was Mindy and that cat had manny kittens (sons), so the basic idea it was the code generation engine generates the code as fast Mindy provided kittens ha,ha,ha

Source picture for "CatFactory" concept =^^=

Behind CatFactory Concept

Background

Code generation it's a common technique developers use to reduce time in code writing, I know the most programmers build a code generator in their professional lifes.

EF 6.x had a wizard for code generation, that tool generates DbContext and POCOs but there isn't code for Fluent API, Repositories and other things like those; with .NET Core there is a command line tool for code generation but we have the same scenario, there is generation only for DbContext and Entities; with CatFactory we're looking for a simple way to generate code with enterprise patterns, please don't forget this is an alpha version of CatFactory, don't pretend to have in this date a full version of code generation engine.

Why don't use code CodeDOM? CodeDOM it's a complex code generation engine, I don't saying CodeDOM sucks or something like that, but at this moment we're focus on generate code in the more simple way, maybe in the incoming versions we'll add integration with CodeDOM.

Skills Prerequisites

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

Software Prerequisites

  • IDE (Visual Studio 2015 or Visual Studio 2017 or VS Code)
  • Access to valid SQL Server instance

Using the code

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

Step 01 - Create sample database

I'm using a sample database named Store, but you need to change the connection string acording to your environment, project name and output directory, please make sure that output directory exists and you have permissions to write on that directory, please make sure about that point to avoid common errors.

In order to generate code, run the following script on your SQL Server instance:

use master
go

create database Store
go

use Store
go

create schema HumanResources
go

create schema Production
go

create schema Sales
go

create table [EventLog]
(
	[EventLogID] uniqueidentifier not null,
	[EventType] int not null,
	[Key] varchar(255) not null,
	[Message] varchar(max) not null,
	[EntryDate] datetime not null
)

create table [ChangeLog]
(
	[ChangeLogID] int not null identity(1, 1),
	[ClassName] varchar(255) not null,
	[PropertyName] varchar(255) not null,
	[Key] varchar(255) not null,
	[OriginalValue] varchar(max) null,
	[CurrentValue] varchar(max) null,
	[UserName] varchar(25) not null,
	[ChangeDate] datetime not null
)

create table [ChangeLogExclusion]
(
	[ChangeLogExclusionID] int not null identity(1, 1),
	[EntityName] varchar(128) not null,
	[PropertyName] varchar(128) not null
)

create table [dbo].[Country]
(
	[CountryID] int not null,
	[CountryName] varchar(100) not null,
	[CreationUser] varchar(25) not null,
	[CreationDateTime] datetime not null,
	[LastUpdateUser] varchar(25) null,
	[LastUpdateDateTime] datetime null,
	[Timestamp] rowversion null
)

create table [dbo].[Currency]
(
	[CurrencyID] smallint not null identity(1000, 1000),
	[CurrencyName] varchar(50) not null,
	[CurrencySymbol] varchar(1) not null,
	[CreationUser] varchar(25) not null,
	[CreationDateTime] datetime not null,
	[LastUpdateUser] varchar(25) null,
	[LastUpdateDateTime] datetime null,
	[Timestamp] rowversion null
)

create table [dbo].[CountryCurrency]
(
	[CountryCurrencyID] int not null identity(1, 1),
	[CountryID] int not null,
	[CurrencyID] smallint not null,
	[CreationUser] varchar(25) not null,
	[CreationDateTime] datetime not null,
	[LastUpdateUser] varchar(25) null,
	[LastUpdateDateTime] datetime null,
	[Timestamp] rowversion null
)

create table [HumanResources].[Employee]
(
	[EmployeeID] int not null identity(1, 1),
	[FirstName] varchar(25) not null,
	[MiddleName] varchar(25) null,
	[LastName] varchar(25) not null,
	[BirthDate] datetime not null,
	[CreationUser] varchar(25) not null,
	[CreationDateTime] datetime not null,
	[LastUpdateUser] varchar(25) null,
	[LastUpdateDateTime] datetime null,
	[Timestamp] rowversion null
)

create table [Production].[ProductCategory]
(
	[ProductCategoryID] int not null identity(1, 1),
	[ProductCategoryName] varchar(100) not null,
	[CreationUser] varchar(25) not null,
	[CreationDateTime] datetime not null,
	[LastUpdateUser] varchar(25) null,
	[LastUpdateDateTime] datetime null,
	[Timestamp] rowversion null
)

create table [Production].[Product]
(
	[ProductID] int not null identity(1, 1),
	[ProductName] varchar(100) not null,
	[ProductCategoryID] int not null,
	[UnitPrice] decimal(8, 4) not null,
	[Description] varchar(255) null,
	[Discontinued] bit not null,
	[CreationUser] varchar(25) not null,
	[CreationDateTime] datetime not null,
	[LastUpdateUser] varchar(25) null,
	[LastUpdateDateTime] datetime null,
	[Timestamp] rowversion null
)

create table [Production].[Warehouse]
(
	[WarehouseID] varchar(5) not null,
	[WarehouseName] varchar(100) not null,
	[CreationUser] varchar(25) not null,
	[CreationDateTime] datetime not null,
	[LastUpdateUser] varchar(25) null,
	[LastUpdateDateTime] datetime null,
	[Timestamp] rowversion null
)

create table [Production].[ProductInventory]
(
	[ProductInventoryID] int not null identity(1, 1),
	[ProductID] int not null,
	[WarehouseID] varchar(5) not null,
	[Quantity] int not null,
	[Stocks] int not null,
	[CreationUser] varchar(25) not null,
	[CreationDateTime] datetime not null,
	[LastUpdateUser] varchar(25) null,
	[LastUpdateDateTime] datetime null,
	[Timestamp] rowversion null
)

create table [Sales].[OrderStatus]
(
	[OrderStatusID] smallint not null,
	[Description] varchar(100) not null,
	[CreationUser] varchar(25) not null,
	[CreationDateTime] datetime not null,
	[LastUpdateUser] varchar(25) null,
	[LastUpdateDateTime] datetime null,
	[Timestamp] rowversion null
)

create table [Sales].[PaymentMethod]
(
	[PaymentMethodID] uniqueidentifier not null,
	[PaymentMethodName] varchar(50) not null,
	[PaymentMethodDescription] varchar(255) not null,
	[CreationUser] varchar(25) not null,
	[CreationDateTime] datetime not null,
	[LastUpdateUser] varchar(25) null,
	[LastUpdateDateTime] datetime null,
	[Timestamp] rowversion null
)

create table [Sales].[Customer]
(
	[CustomerID] int not null identity(1, 1),
	[CompanyName] varchar(100) null,
	[ContactName] varchar(100) null,
	[CreationUser] varchar(25) not null,
	[CreationDateTime] datetime not null,
	[LastUpdateUser] varchar(25) null,
	[LastUpdateDateTime] datetime null,
	[Timestamp] rowversion null
)

create table [Sales].[Shipper]
(
	[ShipperID] int not null identity(1, 1),
	[CompanyName] varchar(100) null,
	[ContactName] varchar(100) null,
	[CreationUser] varchar(25) not null,
	[CreationDateTime] datetime not null,
	[LastUpdateUser] varchar(25) null,
	[LastUpdateDateTime] datetime null,
	[Timestamp] rowversion null
)

create table [Sales].[Order]
(
	[OrderID] bigint not null identity(1, 1),
	[OrderStatusID] smallint not null,
	[CustomerID] int not null,
	[EmployeeID] int null,
	[ShipperID] int null,
	[OrderDate] datetime not null,
	[Total] decimal(12, 4) not null,
	[CurrencyID] smallint null,
	[PaymentMethodID] uniqueidentifier null,
	[Comments] varchar(max) null,
	[CreationUser] varchar(25) not null,
	[CreationDateTime] datetime not null,
	[LastUpdateUser] varchar(25) null,
	[LastUpdateDateTime] datetime null,
	[Timestamp] rowversion null
)

create table [Sales].[OrderDetail]
(
	[OrderDetailID] bigint not null identity(1, 1),
	[OrderID] bigint not null,
	[ProductID] int not null,
	[ProductName] varchar(255) not null,
	[UnitPrice] decimal(8, 4) not null,
	[Quantity] int not null,
	[Total] decimal(8, 4) not null,
	[CreationUser] varchar(25) not null,
	[CreationDateTime] datetime not null,
	[LastUpdateUser] varchar(25) null,
	[LastUpdateDateTime] datetime null,
	[Timestamp] rowversion null
)
go

alter table [EventLog]
	add constraint [PK_EventLog] primary key (EventLogID)
go

alter table [ChangeLog]
	add constraint [PK_ChangeLog] primary key (ChangeLogID)
go

alter table [ChangeLogExclusion]
	add constraint [PK_ChangeLogExclusion] primary key(ChangeLogExclusionID)

alter table [dbo].[Country]
	add constraint [PK_Country] primary key([CountryID])
go

alter table [dbo].[Currency]
	add constraint [PK_Currency] primary key([CurrencyID])
go

alter table [dbo].[CountryCurrency]
	add constraint [PK_CountryCurrency] primary key([CountryCurrencyID])
go

alter table [HumanResources].[Employee]
	add constraint [PK_HumanResources_Employee] primary key (EmployeeID)
go

alter table [Production].[ProductCategory]
	add constraint [PK_Production_ProductCategory] primary key (ProductCategoryID)
go

alter table [Production].[Product]
	add constraint [PK_Production_Product] primary key (ProductID)
go

alter table [Production].[Product]
	add constraint [U_Production_Product_ProductName] unique (ProductName)
go

alter table [Production].[ProductInventory]
	add constraint [PK_Production_ProductInventory] primary key (ProductInventoryID)
go

alter table [Production].[Warehouse]
	add constraint [PK_Production_Warehouse] primary key (WarehouseID)
go

alter table [Sales].[Customer]
	add constraint [PK_Sales_Customer] primary key (CustomerID)
go

alter table [Sales].[Shipper]
	add constraint [PK_Sales_Shipper] primary key (ShipperID)
go

alter table [Sales].[OrderStatus]
	add constraint [PK_Sales_OrderStatus] primary key (OrderStatusID)
go

alter table [Sales].[PaymentMethod]
	add constraint [PK_Sales_PaymentMethod] primary key (PaymentMethodID)
go

alter table [Sales].[Order]
	add constraint [PK_Sales_Order] primary key (OrderID)
go

alter table [Sales].[OrderDetail]
	add constraint [PK_Sales_OrderDetail] primary key (OrderDetailID)
go

alter table [dbo].[CountryCurrency]
	add constraint [U_CountryCurrency] unique (CountryID, CurrencyID)
go

alter table [Sales].[OrderDetail]
	add constraint [U_Sales_OrderDetail] unique (OrderID, ProductID)
go

alter table [ChangeLogExclusion]
	add constraint [U_ChangeLogExclusion] unique(EntityName, PropertyName)
go

alter table [dbo].[CountryCurrency]
	add constraint [FK_CountryCurrency_Country] foreign key (CountryID)
		references [dbo].[Country]
go

alter table [dbo].[CountryCurrency]
	add constraint [FK_CountryCurrency_Currency] foreign key (CurrencyID)
		references [dbo].[Currency]
go

alter table [Production].[Product]
	add constraint [FK_Production_Product_ProductCategory] foreign key (ProductCategoryID)
		references [Production].[ProductCategory]
go

alter table [Production].[ProductInventory]
	add constraint [FK_Production_ProductInventory_Product] foreign key (ProductID)
		references [Production].[Product]
go

alter table [Production].[ProductInventory]
	add constraint [FK_Production_ProductInventory_Warehouse] foreign key (WarehouseID)
		references [Production].[Warehouse]
go

alter table [Sales].[Order]
	add constraint [FK_Sales_Order_OrderStatus] foreign key (OrderStatusID)
		references [Sales].[OrderStatus]
go

alter table [Sales].[Order]
	add constraint [FK_Sales_Order_Customer] foreign key (CustomerID)
		references [Sales].[Customer]
go

alter table [Sales].[Order]
	add constraint [FK_Sales_Order_Employee] foreign key (EmployeeID)
		references [HumanResources].[Employee]
go

alter table [Sales].[Order]
	add constraint [FK_Sales_Order_Shipper] foreign key (ShipperID)
		references [Sales].[Shipper]
go

alter table [Sales].[Order]
	add constraint [FK_Sales_Order_Currency] foreign key (CurrencyID)
		references [dbo].[Currency]
go

alter table [Sales].[Order]
	add constraint [FK_Sales_Order_PaymentMethod] foreign key (PaymentMethodID)
		references [Sales].[PaymentMethod]
go

alter table [Sales].[OrderDetail]
	add constraint [FK_Sales_OrderDetail_Order] foreign key (OrderID)
		references [Sales].[Order]
go

alter table [Sales].[OrderDetail]
	add constraint [FK_Sales_OrderDetail_Product] foreign key (ProductID)
		references [Production].[Product]
go

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: Store.CatFactory.

Step 03 - Add Packages for Project

Add package with Nuget Package Manager for your console project:

Name Version Description
CatFactory.SqlServer 1.0.0-beta-sun-build02 Provides import database feature for SQL Server
CatFactory.EntityFrameworkCore 1.0.0-beta-sun-build02 Provides scaffolding for Entity Framework Core

Visual Studio 2017

Visual Studio 2017 Nuget Package Manager

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(LoggerMocker.GetLogger<SqlServerDatabaseFactory>(), "server=(local);database=Store;integrated security=yes;", "dbo.sysdiagrams");

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

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

// Apply settings for specific object
project.Select("Sales.Order", settings => settings.EntitiesWithDataContracts = true);

// Build features for project with default behavior: group all entities by schema into a feature
project.BuildFeatures();

// Add event handlers to before and after of scaffold

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

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

// Scaffolding =^^=
project
    .ScaffoldEntityLayer()
    .ScaffoldDataLayer();
Extension methods for EntityFrameworkCoreProject instance
Name Description
ScaffoldEntityLayer Scaffold code for entities from tables and views
ScaffoldDataLayer Scaffold code for data access: DbContext, Mapping, Contracts, Data Contracts, Repositories and extensions

Step 04 - Create Console Project

Obviously we add this simple code to test generated code doesn't have errors, if we use the generated code in Web API project, we'll need to add dependency injection and anothers things, please check in links list at the end of this post for more information.

Now we can go to output directory and create a console project for .NET Core and add code according to your choose:

Get All:

// Create options for DbContext instance
var options = new DbContextOptionsBuilder<StoreDbContext>()
    .UseSqlServer("server=(local);database=Store;integrated security=yes;")
    .Options;

// Create DbContext instance
var dbContext = new StoreDbContext(options);

using (var repository = new SalesRepository(dbContext))
{
    // Get "in memory" query
    var query = repository.GetOrders();
    
    // Get paging info
    var totalItems = await query.CountAsync();
    var pageSize = 25;
    var pageNumber = 1;
    var pageCount = totalItems / pageSize;
    
    // Retrieve list from database
    var list = await query.Paging(pageSize, pageNumber).ToListAsync();
}

Get by Key:

// Create options for DbContext instance
var options = new DbContextOptionsBuilder<StoreDbContext>()
    .UseSqlServer("server=(local);database=Store;integrated security=yes;")
    .Options;

// Create DbContext instance
var dbContext = new StoreDbContext(options);

using (var repository = new SalesRepository(dbContext))
{
    // Get entity by id
    var entity = await repository.GetOrderAsync(new Order(1));
}

Get by Unique (If table contains an unique constraint):

// Create options for DbContext instance
var options = new DbContextOptionsBuilder<StoreDbContext>()
    .UseSqlServer("server=(local);database=Store;integrated security=yes;")
    .Options;

// Create DbContext instance
var dbContext = new StoreDbContext(options);

using (var repository = new ProductionRepository(dbContext))
{
    // Get entity by name
    var entity = await repository.GetProductByProductNameAsync(new Product { ProductName = "The King of Fighters XIV" });
}

Add:

// Create options for DbContext instance
var options = new DbContextOptionsBuilder<StoreDbContext>()
    .UseSqlServer("server=(local);database=Store;integrated security=yes;")
    .Options;

// Create DbContext instance
var dbContext = new StoreDbContext(options);

using (var repository = new SalesRepository(dbContext))
{
    // Create instance for entity
    var entity = new Order();
    
    // Set values for properties
    // e.g. entity.Total = 29.99m;
    
    // Add entity in database
    await repository.AddOrderAsync(entity);
}

Update:

// Create options for DbContext instance
var options = new DbContextOptionsBuilder<StoreDbContext>()
    .UseSqlServer("server=(local);database=Store;integrated security=yes;")
    .Options;

// Create DbContext instance
var dbContext = new StoreDbContext(options);

using (var repository = new SalesRepository(dbContext))
{
    // Retrieve entity by id instance for entity
    var entity = await repository.GetOrderAsync(new Order(1));
    
    // Set values for properties
    // e.g. entity.Total = 29.99m;
    
    // Add entity in database
    await repository.UpdateOrderAsync(entity);
}

Remove:

// Create options for DbContext instance
var options = new DbContextOptionsBuilder<StoreDbContext>()
    .UseSqlServer("server=(local);database=Store;integrated security=yes;")
    .Options;

// Create DbContext instance
var dbContext = new StoreDbContext(options);

using (var repository = new SalesRepository(dbContext))
{
    // Retrieve entity by id instance for entity
    var entity = await repository.GetOrderAsync(new Order(1));
    
    // Add entity in database
    await repository.RemoveOrderAsync(entity);
}

The previous code samples do not include error handling, these are samples to show the way to work with generated code.

How works all code together?

We create a StoreDbContext instance, that instance use the connection string from DbContextOptionsBuilder and inside of OnModelCreating method there is the configuration for all mappings, that's because it's more a stylish way to mapping entities instead of add a lot of lines inside of OnModelCreating

Later, for example we create an instance of SalesRepository passing a valid instance of StoreDbContext and then we can access to repository's operations.

For this architecture implementation we are using the DotNet naming conventions: PascalCase for classes, interfaces and methods; camelCase for parameters.

Namespaces for generated code:

  1. EntityLayer
  2. DataLayer
  3. DataLayer\Contracts
  4. DataLayer\DataContracts
  5. DataLayer\Mapping
  6. DataLayer\Repositories

Inside of EntityLayer we'll place all entities, in this context entity means a class that represents a table or view from database, sometimes entity is named POCO (Plain Old Common language runtime Object) than means a class with only properties not methods nor other things (events)

Inside of DataLayer we'll place DbContext and AppSettings because they're common classes for DataLayer

Inside of DataLayer\Contracts we'll place all interfaces that represent operations catalog, we're focusing on schemas and we'll create one interface per schema and Store contract for default schema (dbo)

Inside of DataLayer\DataContracts we'll place all object definitions for returned values from Contracts namespace, for now this directory would be empty

Inside of DataLayer\Mapping we'll place all object definition related to mapping a class for database access

Inside of DataLayer\Repositories we'll place the implementations for Contracts definitons

Inside of EntityLayer and DataLayer\Mapping we'll create one directory per schema without include the default schema.

We can review the link about EF Core for enterprise, and we can understand this guide allow to us generate all of that code to reduce time in code writing.

Code Review

We'll review some the generated code for one entity to understand this design:

Code for Order class:

using System;
using Store.EntityLayer;
using Store.EntityLayer.Sales;
using Store.EntityLayer.HumanResources;
using System.Collections.ObjectModel;

namespace Store.EntityLayer.Sales
{
 public class Order : IAuditEntity
 {
  public Order()
  {
  }

  public Order(Int64? orderID)
  {
   OrderID = orderID;
  }

  public Int64? OrderID { get; set; }

  public Int16? OrderStatusID { get; set; }

  public Int32? CustomerID { get; set; }

  public Int32? EmployeeID { get; set; }

  public Int32? ShipperID { get; set; }

  public DateTime? OrderDate { get; set; }

  public Decimal? Total { get; set; }

  public Int16? CurrencyID { get; set; }

  public Guid? PaymentMethodID { 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> OrderDetails { get; set; }
 }
}

Code for StoreDbContext class:

using Microsoft.EntityFrameworkCore;
using Store.DataLayer.Configurations;

namespace Store.DataLayer
{
 public class StoreDbContext : DbContext
 {
  public StoreDbContext(DbContextOptions<StoreDbContext> options)
   : base(options)
  {
  }

  protected override void OnModelCreating(ModelBuilder modelBuilder)
  {
   // Apply all configurations for tables
   
   modelBuilder
    .ApplyConfiguration(new ChangeLogConfiguration())
    .ApplyConfiguration(new ChangeLogExclusionConfiguration())
    .ApplyConfiguration(new CountryConfiguration())
    .ApplyConfiguration(new CountryCurrencyConfiguration())
    .ApplyConfiguration(new CurrencyConfiguration())
    .ApplyConfiguration(new EventLogConfiguration())
    .ApplyConfiguration(new EmployeeConfiguration())
    .ApplyConfiguration(new EmployeeAddressConfiguration())
    .ApplyConfiguration(new EmployeeEmailConfiguration())
    .ApplyConfiguration(new ProductConfiguration())
    .ApplyConfiguration(new ProductCategoryConfiguration())
    .ApplyConfiguration(new ProductInventoryConfiguration())
    .ApplyConfiguration(new WarehouseConfiguration())
    .ApplyConfiguration(new CustomerConfiguration())
    .ApplyConfiguration(new CustomerAddressConfiguration())
    .ApplyConfiguration(new CustomerEmailConfiguration())
    .ApplyConfiguration(new OrderConfiguration())
    .ApplyConfiguration(new OrderDetailConfiguration())
    .ApplyConfiguration(new OrderStatusConfiguration())
    .ApplyConfiguration(new PaymentMethodConfiguration())
    .ApplyConfiguration(new ShipperConfiguration())
   ;
   
   // Apply all configurations for views
   
   modelBuilder
    .ApplyConfiguration(new OrderSummaryConfiguration())
   ;
   
   base.OnModelCreating(modelBuilder);
  }
 }
}

Code for OrderConfiguration class:

using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata.Builders;
using Store.EntityLayer.Sales;

namespace Store.DataLayer.Configurations
{
 public class OrderConfiguration : IEntityTypeConfiguration<Order>
 {
  public void Configure(EntityTypeBuilder<Order> builder)
  {
   // Set configuration for entity
   builder.ToTable("Order", "Sales");
   
   // Set key for entity
   builder.HasKey(p => p.OrderID);
   
   // Set identity for entity (auto increment)
   builder.Property(p => p.OrderID).UseSqlServerIdentityColumn();
   
   // Set configuration for columns
   builder.Property(p => p.OrderID).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("smallint");
   builder.Property(p => p.PaymentMethodID).HasColumnType("uniqueidentifier");
   builder.Property(p => p.Comments).HasColumnType("varchar(-1)");
   builder.Property(p => p.CreationUser).HasColumnType("varchar(25)").IsRequired();
   builder.Property(p => p.CreationDateTime).HasColumnType("datetime").IsRequired();
   builder.Property(p => p.LastUpdateUser).HasColumnType("varchar(25)");
   builder.Property(p => p.LastUpdateDateTime).HasColumnType("datetime");
   builder.Property(p => p.Timestamp).HasColumnType("timestamp");
   
   // 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.Orders)
    .HasForeignKey(p => p.CurrencyID)
    .HasConstraintName("FK_Sales_Order_Currency");
   
   builder
    .HasOne(p => p.CustomerFk)
    .WithMany(b => b.Orders)
    .HasForeignKey(p => p.CustomerID)
    .HasConstraintName("FK_Sales_Order_Customer");
   
   builder
    .HasOne(p => p.EmployeeFk)
    .WithMany(b => b.Orders)
    .HasForeignKey(p => p.EmployeeID)
    .HasConstraintName("FK_Sales_Order_Employee");
   
   builder
    .HasOne(p => p.OrderStatusFk)
    .WithMany(b => b.Orders)
    .HasForeignKey(p => p.OrderStatusID)
    .HasConstraintName("FK_Sales_Order_OrderStatus");
   
   builder
    .HasOne(p => p.PaymentMethodFk)
    .WithMany(b => b.Orders)
    .HasForeignKey(p => p.PaymentMethodID)
    .HasConstraintName("FK_Sales_Order_PaymentMethod");
   
   builder
    .HasOne(p => p.ShipperFk)
    .WithMany(b => b.Orders)
    .HasForeignKey(p => p.ShipperID)
    .HasConstraintName("FK_Sales_Order_Shipper");
   
  }
 }
}

Code for ISalesRepository interface:

using System;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.EntityFrameworkCore;
using Store.EntityLayer;
using Store.DataLayer.Contracts;
using Store.EntityLayer.HumanResources;
using Store.EntityLayer.Production;
using Store.EntityLayer.Sales;
using Store.DataLayer.DataContracts;

namespace Store.DataLayer.Contracts
{
 public interface ISalesRepository : IRepository
 {
  IQueryable<Customer> GetCustomers();

  Task<Customer> GetCustomerAsync(Customer entity);

  Task<Int32> AddCustomerAsync(Customer entity);

  Task<Int32> UpdateCustomerAsync(Customer changes);

  Task<Int32> RemoveCustomerAsync(Customer entity);

  IQueryable<OrderDto> GetOrders(Int16? currencyID = null, Int32? customerID = null, Int32? employeeID = null, Int16? orderStatusID = null, Guid? paymentMethodID = null, Int32? shipperID = null);

  Task<Order> GetOrderAsync(Order entity);

  Task<Int32> AddOrderAsync(Order entity);

  Task<Int32> UpdateOrderAsync(Order changes);

  Task<Int32> RemoveOrderAsync(Order entity);

  IQueryable<OrderDetail> GetOrderDetails(Int64? orderID = null, Int32? productID = null);

  Task<OrderDetail> GetOrderDetailByOrderIDAndProductIDAsync(OrderDetail entity);

  Task<OrderDetail> GetOrderDetailAsync(OrderDetail entity);

  Task<Int32> AddOrderDetailAsync(OrderDetail entity);

  Task<Int32> UpdateOrderDetailAsync(OrderDetail changes);

  Task<Int32> RemoveOrderDetailAsync(OrderDetail entity);

  IQueryable<OrderStatus> GetOrderStatus();

  Task<OrderStatus> GetOrderStatusAsync(OrderStatus entity);

  Task<Int32> AddOrderStatusAsync(OrderStatus entity);

  Task<Int32> UpdateOrderStatusAsync(OrderStatus changes);

  Task<Int32> RemoveOrderStatusAsync(OrderStatus entity);

  IQueryable<PaymentMethod> GetPaymentMethods();

  Task<PaymentMethod> GetPaymentMethodAsync(PaymentMethod entity);

  Task<Int32> AddPaymentMethodAsync(PaymentMethod entity);

  Task<Int32> UpdatePaymentMethodAsync(PaymentMethod changes);

  Task<Int32> RemovePaymentMethodAsync(PaymentMethod entity);

  IQueryable<Shipper> GetShippers();

  Task<Shipper> GetShipperAsync(Shipper entity);

  Task<Int32> AddShipperAsync(Shipper entity);

  Task<Int32> UpdateShipperAsync(Shipper changes);

  Task<Int32> RemoveShipperAsync(Shipper entity);
 }
}

Code for OrderDataContract class:

using System;

namespace Store.DataLayer.DataContracts
{
 public class OrderDto
 {
  public Int64? OrderID { get; set; }

  public Int16? OrderStatusID { get; set; }

  public Int32? CustomerID { get; set; }

  public Int32? EmployeeID { get; set; }

  public Int32? ShipperID { get; set; }

  public DateTime? OrderDate { get; set; }

  public Decimal? Total { get; set; }

  public Int16? CurrencyID { get; set; }

  public Guid? PaymentMethodID { 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 String CurrencyCurrencyName { get; set; }

  public String CurrencyCurrencySymbol { get; set; }

  public String CustomerCompanyName { get; set; }

  public String CustomerContactName { get; set; }

  public String EmployeeFirstName { get; set; }

  public String EmployeeMiddleName { get; set; }

  public String EmployeeLastName { get; set; }

  public DateTime? EmployeeBirthDate { get; set; }

  public String OrderStatusDescription { get; set; }

  public String PaymentMethodPaymentMethodName { get; set; }

  public String PaymentMethodPaymentMethodDescription { get; set; }

  public String ShipperCompanyName { get; set; }

  public String ShipperContactName { get; set; }
 }
}

Code for Repository class:

using System;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.EntityFrameworkCore;
using Store.EntityLayer;

namespace Store.DataLayer.Contracts
{
 public class Repository
 {
  protected Boolean Disposed;
  protected StoreDbContext DbContext;

  public Repository(StoreDbContext dbContext)
  {
   DbContext = dbContext;
  }

  public void Dispose()
  {
   if (!Disposed)
   {
    DbContext?.Dispose();
   
    Disposed = true;
   }
  }

  protected virtual void Add<TEntity>(TEntity entity) where TEntity : class
  {
   // Cast entity to IAuditEntity
   var cast = entity as IAuditEntity;
   
   if (cast != null)
   {
    if (!cast.CreationDateTime.HasValue)
    {
     // Set creation date time
     cast.CreationDateTime = DateTime.Now;
    }
   }
   
   // Get entry from Db context
   var entry = DbContext.Entry(entity);
   
   if (entry.State != EntityState.Detached)
   {
    // Set state for entity entry
    entry.State = EntityState.Added;
   }
   else
   {
    // Add entity to DbSet
    DbContext.Set<TEntity>().Add(entity);
   }
  }

  protected virtual void Update<TEntity>(TEntity entity) where TEntity : class
  {
   var cast = entity as IAuditEntity;
   
   if (cast != null)
   {
    if (!cast.LastUpdateDateTime.HasValue)
    {
     // Set last update date time
     cast.LastUpdateDateTime = DateTime.Now;
    }
   }
   
   // Get entity's entry
   var entry = DbContext.Entry(entity);
   
   if (entry.State == EntityState.Detached)
   {
    // Attach entity to DbSet
    DbContext.Set<TEntity>().Attach(entity);
   }
   
   entry.State = EntityState.Modified;
  }

  protected virtual void Remove<TEntity>(TEntity entity) where TEntity : class
  {
   // Get entity's entry
   var entry = DbContext.Entry(entity);
   
   if (entry.State == EntityState.Deleted)
   {
    // Create set for entity
    var dbSet = DbContext.Set<TEntity>();
   
    // Attach and remove entity from DbSet
    dbSet.Attach(entity);
    dbSet.Remove(entity);
   }
   else
   {
    // Set state for entity to 'Deleted'
    entry.State = EntityState.Deleted;
   }
  }

  public Int32 CommitChanges()
   => DbContext.SaveChanges();

  public Task<Int32> CommitChangesAsync()
   => DbContext.SaveChangesAsync();
 }
}

Code for SalesRepository class:

using System;using System;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.EntityFrameworkCore;
using Store.EntityLayer;
using Store.DataLayer.Contracts;
using Store.EntityLayer.HumanResources;
using Store.EntityLayer.Production;
using Store.EntityLayer.Sales;
using Store.DataLayer.DataContracts;

namespace Store.DataLayer.Repositories
{
 public class SalesRepository : Repository, ISalesRepository
 {
  public SalesRepository(StoreDbContext dbContext)
   : base(dbContext)
  {
  }

  public IQueryable<Customer> GetCustomers()
  {
   // Get query from DbSet
   var query = DbContext.Set<Customer>().AsQueryable();
   
   return query;
  }

  public async Task<Customer> GetCustomerAsync(Customer entity)
   => await DbContext.Set<Customer>().FirstOrDefaultAsync(item => item.CustomerID == entity.CustomerID);

  public async Task<Int32> AddCustomerAsync(Customer entity)
  {
   // Add entity in DbSet
   Add(entity);
   
   // Save changes through DbContext
   return await CommitChangesAsync();
  }

  public async Task<Int32> UpdateCustomerAsync(Customer changes)
  {
   // Update entity in DbSet
   Update(changes);
   
   // Save changes through DbContext
   return await CommitChangesAsync();
  }

  public async Task<Int32> RemoveCustomerAsync(Customer entity)
  {
   // Remove entity from DbSet
   Remove(entity);
   
   // Save changes through DbContext
   return await CommitChangesAsync();
  }

  public IQueryable<CustomerAddress> GetCustomerAddresses(Int32? customerID = null)
  {
   // Get query from DbSet
   var query = DbContext.Set<CustomerAddress>().AsQueryable();
   
   if (customerID.HasValue)
   {
    // Filter by: 'CustomerID'
    query = query.Where(item => item.CustomerID == customerID);
   }
   
   return query;
  }

  public async Task<CustomerAddress> GetCustomerAddressAsync(CustomerAddress entity)
   => await DbContext.Set<CustomerAddress>().FirstOrDefaultAsync(item => item.CustomerAddressID == entity.CustomerAddressID);

  public async Task<Int32> AddCustomerAddressAsync(CustomerAddress entity)
  {
   // Add entity in DbSet
   Add(entity);
   
   // Save changes through DbContext
   return await CommitChangesAsync();
  }

  public async Task<Int32> UpdateCustomerAddressAsync(CustomerAddress changes)
  {
   // Update entity in DbSet
   Update(changes);
   
   // Save changes through DbContext
   return await CommitChangesAsync();
  }

  public async Task<Int32> RemoveCustomerAddressAsync(CustomerAddress entity)
  {
   // Remove entity from DbSet
   Remove(entity);
   
   // Save changes through DbContext
   return await CommitChangesAsync();
  }

  public IQueryable<CustomerEmail> GetCustomerEmails(Int32? customerID = null)
  {
   // Get query from DbSet
   var query = DbContext.Set<CustomerEmail>().AsQueryable();
   
   if (customerID.HasValue)
   {
    // Filter by: 'CustomerID'
    query = query.Where(item => item.CustomerID == customerID);
   }
   
   return query;
  }

  public async Task<CustomerEmail> GetCustomerEmailAsync(CustomerEmail entity)
   => await DbContext.Set<CustomerEmail>().FirstOrDefaultAsync(item => item.CustomerEmailID == entity.CustomerEmailID);

  public async Task<Int32> AddCustomerEmailAsync(CustomerEmail entity)
  {
   // Add entity in DbSet
   Add(entity);
   
   // Save changes through DbContext
   return await CommitChangesAsync();
  }

  public async Task<Int32> UpdateCustomerEmailAsync(CustomerEmail changes)
  {
   // Update entity in DbSet
   Update(changes);
   
   // Save changes through DbContext
   return await CommitChangesAsync();
  }

  public async Task<Int32> RemoveCustomerEmailAsync(CustomerEmail entity)
  {
   // Remove entity from DbSet
   Remove(entity);
   
   // Save changes through DbContext
   return await CommitChangesAsync();
  }

  public IQueryable<OrderDto> GetOrders(Int16? currencyID = null, Int32? customerID = null, Int32? employeeID = null, Int16? orderStatusID = null, Guid? paymentMethodID = null, Int32? shipperID = null)
  {
   // Get query from DbSet
   var query = from order in DbContext.Set<Order>()
    join currencyJoin in DbContext.Set<Currency>() on order.CurrencyID equals currencyJoin.CurrencyID into currencyTemp
     from currency in currencyTemp.DefaultIfEmpty()
    join customer in DbContext.Set<Customer>() on order.CustomerID equals customer.CustomerID
    join employeeJoin in DbContext.Set<Employee>() on order.EmployeeID equals employeeJoin.EmployeeID into employeeTemp
     from employee in employeeTemp.DefaultIfEmpty()
    join orderStatus in DbContext.Set<OrderStatus>() on order.OrderStatusID equals orderStatus.OrderStatusID
    join paymentMethodJoin in DbContext.Set<PaymentMethod>() on order.PaymentMethodID equals paymentMethodJoin.PaymentMethodID into paymentMethodTemp
     from paymentMethod in paymentMethodTemp.DefaultIfEmpty()
    join shipperJoin in DbContext.Set<Shipper>() on order.ShipperID equals shipperJoin.ShipperID into shipperTemp
     from shipper in shipperTemp.DefaultIfEmpty()
    select new OrderDto
    {
     OrderID = order.OrderID,
     OrderStatusID = order.OrderStatusID,
     CustomerID = order.CustomerID,
     EmployeeID = order.EmployeeID,
     ShipperID = order.ShipperID,
     OrderDate = order.OrderDate,
     Total = order.Total,
     CurrencyID = order.CurrencyID,
     PaymentMethodID = order.PaymentMethodID,
     Comments = order.Comments,
     CreationUser = order.CreationUser,
     CreationDateTime = order.CreationDateTime,
     LastUpdateUser = order.LastUpdateUser,
     LastUpdateDateTime = order.LastUpdateDateTime,
     Timestamp = order.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,
    };
   
   if (currencyID.HasValue)
   {
    // Filter by: 'CurrencyID'
    query = query.Where(item => item.CurrencyID == currencyID);
   }
   
   if (customerID.HasValue)
   {
    // Filter by: 'CustomerID'
    query = query.Where(item => item.CustomerID == customerID);
   }
   
   if (employeeID.HasValue)
   {
    // Filter by: 'EmployeeID'
    query = query.Where(item => item.EmployeeID == employeeID);
   }
   
   if (orderStatusID.HasValue)
   {
    // Filter by: 'OrderStatusID'
    query = query.Where(item => item.OrderStatusID == orderStatusID);
   }
   
   if (paymentMethodID.HasValue)
   {
    // Filter by: 'PaymentMethodID'
    query = query.Where(item => item.PaymentMethodID == paymentMethodID);
   }
   
   if (shipperID.HasValue)
   {
    // Filter by: 'ShipperID'
    query = query.Where(item => item.ShipperID == shipperID);
   }
   
   return query;
  }

  public async Task<Order> GetOrderAsync(Order entity)
  {
   return await DbContext.Set<Order>()
    .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.OrderID == entity.OrderID);
  }

  public async Task<Int32> AddOrderAsync(Order entity)
  {
   // Add entity in DbSet
   Add(entity);
   
   // Save changes through DbContext
   return await CommitChangesAsync();
  }

  public async Task<Int32> UpdateOrderAsync(Order changes)
  {
   // Update entity in DbSet
   Update(changes);
   
   // Save changes through DbContext
   return await CommitChangesAsync();
  }

  public async Task<Int32> RemoveOrderAsync(Order entity)
  {
   // Remove entity from DbSet
   Remove(entity);
   
   // Save changes through DbContext
   return await CommitChangesAsync();
  }

  public IQueryable<OrderDetail> GetOrderDetails(Int64? orderID = null, Int32? productID = null)
  {
   // Get query from DbSet
   var query = DbContext.Set<OrderDetail>().AsQueryable();
   
   if (orderID.HasValue)
   {
    // Filter by: 'OrderID'
    query = query.Where(item => item.OrderID == orderID);
   }
   
   if (productID.HasValue)
   {
    // Filter by: 'ProductID'
    query = query.Where(item => item.ProductID == productID);
   }
   
   return query;
  }

  public async Task<OrderDetail> GetOrderDetailAsync(OrderDetail entity)
   => await DbContext.Set<OrderDetail>().FirstOrDefaultAsync(item => item.OrderDetailID == entity.OrderDetailID);

  public async Task<OrderDetail> GetOrderDetailByOrderIDAndProductIDAsync(OrderDetail entity)
   => await DbContext.Set<OrderDetail>().FirstOrDefaultAsync(item => item.OrderID == entity.OrderID && item.ProductID == entity.ProductID);

  public async Task<Int32> AddOrderDetailAsync(OrderDetail entity)
  {
   // Add entity in DbSet
   Add(entity);
   
   // Save changes through DbContext
   return await CommitChangesAsync();
  }

  public async Task<Int32> UpdateOrderDetailAsync(OrderDetail changes)
  {
   // Update entity in DbSet
   Update(changes);
   
   // Save changes through DbContext
   return await CommitChangesAsync();
  }

  public async Task<Int32> RemoveOrderDetailAsync(OrderDetail entity)
  {
   // Remove entity from DbSet
   Remove(entity);
   
   // Save changes through DbContext
   return await CommitChangesAsync();
  }

  public IQueryable<OrderStatus> GetOrderStatuses()
  {
   // Get query from DbSet
   var query = DbContext.Set<OrderStatus>().AsQueryable();
   
   return query;
  }

  public async Task<OrderStatus> GetOrderStatusAsync(OrderStatus entity)
   => await DbContext.Set<OrderStatus>().FirstOrDefaultAsync(item => item.OrderStatusID == entity.OrderStatusID);

  public async Task<Int32> AddOrderStatusAsync(OrderStatus entity)
  {
   // Add entity in DbSet
   Add(entity);
   
   // Save changes through DbContext
   return await CommitChangesAsync();
  }

  public async Task<Int32> UpdateOrderStatusAsync(OrderStatus changes)
  {
   // Update entity in DbSet
   Update(changes);
   
   // Save changes through DbContext
   return await CommitChangesAsync();
  }

  public async Task<Int32> RemoveOrderStatusAsync(OrderStatus entity)
  {
   // Remove entity from DbSet
   Remove(entity);
   
   // Save changes through DbContext
   return await CommitChangesAsync();
  }

  public IQueryable<PaymentMethod> GetPaymentMethods()
  {
   // Get query from DbSet
   var query = DbContext.Set<PaymentMethod>().AsQueryable();
   
   return query;
  }

  public async Task<PaymentMethod> GetPaymentMethodAsync(PaymentMethod entity)
   => await DbContext.Set<PaymentMethod>().FirstOrDefaultAsync(item => item.PaymentMethodID == entity.PaymentMethodID);

  public async Task<Int32> AddPaymentMethodAsync(PaymentMethod entity)
  {
   // Set value for GUID
   entity.PaymentMethodID = Guid.NewGuid();
   
   // Add entity in DbSet
   Add(entity);
   
   // Save changes through DbContext
   return await CommitChangesAsync();
  }

  public async Task<Int32> UpdatePaymentMethodAsync(PaymentMethod changes)
  {
   // Update entity in DbSet
   Update(changes);
   
   // Save changes through DbContext
   return await CommitChangesAsync();
  }

  public async Task<Int32> RemovePaymentMethodAsync(PaymentMethod entity)
  {
   // Remove entity from DbSet
   Remove(entity);
   
   // Save changes through DbContext
   return await CommitChangesAsync();
  }

  public IQueryable<Shipper> GetShippers()
  {
   // Get query from DbSet
   var query = DbContext.Set<Shipper>().AsQueryable();
   
   return query;
  }

  public async Task<Shipper> GetShipperAsync(Shipper entity)
   => await DbContext.Set<Shipper>().FirstOrDefaultAsync(item => item.ShipperID == entity.ShipperID);

  public async Task<Int32> AddShipperAsync(Shipper entity)
  {
   // Add entity in DbSet
   Add(entity);
   
   // Save changes through DbContext
   return await CommitChangesAsync();
  }

  public async Task<Int32> UpdateShipperAsync(Shipper changes)
  {
   // Update entity in DbSet
   Update(changes);
   
   // Save changes through DbContext
   return await CommitChangesAsync();
  }

  public async Task<Int32> RemoveShipperAsync(Shipper entity)
  {
   // Remove entity from DbSet
   Remove(entity);
   
   // Save changes through DbContext
   return await CommitChangesAsync();
  }

  public IQueryable<OrderSummary> GetOrderSummaries()
   => DbContext.Set<OrderSummary>();
 }
}

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 use 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 is the list of entities that will be generated with joins in linq, this means CatFactory engine reads all foreign keys and create a data contract to retrieve information, if we take a look on GetOrders method on SalesRepository, we can see a linq query with data contract and not a lambda expression as GetShippers method

EnableDataBindings this flag implements INotifyPropertyChanged in all entities that represent tables

Please take a look on all operations in repositories, all 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 on those settings:

Name Default Value Description
ForceOverwrite false Indicates if code builder must overwrite files if they already exist
SimplifyDataTypes false 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 contains private fields
UseBackingFields false Enables the using of fields for change tracking in entities instances materialization
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 to contains declaration of DbSet
DeclareNavigationPropertiesAsVirtual false Indicates if navigation propeties must to declare 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 respresents 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   Sets the list of entities must to generate as data transfer objects in repositories

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
Mapping Mapping Gets or sets the namespace for mapping 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

Features List

CatFactory Features
Layer Feature Supported
Database SQL Server Database import Yes
Entity layer POCOs scaffolding for tables and views Yes
Entity layer Addition of Navigation properties for entities Yes
Data layer Data annotations for POCOs Yes
Data layer DbContext scaffolding Yes
Data layer DbSet properties in DbContext Yes
Data layer Mappings scaffolding Yes
Data layer Contracts scaffolding Yes
Data layer Data Contracts scaffolding Yes
Data layer Add invocations for stored procedures Not yet
Data layer Repositories scaffolding Yes
Data layer Async operations for Repositories Yes
Back-end Scaffold API controllers Not yet
QA Scaffold unit tests for API controllers Not yet

How to Use CatFactory Framework

We need to understand the scope for CatFactory, in few words CatFactory is the core to scaffolding, if we want to have more packages we can create them with this naming convention: CatFactory.PackageName.

As we can see above the basic flow for existing database on CatFactory is this:

  1. Set connection string
  2. Import database
  3. Create instance of Entity Framework Core project
  4. Build features (One feature per schema)
  5. Scaffold code for entity layer and data layer, these methods read all tables and views from database and create instances for code builders

We'll work on common tasks with CatFactory, these will apply once we have a database imported from database server or we have created a database in memory.

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 resolver = new ClrTypeResolver();
        
        var clrType = resolver.Resolve(column.Type);
    }
}

Workshop

This section allows to expose the new concepts for CatFactory andin order to know if they are "good" to improve user experience, please feel free to let me know your feedback about what do you think about this improvements :)

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:

Event Handlers to Scaffold

In order to provide a more flexible way in scaffolding, there are two delegates in CatFactory, one to perform an action before of scaffolding and another one to handle and action after of scaffolding.

Code Sample:

// Add event handlers to before and after of scaffold

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

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

Database Type Map

One of things I don't like to get equivalent between SQL data type for CLR is use magic strings, after of review the more "fancy" way to resolve a type equivalence is to have a class that allows to know the equivalence between SQL data type and CLR type.

Using this table as reference, now CatFactory has a class with name DatabaseTypeMap. Database class contains a property with all mappings with name Mappings, so this property is filled by Import feature for SQL Server package.

Class Definition:

public class DatabaseTypeMap
{
    public DatabaseTypeMap()
    {
    }

    public string DatabaseType { get; set; }

    public bool AllowsLengthInDeclaration { get; set; }

    public bool AllowsPrecInDeclaration { get; set; }

    public bool AllowsScaleInDeclaration { get; set; }

    public string ClrFullNameType { get; set; }

    public bool HasClrFullNameType
        => !string.IsNullOrEmpty(ClrFullNameType);

    [XmlIgnore]
    public Type ClrType
        => HasClrFullNameType ? Type.GetType(ClrFullNameType) : null;

    public string ClrAliasType { get; set; }

    public bool HasClrAliasType
        => !string.IsNullOrEmpty(ClrAliasType);

    public bool AllowClrNullable { get; set; }

    public DbType DbTypeEnum { get; set; }

    public bool IsUserDefined { get; set; }

    public string ParentDatabaseType { get; set; }
}

Code Sample:

// Get mappings
var mappings = DatabaseTypeMapList.Definition;

// Resolve CLR type
var mapsForString = mappings.Where(item => item.ClrType == typeof(string)).ToList();

// Resolve SQL Server type
var mapForVarchar = mappings.FirstOrDefault(item => item.DatabaseType == "varchar");

Project Selection

A project selection is a limit to apply settings for objects match with pattern.

GlobalSelection is the default selection for project, contains a default instance of settings.

Patterns:

Pattern Scope
Sales.Order Applies for specific object with name Sales.Order
Sales.* Applies for all objects inside of Sales schema
*.Order Applies for all objects with name Order with no matter schema
*.* Applies for all objects, this is the global selection

Code Sample:

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

// Apply settings for specific object
project.Select("Sales.Order", settings =>
{
    settings.ForceOverwrite = true;
    settings.AuditEntity = new AuditEntity("CreationUser", "CreationDateTime", "LastUpdateUser", "LastUpdateDateTime");
    settings.ConcurrencyToken = "Timestamp";
    settings.EntitiesWithDataContracts = true;
});

Points of Interest

  • CatFactory has a package for Dapper, at this moment there isn't article for that but the way to use is similar for Entity Framework Core; you can install CatFactory.Dapper package from nuget
  • 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 is more simple 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 an 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 :)
  • We're working in continuous updates to provide better help for user

Related Links

Code Improvements

  • Allow to overwrite naming convention for project
  • Add author's information for output files

Trivia

  • The name for this framework it was F4N1 before than CatFactory
  • Framework's name is related to kitties
  • Import logic uses sp_help stored procedure to retrieve the database object's definition, I learned that in my database course at college
  • Load mapping for entities with MEF, it's inspired in "OdeToCode" (Scott Allen) article for Entity Framework 6.x
  • Expose all settings in one class inside of project's definition is inspired on DevExpress settings for Web controls (Web Forms)
  • There are three alpha versions for CatFactory as reference for Street Fighter Alpha fighting game.
  • There will be two beta versions for CatFactory, the first with name Sun and second one with name Moon as reference for characters from The King of Fighters fighting game: Kusanagi Kyo and Yagami Iori.

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
  • 31th October, 2017: Update to alpha 3 version
  • 21th November, 2017: Addition of Trivia
  • 18th January, 2018: Addition of Workshop
  • 30th Abril, 2018: Update package to beta version
  • 16th May, 2018: Add support for EF Core 2

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
United States United States
Full Stack Developer with Experience in ASP.NET Core and Angular

You may also be interested in...

Comments and Discussions

 
SuggestionImprovement and suggestions Pin
Member 965484630-Nov-17 0:23
memberMember 965484630-Nov-17 0:23 
GeneralRe: Improvement and suggestions Pin
HHerzl18-Dec-17 17:03
memberHHerzl18-Dec-17 17:03 
PraiseRe: Improvement and suggestions Pin
Member 965484620-Dec-17 7:08
memberMember 965484620-Dec-17 7:08 
BugPrimary keys Pin
Member 965484629-Nov-17 0:25
memberMember 965484629-Nov-17 0:25 
GeneralRe: Primary keys Pin
HHerzl3-Dec-17 17:39
memberHHerzl3-Dec-17 17:39 
GeneralRe: Primary keys Pin
Member 96548464-Dec-17 7:11
memberMember 96548464-Dec-17 7:11 
GeneralRe: Primary keys Pin
HHerzl5-Dec-17 2:19
memberHHerzl5-Dec-17 2:19 
GeneralRe: Primary keys Pin
HHerzl18-Dec-17 13:26
memberHHerzl18-Dec-17 13:26 
Bugviews Pin
Member 965484628-Nov-17 23:50
memberMember 965484628-Nov-17 23:50 
GeneralRe: views Pin
HHerzl3-Dec-17 17:33
memberHHerzl3-Dec-17 17:33 
GeneralRe: views Pin
Member 96548464-Dec-17 7:13
memberMember 96548464-Dec-17 7:13 
BugAn other issue Pin
Member 965484628-Nov-17 21:45
memberMember 965484628-Nov-17 21:45 
GeneralRe: An other issue Pin
HHerzl5-Dec-17 2:14
memberHHerzl5-Dec-17 2:14 
GeneralRe: An other issue Pin
HHerzl18-Dec-17 13:25
memberHHerzl18-Dec-17 13:25 
QuestionGreat Job :-) Pin
Member 96548465-Nov-17 23:27
memberMember 96548465-Nov-17 23:27 
AnswerRe: Great Job :-) Pin
HHerzl7-Nov-17 16:57
memberHHerzl7-Nov-17 16:57 
AnswerRe: Great Job :-) Pin
HHerzl8-Nov-17 21:47
memberHHerzl8-Nov-17 21:47 
PraiseRe: Great Job :-) Pin
Member 96548468-Nov-17 23:38
memberMember 96548468-Nov-17 23:38 
GeneralRe: Great Job :-) Pin
HHerzl20-Nov-17 14:11
memberHHerzl20-Nov-17 14:11 
PraiseRe: Great Job :-) Pin
Member 965484623-Nov-17 23:54
memberMember 965484623-Nov-17 23:54 
GeneralRe: Great Job :-) Pin
Member 965484628-Nov-17 21:48
memberMember 965484628-Nov-17 21:48 
QuestionGreat article & concept, but a little problem Pin
dkurok5-Nov-17 1:58
memberdkurok5-Nov-17 1:58 
AnswerSolved: Great article & concept, but a little problem Pin
dkurok5-Nov-17 2:50
memberdkurok5-Nov-17 2:50 
AnswerRe: Great article & concept, but a little problem Pin
HHerzl7-Nov-17 5:39
memberHHerzl7-Nov-17 5:39 
QuestionI like the concept.. Pin
Keith Stone2-Nov-17 3:55
memberKeith Stone2-Nov-17 3:55 

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.

Permalink | Advertise | Privacy | Cookies | Terms of Use | Mobile
Web02-2016 | 2.8.180712.1 | Last Updated 16 May 2018
Article Copyright 2016 by HHerzl
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid