Menu
Project solution file has two C# projects. Api project consists of backend api capabilities and Schema project has DTOs.
dotnet new Fi.Template.Api -n Fi.YourProject.Api -o Fi.YourProject.Api –ModuleName YourProject –Database Relational –StartErrorCode 8000 –EndErrorCode 8500 –LocalHostPort 10099
dotnet new Fi.Template.Schema -n Fi.YourProject.Schema -o Fi.YourProject.Schema
To reach the internal services(database, redis, minio etc.) of environments as localhost you need to follow these steps.
Kubernetes port forwarding sample command is like below. Most common commands.
kubectl port-forward services/fi-mssql 1433:1433 -n data
kubectl port-forward services/fi-redis-master 6379:6379 -n data
kubectl port-forward services/fi-camunda-zeebe-gateway 26500:26500 -n camunda
kubectl port-forward services/fi-minio 9000:9000 -n data
Partner which has static IP can connect to the database over the IP address that is given by Fimple devops teams.
Fimple uses Rancher to manage the clusters on Kubernetes. It is a complete software stack for teams adopting containers. It addresses the operational and security challenges of managing multiple Kubernetes clusters across any infrastructure, while providing DevOps teams with integrated tools for running containerized workloads.
Fimple uses VSCode to develop frontend and backend software.
For Windows user visual studio can be used.
Fimple developers use tools below. One of them can be chosen.
For Windows developers Sql Server Management Studio can be used.
Fimple uses Git and Azure Devops as a source code repository. As a tool one of them can be chosen.
Before start development basic git commands should be known. Click here to see git fundamental commands.
Developers cannot push code changes directly to the main and master branch.
Create a local branch from main branch
Push the local branch to remote with same name
Make some changes like new feature or bug fixing
Create a merge request and assign it an eligible approver
Create a local branch from master branch
Cherry-pick your changes into the local branch
Push the local branch to remote with same name
Create a merge request and assign it an eligible approver
Fimple has microservice architecture and uses .Net Core 6.0.
Fimple architecture supports On-Premises, Hybrid and Cloud installations.
To reach the internal services(database, redis, minio etc.) of environments as localhost you need to follow these steps.
Kubernetes port forwarding sample command is like below. Most common commands.
kubectl port-forward services/fi-mssql 1433:1433 -n data
kubectl port-forward services/fi-redis-master 6379:6379 -n data
kubectl port-forward services/fi-camunda-zeebe-gateway 26500:26500 -n camunda
kubectl port-forward services/fi-minio 9000:9000 -n data
Partner which has static IP can connect to the database over the IP address that is given by Fimple devops teams.
Fimple uses Rancher to manage the clusters on Kubernetes. It is a complete software stack for teams adopting containers. It addresses the operational and security challenges of managing multiple Kubernetes clusters across any infrastructure, while providing DevOps teams with integrated tools for running containerized workloads.
Fimple uses VSCode to develop frontend and backend software.
For Windows user visual studio can be used.
Fimple uses EF Core for ORM with the code first approach.
Fimple uses EF Migration to update the database schema to keep it in sync with the application’s data model while preserving existing data in the database. Click here for more details.
After creating a new Api project and creating required entities you need to prepare your Seed Data and add Initial Migration with dotnet ef commands. This Initial Migration and Seed Data is being updated to the all tenant DB’s in the environment by the pipeline when it is deployed.
After Initial Migration updated in the DBs, you must create Update (Feature) Migrations for DB related code changes like entity update. These update (feature) migrations are also being updated to the all tenant DB’s in the environment by the pipeline when it is deployed.
In the ci-cd pipe of fi.src project, dotnet ef idempotent script generation is used to generate script of all ef core migrations if there is any change under migrations folder of the project.
This generated file Fi.ServiceName.Api.sql is pushed to Migrations/Output folder, to main branch of the project.
As a last step Fi.Util exe is called to execute all migrations.
How Fi.Util exe works;
After you define the initial domain classes and make your project testable than create an initial migration.
dotnet ef migrations add InitialMigration
Then update database with your initial migration (do this only for local database, this command is run by pipeline for environment DBs).
dotnet ef database update
You can use this command whenever you want. This command gets all the initial and update (feature) migrations and applies them to the DB.
There shoul be only one initial migration on the Api project. After updating database, your newly entity changes need a new migration and a new update to the database. Create an update (feature) migration with a well migration name which is clear for your change.
dotnet ef migrations add ChangingCustomerAddressModels
dotnet ef database update
Whenever you merge your code to main, pipeline runs dotnet ef database update command and this command updates related environment DB.
Add your Initial Seed Data into Migrations/Seed/InitialSeed.sql file. Do not put begin tran and commit tran blocks inside these sqls ( forbidden ).
Add this .sql to the end of protected override void Up(MigrationBuilder migrationBuilder) method in date_InitialCreate.cs migration file (or date_InitialMigration.cs).
When you want to add new definitions or seed data related to your change, add another UpdateSeed.sql file and again add this .sql to the end of protected override void Up( MigrationBuilder migrationBuilder) method in update (feature) migration file (sample: date_UpdateMigrationForGenderTypes.cs).
If you need to separate migration data for Test and Production Environment
You can added Test Data to Migrations/Seed/InitialSeedTestExtended.sql file.
Add this .sql to the end of protected override void Up(MigrationBuilder migrationBuilder) method in date_InitialCreate.cs migration file.
Up(MigrationBuilder migrationBuilder) Method Override Code Block
migrationBuilder.Sql(System.IO.File.ReadAllText(System.IO.Path.Combine(AppContext.BaseDirectory, “Migrations/Seed/InitialSeed.sql”)));
var currentEnvironment = Environment.GetEnvironmentVariable(“ASPNETCORE_ENVIRONMENT”);
if (currentEnvironment != “Production”)
{
migrationBuilder.Sql(System.IO.File.ReadAllText(System.IO.Path.Combine(AppContext.BaseDirectory, “Migrations/Seed/InitialSeedTestExtended.sql”)));
}
Add ItemGroup to project .sln for copy .sql files to output directory.
project .sln ItemGroup Code Block
<ItemGroup>
<None Include=”Migrations\Seed\InitialSeed.sql” Condition=”‘$(ExcludeConfigFilesFromBuildOutput)’!=’true'”>
<ExcludeFromSingleFile>true</ExcludeFromSingleFile>
<CopyToOutputDirectory>Always</CopyToOutputDirectory>
<CopyToPublishDirectory>PreserveNewest</CopyToPublishDirectory>
</None>
<None Include=”Migrations\Seed\InitialSeedTestExtended.sql” Condition=”‘$(ExcludeConfigFilesFromBuildOutput)’!=’true'”>
<ExcludeFromSingleFile>true</ExcludeFromSingleFile>
<CopyToOutputDirectory>Always</CopyToOutputDirectory>
<CopyToPublishDirectory>PreserveNewest</CopyToPublishDirectory>
</None>
</ItemGroup>
Add flag “IsEFMigrationEnabled” : true to appsettings.json.
This flag is temporary. Should be set in order to “dotnet-ef” command can run.
Note: Make sure your Seed sql files has where clauses to prevent from duplications.
Warning: When you change the length of a property, make sure your migration is working fine and you do not cause data lose. To prevent data lose, you can add manual sql scripts to the end of the protected override void Up(MigrationBuilder migrationBuilder) method in related migration file.
Important Note: As standart, all the string fields are nvarchar and Unicode. So in seed data, you need to add N sign before string fields to save the value as Unicode to the database. Otherwise your data will not be saved as Unicode and you lose data.
As explained above, for moving new definitions from Development Environment to Production Environment, we are using seed sql files. To generate seed sql scripts easily for a table or for new records, you can use sp_generate_table_seed and sp_generate_detailtable_seed stored procedures in your development db.
exec [dbo].[sp_generate_table_seed] ‘SlipDefinition’, ‘Id’, ‘Code’
exec [dbo].[sp_generate_detailtable_seed] ‘SlipDefinitionDetail’, ‘Id’, ‘SlipDefinitionId’, ‘Order’, ‘SlipDefinition’, ‘Id’, ‘Code’
By using sp_generate_table_seed proc, you generate a script like below for a table:
IF NOT EXISTS (SELECT * FROM dbo.SlipDefinition WHERE Code=’CashDeposit’)
BEGIN
INSERT INTO dbo.SlipDefinition([Code],[CreatedBy],[CreateTime],[Description],[IsGenerateSerialNumber],[IsLatestVersion],[LanguageId],[Name],[UpdateCount],[UpdatedBy],[UpdateTime],[Version])
VALUES (N’CashDeposit’, N’system’, GETDATE(), N’Cash Deposit Slip’, 0, 1, 41, N’CashDeposit’, 0, NULL, NULL, 1)
END
ELSE
BEGIN
UPDATE dbo.SlipDefinition SET [Code] = N’CashDeposit’, [Description] = N’Cash Deposit Slip’, [IsGenerateSerialNumber] = 0, [IsLatestVersion] = 1, [LanguageId] = 41, [Name] = N’CashDeposit’, [UpdateCount] = UpdateCount + 1, [UpdatedBy] = ‘system’, [UpdateTime] = GETDATE(), [Version] = 1
WHERE Code=’CashDeposit’
END
If you are generating records of a detail table, you must use sp_generate_detailtable_seed and this proc generates a script like below:
— ********** Code(CashDeposit). Deleting all old details in the table and inserting new ones for this record. **********
DELETE dbo.SlipDefinitionDetail
FROM dbo.SlipDefinitionDetail d
INNER JOIN dbo.SlipDefinition m ON m.Id=d.SlipDefinitionId
WHERE m.Code=’CashDeposit’
DECLARE @masterTableIdValueForCashDeposit INT
SELECT @masterTableIdValueForCashDeposit=Id FROM dbo.SlipDefinition WHERE Code=’CashDeposit’
INSERT INTO dbo.SlipDefinitionDetail([Area],[CreatedBy],[CreateTime],[Label],[Order],[SlipDefinitionId],[Tags],[UpdateCount],[UpdatedBy],[UpdateTime],[Value])
VALUES (N’Title’, N’system’, GETDATE(), N’Title’, 1, @masterTableIdValueForCashDeposit, NULL, 0, NULL, NULL, N’Cash Deposit’)
INSERT INTO dbo.SlipDefinitionDetail([Area],[CreatedBy],[CreateTime],[Label],[Order],[SlipDefinitionId],[Tags],[UpdateCount],[UpdatedBy],[UpdateTime],[Value])
VALUES (N’LeftHeader’, N’system’, GETDATE(), N’Customer Number’, 2, @masterTableIdValueForCashDeposit, NULL, 0, NULL, NULL, N’#c.Cus.Id’)
INSERT INTO dbo.SlipDefinitionDetail([Area],[CreatedBy],[CreateTime],[Label],[Order],[SlipDefinitionId],[Tags],[UpdateCount],[UpdatedBy],[UpdateTime],[Value])
VALUES (N’LeftHeader’, N’system’, GETDATE(), N’Account Number’, 3, @masterTableIdValueForCashDeposit, NULL, 0, NULL, NULL, N’#c.C.AccountNumber’)
INSERT INTO dbo.SlipDefinitionDetail([Area],[CreatedBy],[CreateTime],[Label],[Order],[SlipDefinitionId],[Tags],[UpdateCount],[UpdatedBy],[UpdateTime],[Value])
VALUES (N’LeftHeader’, N’system’, GETDATE(), N’Transaction Date’, 4, @masterTableIdValueForCashDeposit, NULL, 0, NULL, NULL, N’#m.Today’)
INSERT INTO dbo.SlipDefinitionDetail([Area],[CreatedBy],[CreateTime],[Label],[Order],[SlipDefinitionId],[Tags],[UpdateCount],[UpdatedBy],[UpdateTime],[Value])
VALUES (N’LeftHeader’, N’system’, GETDATE(), N’Identity Number’, 5, @masterTableIdValueForCashDeposit, NULL, 0, NULL, NULL, N’#c.Cus.CustomerIdentifications[0].IdentityNumber’)
INSERT INTO dbo.SlipDefinitionDetail([Area],[CreatedBy],[CreateTime],[Label],[Order],[SlipDefinitionId],[Tags],[UpdateCount],[UpdatedBy],[UpdateTime],[Value])
VALUES (N’LeftHeader’, N’system’, GETDATE(), N’Issue Date’, 6, @masterTableIdValueForCashDeposit, NULL, 0, NULL, NULL, N’#m.Today’)
INSERT INTO dbo.SlipDefinitionDetail([Area],[CreatedBy],[CreateTime],[Label],[Order],[SlipDefinitionId],[Tags],[UpdateCount],[UpdatedBy],[UpdateTime],[Value])
VALUES (N’LeftHeader’, N’system’, GETDATE(), N’Reference’, 7, @masterTableIdValueForCashDeposit, NULL, 0, NULL, NULL, N’#m.BusinessKey’)
INSERT INTO dbo.SlipDefinitionDetail([Area],[CreatedBy],[CreateTime],[Label],[Order],[SlipDefinitionId],[Tags],[UpdateCount],[UpdatedBy],[UpdateTime],[Value])
VALUES (N’RightHeader’, N’system’, GETDATE(), N’Dear’, 8, @masterTableIdValueForCashDeposit, NULL, 0, NULL, NULL, N’#c.Cus.Name’)
INSERT INTO dbo.SlipDefinitionDetail([Area],[CreatedBy],[CreateTime],[Label],[Order],[SlipDefinitionId],[Tags],[UpdateCount],[UpdatedBy],[UpdateTime],[Value])
VALUES (N’RightHeader’, N’system’, GETDATE(), N’Address’, 9, @masterTableIdValueForCashDeposit, NULL, 0, NULL, NULL, N’Istanbul’)
INSERT INTO dbo.SlipDefinitionDetail([Area],[CreatedBy],[CreateTime],[Label],[Order],[SlipDefinitionId],[Tags],[UpdateCount],[UpdatedBy],[UpdateTime],[Value])
VALUES (N’LeftBody’, N’system’, GETDATE(), N’Sender Name’, 10, @masterTableIdValueForCashDeposit, NULL, 0, NULL, NULL, N’#c.Cus.Name’)
INSERT INTO dbo.SlipDefinitionDetail([Area],[CreatedBy],[CreateTime],[Label],[Order],[SlipDefinitionId],[Tags],[UpdateCount],[UpdatedBy],[UpdateTime],[Value])
VALUES (N’LeftBody’, N’system’, GETDATE(), N’Location’, 11, @masterTableIdValueForCashDeposit, NULL, 0, NULL, NULL, N’Mobile Branch’)
INSERT INTO dbo.SlipDefinitionDetail([Area],[CreatedBy],[CreateTime],[Label],[Order],[SlipDefinitionId],[Tags],[UpdateCount],[UpdatedBy],[UpdateTime],[Value])
VALUES (N’LeftBody’, N’system’, GETDATE(), N’Comment’, 12, @masterTableIdValueForCashDeposit, NULL, 0, NULL, NULL, N’#c.C.Comment’)
INSERT INTO dbo.SlipDefinitionDetail([Area],[CreatedBy],[CreateTime],[Label],[Order],[SlipDefinitionId],[Tags],[UpdateCount],[UpdatedBy],[UpdateTime],[Value])
VALUES (N’BottomFooter’, N’system’, GETDATE(), N’Amount’, 13, @masterTableIdValueForCashDeposit, N’Amount’, 0, NULL, NULL, N’#c.C.Amount’)
INSERT INTO dbo.SlipDefinitionDetail([Area],[CreatedBy],[CreateTime],[Label],[Order],[SlipDefinitionId],[Tags],[UpdateCount],[UpdatedBy],[UpdateTime],[Value])
VALUES (N’BottomFooter’, N’system’, GETDATE(), N’Currency’, 14, @masterTableIdValueForCashDeposit, NULL, 0, NULL, NULL, N’#c.C.CurrencyCode’)
LookupDbContext is used for reading other services most demanded data which is copied to a LookupDb which is stored next to the other Dbs in MsSql instance. LookupDbContext also eliminates needs of defining lots of Change Data Capture connectors.
If you need to copy data that belongs to another microservice, you can create a trigger than you can get data as a readonly from lookup api db by using LookUpDbContext. Adding new entity to LookupDb needs approval from Fimple Infra Team.
public class FiLookupDbContext : FiBaseLookupDbContext
{
public FiLookupDbContext(FiOptionsSingleton fiOptionsSingleton, ISessionContextDI sessionContextDi)
: base(fiOptionsSingleton, sessionContextDi)
{
}
public DbSet<CustomerLookup> CustomerLookups { get; set; }
public DbSet<CustomerCommunicationLookup> CustomerCommunicationLookups { get; set; }
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
var sqlServerOptions = optionsBuilder.UseSqlServer(DefaultLookupConnectionString
, sqlServerOptions =>
{
sqlServerOptions.CommandTimeout(TimeoutConstants.DefaultSqlServerCommandTimeout);
sqlServerOptions.EnableRetryOnFailure();
});
}
}
! Note: You do not need to add entity and configuration file to LookupDbContext ModelBuilder. These are will be added automatically.
dotnet ef migrations add test –startup-project Fi.Catalog.Api.csproj –context FiCatalogDbContext
! Note !: LookupDbContext should be use only for readonly operations. Do not save any changes.
! Note !: DO NOT CREATE A MIGRATION by using LookupDbContext! Note !: Do not forget copy old data to Lookup database
Project solution file has two C# projects. Api project consists of backend api capabilities and Schema project has DTOs.
dotnet new Fi.Template.Api -n Fi.YourProject.Api -o Fi.YourProject.Api –ModuleName YourProject –Database Relational –StartErrorCode 8000 –EndErrorCode 8500 –LocalHostPort 10099
dotnet new Fi.Template.Schema -n Fi.YourProject.Schema -o Fi.YourProject.Schema
Fimple uses EF Core for ORM with the code first approach.
Fimple uses EF Migration to update the database schema to keep it in sync with the application’s data model while preserving existing data in the database. Click here for more details.
After creating a new Api project and creating required entities you need to prepare your Seed Data and add Initial Migration with dotnet ef commands. This Initial Migration and Seed Data is being updated to the all tenant DB’s in the environment by the pipeline when it is deployed.
After Initial Migration updated in the DBs, you must create Update (Feature) Migrations for DB related code changes like entity update. These update (feature) migrations are also being updated to the all tenant DB’s in the environment by the pipeline when it is deployed.
In the ci-cd pipe of fi.src project, dotnet ef idempotent script generation is used to generate script of all ef core migrations if there is any change under migrations folder of the project.
This generated file Fi.ServiceName.Api.sql is pushed to Migrations/Output folder, to main branch of the project.
As a last step Fi.Util exe is called to execute all migrations.
How Fi.Util exe works;
After you define the initial domain classes and make your project testable than create an initial migration.
dotnet ef migrations add InitialMigration
Then update database with your initial migration (do this only for local database, this command is run by pipeline for environment DBs).
dotnet ef database update
You can use this command whenever you want. This command gets all the initial and update (feature) migrations and applies them to the DB.
There shoul be only one initial migration on the Api project. After updating database, your newly entity changes need a new migration and a new update to the database. Create an update (feature) migration with a well migration name which is clear for your change.
dotnet ef migrations add ChangingCustomerAddressModels
dotnet ef database update
Whenever you merge your code to main, pipeline runs dotnet ef database update command and this command updates related environment DB.
Add your Initial Seed Data into Migrations/Seed/InitialSeed.sql file. Do not put begin tran and commit tran blocks inside these sqls ( forbidden ).
Add this .sql to the end of protected override void Up(MigrationBuilder migrationBuilder) method in date_InitialCreate.cs migration file (or date_InitialMigration.cs).
When you want to add new definitions or seed data related to your change, add another UpdateSeed.sql file and again add this .sql to the end of protected override void Up( MigrationBuilder migrationBuilder) method in update (feature) migration file (sample: date_UpdateMigrationForGenderTypes.cs).
If you need to separate migration data for Test and Production Environment
You can added Test Data to Migrations/Seed/InitialSeedTestExtended.sql file.
Add this .sql to the end of protected override void Up(MigrationBuilder migrationBuilder) method in date_InitialCreate.cs migration file.
Up(MigrationBuilder migrationBuilder) Method Override Code Block
migrationBuilder.Sql(System.IO.File.ReadAllText(System.IO.Path.Combine(AppContext.BaseDirectory, “Migrations/Seed/InitialSeed.sql”)));
var currentEnvironment = Environment.GetEnvironmentVariable(“ASPNETCORE_ENVIRONMENT”);
if (currentEnvironment != “Production”)
{
migrationBuilder.Sql(System.IO.File.ReadAllText(System.IO.Path.Combine(AppContext.BaseDirectory, “Migrations/Seed/InitialSeedTestExtended.sql”)));
}
Add ItemGroup to project .sln for copy .sql files to output directory.
project .sln ItemGroup Code Block
<ItemGroup>
<None Include=”Migrations\Seed\InitialSeed.sql” Condition=”‘$(ExcludeConfigFilesFromBuildOutput)’!=’true'”>
<ExcludeFromSingleFile>true</ExcludeFromSingleFile>
<CopyToOutputDirectory>Always</CopyToOutputDirectory>
<CopyToPublishDirectory>PreserveNewest</CopyToPublishDirectory>
</None>
<None Include=”Migrations\Seed\InitialSeedTestExtended.sql” Condition=”‘$(ExcludeConfigFilesFromBuildOutput)’!=’true'”>
<ExcludeFromSingleFile>true</ExcludeFromSingleFile>
<CopyToOutputDirectory>Always</CopyToOutputDirectory>
<CopyToPublishDirectory>PreserveNewest</CopyToPublishDirectory>
</None>
</ItemGroup>
Add flag “IsEFMigrationEnabled” : true to appsettings.json.
This flag is temporary. Should be set in order to “dotnet-ef” command can run.
Note: Make sure your Seed sql files has where clauses to prevent from duplications.
Warning: When you change the length of a property, make sure your migration is working fine and you do not cause data lose. To prevent data lose, you can add manual sql scripts to the end of the protected override void Up(MigrationBuilder migrationBuilder) method in related migration file.
Important Note: As standart, all the string fields are nvarchar and Unicode. So in seed data, you need to add N sign before string fields to save the value as Unicode to the database. Otherwise your data will not be saved as Unicode and you lose data.
As explained above, for moving new definitions from Development Environment to Production Environment, we are using seed sql files. To generate seed sql scripts easily for a table or for new records, you can use sp_generate_table_seed and sp_generate_detailtable_seed stored procedures in your development db.
exec [dbo].[sp_generate_table_seed] ‘SlipDefinition’, ‘Id’, ‘Code’
exec [dbo].[sp_generate_detailtable_seed] ‘SlipDefinitionDetail’, ‘Id’, ‘SlipDefinitionId’, ‘Order’, ‘SlipDefinition’, ‘Id’, ‘Code’
By using sp_generate_table_seed proc, you generate a script like below for a table:
IF NOT EXISTS (SELECT * FROM dbo.SlipDefinition WHERE Code=’CashDeposit’)
BEGIN
INSERT INTO dbo.SlipDefinition([Code],[CreatedBy],[CreateTime],[Description],[IsGenerateSerialNumber],[IsLatestVersion],[LanguageId],[Name],[UpdateCount],[UpdatedBy],[UpdateTime],[Version])
VALUES (N’CashDeposit’, N’system’, GETDATE(), N’Cash Deposit Slip’, 0, 1, 41, N’CashDeposit’, 0, NULL, NULL, 1)
END
ELSE
BEGIN
UPDATE dbo.SlipDefinition SET [Code] = N’CashDeposit’, [Description] = N’Cash Deposit Slip’, [IsGenerateSerialNumber] = 0, [IsLatestVersion] = 1, [LanguageId] = 41, [Name] = N’CashDeposit’, [UpdateCount] = UpdateCount + 1, [UpdatedBy] = ‘system’, [UpdateTime] = GETDATE(), [Version] = 1
WHERE Code=’CashDeposit’
END
If you are generating records of a detail table, you must use sp_generate_detailtable_seed and this proc generates a script like below:
— ********** Code(CashDeposit). Deleting all old details in the table and inserting new ones for this record. **********
DELETE dbo.SlipDefinitionDetail
FROM dbo.SlipDefinitionDetail d
INNER JOIN dbo.SlipDefinition m ON m.Id=d.SlipDefinitionId
WHERE m.Code=’CashDeposit’
DECLARE @masterTableIdValueForCashDeposit INT
SELECT @masterTableIdValueForCashDeposit=Id FROM dbo.SlipDefinition WHERE Code=’CashDeposit’
INSERT INTO dbo.SlipDefinitionDetail([Area],[CreatedBy],[CreateTime],[Label],[Order],[SlipDefinitionId],[Tags],[UpdateCount],[UpdatedBy],[UpdateTime],[Value])
VALUES (N’Title’, N’system’, GETDATE(), N’Title’, 1, @masterTableIdValueForCashDeposit, NULL, 0, NULL, NULL, N’Cash Deposit’)
INSERT INTO dbo.SlipDefinitionDetail([Area],[CreatedBy],[CreateTime],[Label],[Order],[SlipDefinitionId],[Tags],[UpdateCount],[UpdatedBy],[UpdateTime],[Value])
VALUES (N’LeftHeader’, N’system’, GETDATE(), N’Customer Number’, 2, @masterTableIdValueForCashDeposit, NULL, 0, NULL, NULL, N’#c.Cus.Id’)
INSERT INTO dbo.SlipDefinitionDetail([Area],[CreatedBy],[CreateTime],[Label],[Order],[SlipDefinitionId],[Tags],[UpdateCount],[UpdatedBy],[UpdateTime],[Value])
VALUES (N’LeftHeader’, N’system’, GETDATE(), N’Account Number’, 3, @masterTableIdValueForCashDeposit, NULL, 0, NULL, NULL, N’#c.C.AccountNumber’)
INSERT INTO dbo.SlipDefinitionDetail([Area],[CreatedBy],[CreateTime],[Label],[Order],[SlipDefinitionId],[Tags],[UpdateCount],[UpdatedBy],[UpdateTime],[Value])
VALUES (N’LeftHeader’, N’system’, GETDATE(), N’Transaction Date’, 4, @masterTableIdValueForCashDeposit, NULL, 0, NULL, NULL, N’#m.Today’)
INSERT INTO dbo.SlipDefinitionDetail([Area],[CreatedBy],[CreateTime],[Label],[Order],[SlipDefinitionId],[Tags],[UpdateCount],[UpdatedBy],[UpdateTime],[Value])
VALUES (N’LeftHeader’, N’system’, GETDATE(), N’Identity Number’, 5, @masterTableIdValueForCashDeposit, NULL, 0, NULL, NULL, N’#c.Cus.CustomerIdentifications[0].IdentityNumber’)
INSERT INTO dbo.SlipDefinitionDetail([Area],[CreatedBy],[CreateTime],[Label],[Order],[SlipDefinitionId],[Tags],[UpdateCount],[UpdatedBy],[UpdateTime],[Value])
VALUES (N’LeftHeader’, N’system’, GETDATE(), N’Issue Date’, 6, @masterTableIdValueForCashDeposit, NULL, 0, NULL, NULL, N’#m.Today’)
INSERT INTO dbo.SlipDefinitionDetail([Area],[CreatedBy],[CreateTime],[Label],[Order],[SlipDefinitionId],[Tags],[UpdateCount],[UpdatedBy],[UpdateTime],[Value])
VALUES (N’LeftHeader’, N’system’, GETDATE(), N’Reference’, 7, @masterTableIdValueForCashDeposit, NULL, 0, NULL, NULL, N’#m.BusinessKey’)
INSERT INTO dbo.SlipDefinitionDetail([Area],[CreatedBy],[CreateTime],[Label],[Order],[SlipDefinitionId],[Tags],[UpdateCount],[UpdatedBy],[UpdateTime],[Value])
VALUES (N’RightHeader’, N’system’, GETDATE(), N’Dear’, 8, @masterTableIdValueForCashDeposit, NULL, 0, NULL, NULL, N’#c.Cus.Name’)
INSERT INTO dbo.SlipDefinitionDetail([Area],[CreatedBy],[CreateTime],[Label],[Order],[SlipDefinitionId],[Tags],[UpdateCount],[UpdatedBy],[UpdateTime],[Value])
VALUES (N’RightHeader’, N’system’, GETDATE(), N’Address’, 9, @masterTableIdValueForCashDeposit, NULL, 0, NULL, NULL, N’Istanbul’)
INSERT INTO dbo.SlipDefinitionDetail([Area],[CreatedBy],[CreateTime],[Label],[Order],[SlipDefinitionId],[Tags],[UpdateCount],[UpdatedBy],[UpdateTime],[Value])
VALUES (N’LeftBody’, N’system’, GETDATE(), N’Sender Name’, 10, @masterTableIdValueForCashDeposit, NULL, 0, NULL, NULL, N’#c.Cus.Name’)
INSERT INTO dbo.SlipDefinitionDetail([Area],[CreatedBy],[CreateTime],[Label],[Order],[SlipDefinitionId],[Tags],[UpdateCount],[UpdatedBy],[UpdateTime],[Value])
VALUES (N’LeftBody’, N’system’, GETDATE(), N’Location’, 11, @masterTableIdValueForCashDeposit, NULL, 0, NULL, NULL, N’Mobile Branch’)
INSERT INTO dbo.SlipDefinitionDetail([Area],[CreatedBy],[CreateTime],[Label],[Order],[SlipDefinitionId],[Tags],[UpdateCount],[UpdatedBy],[UpdateTime],[Value])
VALUES (N’LeftBody’, N’system’, GETDATE(), N’Comment’, 12, @masterTableIdValueForCashDeposit, NULL, 0, NULL, NULL, N’#c.C.Comment’)
INSERT INTO dbo.SlipDefinitionDetail([Area],[CreatedBy],[CreateTime],[Label],[Order],[SlipDefinitionId],[Tags],[UpdateCount],[UpdatedBy],[UpdateTime],[Value])
VALUES (N’BottomFooter’, N’system’, GETDATE(), N’Amount’, 13, @masterTableIdValueForCashDeposit, N’Amount’, 0, NULL, NULL, N’#c.C.Amount’)
INSERT INTO dbo.SlipDefinitionDetail([Area],[CreatedBy],[CreateTime],[Label],[Order],[SlipDefinitionId],[Tags],[UpdateCount],[UpdatedBy],[UpdateTime],[Value])
VALUES (N’BottomFooter’, N’system’, GETDATE(), N’Currency’, 14, @masterTableIdValueForCashDeposit, NULL, 0, NULL, NULL, N’#c.C.CurrencyCode’)
LookupDbContext is used for reading other services most demanded data which is copied to a LookupDb which is stored next to the other Dbs in MsSql instance. LookupDbContext also eliminates needs of defining lots of Change Data Capture connectors.
If you need to copy data that belongs to another microservice, you can create a trigger than you can get data as a readonly from lookup api db by using LookUpDbContext. Adding new entity to LookupDb needs approval from Fimple Infra Team.
public class FiLookupDbContext : FiBaseLookupDbContext
{
public FiLookupDbContext(FiOptionsSingleton fiOptionsSingleton, ISessionContextDI sessionContextDi)
: base(fiOptionsSingleton, sessionContextDi)
{
}
public DbSet<CustomerLookup> CustomerLookups { get; set; }
public DbSet<CustomerCommunicationLookup> CustomerCommunicationLookups { get; set; }
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
var sqlServerOptions = optionsBuilder.UseSqlServer(DefaultLookupConnectionString
, sqlServerOptions =>
{
sqlServerOptions.CommandTimeout(TimeoutConstants.DefaultSqlServerCommandTimeout);
sqlServerOptions.EnableRetryOnFailure();
});
}
}
! Note: You do not need to add entity and configuration file to LookupDbContext ModelBuilder. These are will be added automatically.
dotnet ef migrations add test –startup-project Fi.Catalog.Api.csproj –context FiCatalogDbContext
! Note !: LookupDbContext should be use only for readonly operations. Do not save any changes.
! Note !: DO NOT CREATE A MIGRATION by using LookupDbContext! Note !: Do not forget copy old data to Lookup database
Data manupulations and data retrivals are coded Command and Query area of your Api project. All DB access must only be in commands and queries.
Entity and related EntityConfigurator classes are defined under Domain/Entity.
If you want to use other database server like as Postgresql or Couchbase, you must set ConnectionString, HostName, etc. are defined in appsettings.json.
Default Settings are Mssql Server at ApiBase appsettings.json file so you do not need to set this settings.
You can use both ToListAsNoTrackingAsync and ToListAsNoTrackingNoPaginationAsync methods for getting list data. ToListAsNoTrackingAsync and ToListAsNoTrackingNoPaginationAsync get only 1000 rows by default. If you want more than 1000 rows, use ToListAsNoTrackingNoPaginationAsync and set queryMaxDataRowCount as null to get limitless data row.
If you use EFCore ToListAsync method and if your query do not have tracking needs, be sure to add AsNoTracking().
If you use base methods (ToListAsNoTrackingAsync,ToListAsNoTrackingNoPaginationAsync etc…), ‘WITH (NOLOCK) ‘ scripts adds to generated queries by EF automatically.
Sample Generated Query :
If you do not use base methods like above, you can use WithNoLock extension methods
Note: This feature is disabled as a default. You can enable from appsettings.json or environment variables.
When get data for view and you do not need to change data, you must use AsNoTracking at Query API’s.
As you see ToListAsNoTrackingAsync Method has some parameters. All Query API’s are supported pagination, so sessionDI.MessageContext parameter is required for this method.
When update or delete data at Command API’s, AsNoTracking must not be used.
When updating a record, firstly it is preferred to check if the record exists in DB or not. It is essential for EFCore to track the selected entity to be able to update it.
You are sending the model with same values with db entity but only updating SortId. EFCore updates only SortId.
public async Task<ActionDefinitionOutputModel> Handle(UpdateActionDefinitionCommand request, CancellationToken cancellationToken)
{
sessionDI.ExecutionTrace.InitTrace();
request.Model.Id = request.Id;
var mapped = mapper.Map<ActionDefinition>(request.Model);
var fromDb = await dbContext.Set<ActionDefinition>().FirstOrDefaultAsync(x => x.Id == request.Model.Id);
if (fromDb == null)
throw ExceptionFactory.BadRequestEx(BaseErrorCodes.ItemDoNotExists, “ActionDefinition”, request.Model.Id);
await dbContext.UpdatePartial(fromDb, mapped); // Updates only SortId.
// dbContext.Update(fromDb); // Do not write this line or your sql script will be generated to update all the column.
await dbContext.SaveChangesAsync();
return mapper.Map<ActionDefinitionOutputModel>(fromDb);
}
Generated update script (Tracker does not get unchanged values):
SET NOCOUNT ON;
UPDATE [ActionDefinition] SET [SortId] = @p0
WHERE [Id] = @p1;
SELECT @@ROWCOUNT;
Note: If you change the entity in code without any mapping, then you do not need to call Set Values or Update methods. SaveChangesAsync() is enough for updating data.
public async Task<ActionDefinitionOutputModel> Handle(UpdateActionDefinitionCommand request, CancellationToken cancellationToken)
{
sessionDI.ExecutionTrace.InitTrace();
request.Model.Id = request.Id;
var fromDb = await dbContext.Set<ActionDefinition>().FirstOrDefaultAsync(x => x.Id == request.Model.Id);
if (fromDb == null)
throw ExceptionFactory.BadRequestEx(BaseErrorCodes.ItemDoNotExists, “ActionDefinition”, request.Model.Id);
fromDb.Description = “Desc Updated”;
await dbContext.SaveChangesAsync();
return mapper.Map<ActionDefinitionOutputModel>(fromDb);
}
If you use AutoMap, while dbContext.Update you get this error: System.InvalidOperationException: The instance of entity type ‘ActionDefinition’ cannot be tracked because another instance with the key value ‘{Id: 10}’ is already being tracked.
public async Task<ActionDefinitionOutputModel> Handle(UpdateActionDefinitionCommand request, CancellationToken cancellationToken)
{
sessionDI.ExecutionTrace.InitTrace();
request.Model.Id = request.Id;
var fromDb = await dbContext.Set<ActionDefinition>().FirstOrDefaultAsync(x => x.Id == request.Model.Id);
if (fromDb == null)
throw ExceptionFactory.BadRequestEx(BaseErrorCodes.ItemDoNotExists, “ActionDefinition”, request.Model.Id);
fromDb = mapper.Map<ActionDefinition>(request.Model);
dbContext.Update(fromDb);
await dbContext.SaveChangesAsync();
return mapper.Map<ActionDefinitionOutputModel>(fromDb);
}
If you do not read related record from db, EFCore does not track the record and updates all the columns.
public async Task<ActionDefinitionOutputModel> Handle(UpdateActionDefinitionCommand request, CancellationToken cancellationToken)
{
sessionDI.ExecutionTrace.InitTrace();
request.Model.Id = request.Id;
//var fromDb = await dbContext.Set<ActionDefinition>().FirstOrDefaultAsync(x => x.Id == request.Model.Id);
//if (fromDb == null)
// throw ExceptionFactory.BadRequestEx(BaseErrorCodes.ItemDoNotExists, “ActionDefinition”, request.Model.Id);
var entity = mapper.Map<ActionDefinition>(request.Model);
dbContext.Update(entity);
await dbContext.SaveChangesAsync();
return mapper.Map<ActionDefinitionOutputModel>(entity);
}
Generated update script:
SET NOCOUNT ON;
UPDATE [ActionDefinition] SET [ActionType] = @p0, [CommandName] = @p1, [Description] = @p2, [IconPath] = @p3, [Name] = @p4, [OperationType] = @p5, [SortId] = @p6, [UpdateCount] = @p7, [UpdateTime] = @p8, [UpdatedBy] = @p9
WHERE [Id] = @p10;
SELECT @@ROWCOUNT;
Important: If you do not send all columns in your model, and if you do not get record from Db, EFCore does not track record and updates all the columns with default values.
Important: If you have nested objects / lists in your model and changed some of them, and if you do not get record from Db, EFCore does not track record and does changes according to your model. In this case you do not send deleted items in your model, so EFCore keeps this item in Db, this case is not desired.
If you want to update some of the fields of your record then you can create another model class and set to related entity to update only changed data.
public async Task<ActionDefinitionOutputModel> Handle(UpdateActionDefinitionCommand request, CancellationToken cancellationToken)
{
sessionDI.ExecutionTrace.InitTrace();
request.Model.Id = request.Id;
ActionDefinitionInputModelX newModel = new ActionDefinitionInputModelX();
newModel.Id = request.Model.Id;
newModel.SortId = request.Model.SortId;
var fromDb = await dbContext.Set<ActionDefinition>().FirstOrDefaultAsync(x => x.Id == request.Model.Id);
if (fromDb == null)
throw ExceptionFactory.BadRequestEx(BaseErrorCodes.ItemDoNotExists, “ActionDefinition”, request.Model.Id);
await dbContext.UpdatePartialWithModel(fromDb, newModel);
await dbContext.SaveChangesAsync();
return mapper.Map<ActionDefinitionOutputModel>(fromDb);
}
Generated update script:
SET NOCOUNT ON;
UPDATE [ActionDefinition] SET [SortId] = @p0
WHERE [Id] = @p1;
SELECT @@ROWCOUNT;
If you have nesned objects / lists in your model, and there are cases of adding, removing and updating the list according to your business needs. You can use the following metod to updated the nested object.
public async Task<ProductInfoOutputModel> Handle(UpdateProductCommand request, CancellationToken cancellationToken)
{
request.Model.Id = request.ProductId;
sessionDI.ExecutionTrace.InitTrace();
//Select Main Entity with Nested List
var fromDb = await dbContext.Set<Product>()
.Include(x => x.ProductFeeSettings)
.Include(x => x.ProductBranchSettings)
.FirstOrDefaultAsync<Product>(x => x.Id == request.ProductId);
if (fromDb == null)
throw ExceptionFactory.BadRequestEx(BaseErrorCodes.ItemDoNotExists, “Product”, request.ProductId);
request.Model.Version = fromDb.Version;
request.Model.IsLatestVersion = fromDb.IsLatestVersion;
var mapped = mapper.Map<Product>(request.Model);
//If Your Entity Inheritance From IFiEntityWithIdentity, you can use this overload method “UpdateList”
mapped.ProductFeeSettings.ForEach(x => x.ProductId = request.ProductId);
await dbContext.UpdateList<ProductFeeSetting, int>(fromDb.ProductFeeSettings.ToList(),
mapped.ProductFeeSettings);
//If your Unique Id different from Default Identity column, you must define “idSelector” like as below, and pass this Func overload method “UpdateList”
Func<ProductBranchSetting, int> idSelectorSourceBranch = src => src.BranchId;
mapped.ProductBranchSettings.ForEach(x => x.ProductId = request.ProductId);
await dbContext.UpdateList<ProductBranchSetting, int>(fromDb.ProductBranchSettings.ToList(),
mapped.ProductBranchSettings,
idSelectorSourceBranch);
//CurrentValues.SetValues method just only update main entity, in this example it is “Product”
await dbContext.UpdatePartial(fromDb, mapped);
await dbContext.SaveChangesAsync();
var productInfoOutputModel = mapper.Map<ProductInfoOutputModel>(fromDb);
return productInfoOutputModel;
}
If you need to update just only some properties on model, you can use Patch API.
You should use ApplyTo method for mapping to model.
public async Task<Account> PatchAccountAsync(long accounNumber, JsonPatchDocument<AccountInputModel> patchModel)
{
sessionDI.ExecutionTrace.InitTrace();
//Get Account From Db By Account Number
var account = await CheckAccount(accounNumber);
//Map From Entity To Input Model & Run Patch ApplyTo
var accountInputModel = mapper.Map<AccountInputModel>(account);
patchModel.ApplyTo(accountInputModel);
//Map Input Model To New Entity & UpdatePartial
var mapped = mapper.Map<Account>(accountInputModel);
await dbContext.UpdatePartial(account, mapped);
await dbContext.SaveChangesAsync();
return account;
}
Your tables are actually your entities. When creating an entity system will create a table in the Db.
Code First approach is used in Data Layer. Click here for detailed information.
Entity (table) designs are not approved by a Data Architect or Chief Architect. Teams are responsible for entity designs. Chief Architect can check entity designs by sampling few of the tables in DBs.
Entities names should be in PascalCase.
Do not use “s” suffix at the end of entity name.
Avoid using Definition suffix for the entities. Only use if the related name is very generic like Api, Action, Resource etc. Samples: ApiDefinition, ActionDefinition, ServiceDefinition, Country, City, Currency.
Before creating your models and entities please check Data Dictionary&Db Rules section.
While creating models and entities, it is important to decide multi language fields from the very beginning. Multi language fields should store Json String. Sample: { “tr”:”Adı”, “en”: “Name”, “ru”: “ади” }
When you create a new entity, you can use below base classes and interfaces for this entity.
This class can be used when default fields in tables are not needed.
In this case, the identity “Id” column does not come from base and you should manage it if you need it.
Generally not recommended for business projects.
As an example, you can review the Document Entity
This class can be used when default fields in tables are needed . But “Id” column is not included.
In this case, the identity “Id” column does not come from base and you should manage it if you need it.
Generally it is used when you need default fields but not an identity column. Or, when you want to use composite key instead of an identity column.
Default Fields are below
These fields value are given automatically by persistence layer.
Contains “Id” column in addition to the base class “EntityBaseWithBaseFields”.
Id column is managed by persistence layer.
Contains State, DeletedBy, and DeleteTime columns in addition to the base class “EntityBaseWithBaseFields”.
If you want to check the state of your record, you can use this class. But not recommended for complex state situations.
When you use this class, your deletions are done as soft delete
Entity States are below
Contains “Id” column in addition to the base class “EntityBaseWithBaseFieldsWithState”.
Id column is managed by persistence layer.
This interface should be added to the entities that need row version control.
After adding Row Version, concurrent update of a record by multiple users is controlled by the persistence layer. Be careful when you want to use it, you should use it for the main table, not for the detail tables.
This interface should be added to the entities that need application code for service banking.
Application Code value are given automatically by persistence layer. If Tenant and Application are service banking, it is automatically added to the filter in your select queries.
For all fields, IsRequired, MaxLength, IsUnique, etc. should be defined.
Related entities (PF/FK) should be bound with required relation rule.
The FK type is added to the table as “Cascade” by default. This means that when a record is deleted in the main table, the related record/s are also deleted in the detail table.
FK is created by EFCore as “Cascade” for navigation properties. Even if you don’t define an FK relationship on your Configurator.
If a relationship is established between the definition table and the transaction table, FK Type “No Action” must be set so that the definition table is not affected when a record is deleted from the transaction table.
Code View For NoAction :
builder.HasOne(p => p.Parent)
.WithMany()
.HasForeignKey(x => x.ParentId)
.IsRequired(false)
.OnDelete(DeleteBehavior.Restrict);
When a record is deleted from the table, the relevant Column is set to NULL in the Main table according to the business need.
Code View For NULL :
builder.HasMany(p => p.POSTransactions)
.WithOne(y => y.Workgroup)
.HasForeignKey(p => p.TransactionWorkgroupId)
.IsRequired(false)
.OnDelete(DeleteBehavior.SetNull);
Do not prefer ForeignKey attribute, because it set Cascade option by default for foreign keys. Use EF Core Fluent API like above.
When you Save a Record, below fields are being saved to DB automatically by the DBContext (The fields vary according to the types of IFiEntity).
Every entity must have an Id field and this field must be PK.
Entities which inherited from EntityBaseWithBaseFieldsWithIdentity has an Id column which is also PK of the table. Do not add another Id column to your entity. Wrong: FecId.
For PK/FK relations, add virtual objects to related entities. And also detail class a foreignkey field like MainEntityNameId.
public virtual List<ApiParameterDefinition> Parameters { get; set; } = new List<ApiParameterDefinition>();
public virtual ApiDefinition ApiDefinition { get; set; }
public virtual List<ApiParameterPropertyDefinition> Properties { get; set; } = new List<ApiParameterPropertyDefinition>();
If the entity is a definition entity like Resource, Action, Event, etc. then Id column is not enough for data transport operations. So also consider to add a Code, Key or UniqueName field in your entity. Try to choose only one of Code, Key or UniqueName fields. While choosing Code, Key or UniqueName, look at entity and your concept to decide the best option.
When creating Database Model you must check this table.
If your data type in the table, you must choose available type, related to your purpose.
Check for other pre-defined data types: Fi.Persistence.Relational.Domain.DataTypes
This section applies to all holding and showing currency decimal values like amount, rate, balance.
In database, always we need to store rounded value.
Rounding must be done according to currency, not per country etc.
Rounding must be done as a last step of all calculations. Rounding is not an optinal step. It is mandatory.
All amount and balance values of a currency.
Fimple developers use tools below. One of them can be chosen.
For Windows developers Sql Server Management Studio can be used.
Data manupulations and data retrivals are coded Command and Query area of your Api project. All DB access must only be in commands and queries.
Entity and related EntityConfigurator classes are defined under Domain/Entity.
If you want to use other database server like as Postgresql or Couchbase, you must set ConnectionString, HostName, etc. are defined in appsettings.json.
Default Settings are Mssql Server at ApiBase appsettings.json file so you do not need to set this settings.
You can use both ToListAsNoTrackingAsync and ToListAsNoTrackingNoPaginationAsync methods for getting list data. ToListAsNoTrackingAsync and ToListAsNoTrackingNoPaginationAsync get only 1000 rows by default. If you want more than 1000 rows, use ToListAsNoTrackingNoPaginationAsync and set queryMaxDataRowCount as null to get limitless data row.
If you use EFCore ToListAsync method and if your query do not have tracking needs, be sure to add AsNoTracking().
If you use base methods (ToListAsNoTrackingAsync,ToListAsNoTrackingNoPaginationAsync etc…), ‘WITH (NOLOCK) ‘ scripts adds to generated queries by EF automatically.
Sample Generated Query :
If you do not use base methods like above, you can use WithNoLock extension methods
Note: This feature is disabled as a default. You can enable from appsettings.json or environment variables.
When get data for view and you do not need to change data, you must use AsNoTracking at Query API’s.
As you see ToListAsNoTrackingAsync Method has some parameters. All Query API’s are supported pagination, so sessionDI.MessageContext parameter is required for this method.
When update or delete data at Command API’s, AsNoTracking must not be used.
When updating a record, firstly it is preferred to check if the record exists in DB or not. It is essential for EFCore to track the selected entity to be able to update it.
You are sending the model with same values with db entity but only updating SortId. EFCore updates only SortId.
public async Task<ActionDefinitionOutputModel> Handle(UpdateActionDefinitionCommand request, CancellationToken cancellationToken)
{
sessionDI.ExecutionTrace.InitTrace();
request.Model.Id = request.Id;
var mapped = mapper.Map<ActionDefinition>(request.Model);
var fromDb = await dbContext.Set<ActionDefinition>().FirstOrDefaultAsync(x => x.Id == request.Model.Id);
if (fromDb == null)
throw ExceptionFactory.BadRequestEx(BaseErrorCodes.ItemDoNotExists, “ActionDefinition”, request.Model.Id);
await dbContext.UpdatePartial(fromDb, mapped); // Updates only SortId.
// dbContext.Update(fromDb); // Do not write this line or your sql script will be generated to update all the column.
await dbContext.SaveChangesAsync();
return mapper.Map<ActionDefinitionOutputModel>(fromDb);
}
Generated update script (Tracker does not get unchanged values):
SET NOCOUNT ON;
UPDATE [ActionDefinition] SET [SortId] = @p0
WHERE [Id] = @p1;
SELECT @@ROWCOUNT;
Note: If you change the entity in code without any mapping, then you do not need to call Set Values or Update methods. SaveChangesAsync() is enough for updating data.
public async Task<ActionDefinitionOutputModel> Handle(UpdateActionDefinitionCommand request, CancellationToken cancellationToken)
{
sessionDI.ExecutionTrace.InitTrace();
request.Model.Id = request.Id;
var fromDb = await dbContext.Set<ActionDefinition>().FirstOrDefaultAsync(x => x.Id == request.Model.Id);
if (fromDb == null)
throw ExceptionFactory.BadRequestEx(BaseErrorCodes.ItemDoNotExists, “ActionDefinition”, request.Model.Id);
fromDb.Description = “Desc Updated”;
await dbContext.SaveChangesAsync();
return mapper.Map<ActionDefinitionOutputModel>(fromDb);
}
If you use AutoMap, while dbContext.Update you get this error: System.InvalidOperationException: The instance of entity type ‘ActionDefinition’ cannot be tracked because another instance with the key value ‘{Id: 10}’ is already being tracked.
public async Task<ActionDefinitionOutputModel> Handle(UpdateActionDefinitionCommand request, CancellationToken cancellationToken)
{
sessionDI.ExecutionTrace.InitTrace();
request.Model.Id = request.Id;
var fromDb = await dbContext.Set<ActionDefinition>().FirstOrDefaultAsync(x => x.Id == request.Model.Id);
if (fromDb == null)
throw ExceptionFactory.BadRequestEx(BaseErrorCodes.ItemDoNotExists, “ActionDefinition”, request.Model.Id);
fromDb = mapper.Map<ActionDefinition>(request.Model);
dbContext.Update(fromDb);
await dbContext.SaveChangesAsync();
return mapper.Map<ActionDefinitionOutputModel>(fromDb);
}
If you do not read related record from db, EFCore does not track the record and updates all the columns.
public async Task<ActionDefinitionOutputModel> Handle(UpdateActionDefinitionCommand request, CancellationToken cancellationToken)
{
sessionDI.ExecutionTrace.InitTrace();
request.Model.Id = request.Id;
//var fromDb = await dbContext.Set<ActionDefinition>().FirstOrDefaultAsync(x => x.Id == request.Model.Id);
//if (fromDb == null)
// throw ExceptionFactory.BadRequestEx(BaseErrorCodes.ItemDoNotExists, “ActionDefinition”, request.Model.Id);
var entity = mapper.Map<ActionDefinition>(request.Model);
dbContext.Update(entity);
await dbContext.SaveChangesAsync();
return mapper.Map<ActionDefinitionOutputModel>(entity);
}
Generated update script:
SET NOCOUNT ON;
UPDATE [ActionDefinition] SET [ActionType] = @p0, [CommandName] = @p1, [Description] = @p2, [IconPath] = @p3, [Name] = @p4, [OperationType] = @p5, [SortId] = @p6, [UpdateCount] = @p7, [UpdateTime] = @p8, [UpdatedBy] = @p9
WHERE [Id] = @p10;
SELECT @@ROWCOUNT;
Important: If you do not send all columns in your model, and if you do not get record from Db, EFCore does not track record and updates all the columns with default values.
Important: If you have nested objects / lists in your model and changed some of them, and if you do not get record from Db, EFCore does not track record and does changes according to your model. In this case you do not send deleted items in your model, so EFCore keeps this item in Db, this case is not desired.
If you want to update some of the fields of your record then you can create another model class and set to related entity to update only changed data.
public async Task<ActionDefinitionOutputModel> Handle(UpdateActionDefinitionCommand request, CancellationToken cancellationToken)
{
sessionDI.ExecutionTrace.InitTrace();
request.Model.Id = request.Id;
ActionDefinitionInputModelX newModel = new ActionDefinitionInputModelX();
newModel.Id = request.Model.Id;
newModel.SortId = request.Model.SortId;
var fromDb = await dbContext.Set<ActionDefinition>().FirstOrDefaultAsync(x => x.Id == request.Model.Id);
if (fromDb == null)
throw ExceptionFactory.BadRequestEx(BaseErrorCodes.ItemDoNotExists, “ActionDefinition”, request.Model.Id);
await dbContext.UpdatePartialWithModel(fromDb, newModel);
await dbContext.SaveChangesAsync();
return mapper.Map<ActionDefinitionOutputModel>(fromDb);
}
Generated update script:
SET NOCOUNT ON;
UPDATE [ActionDefinition] SET [SortId] = @p0
WHERE [Id] = @p1;
SELECT @@ROWCOUNT;
If you have nesned objects / lists in your model, and there are cases of adding, removing and updating the list according to your business needs. You can use the following metod to updated the nested object.
public async Task<ProductInfoOutputModel> Handle(UpdateProductCommand request, CancellationToken cancellationToken)
{
request.Model.Id = request.ProductId;
sessionDI.ExecutionTrace.InitTrace();
//Select Main Entity with Nested List
var fromDb = await dbContext.Set<Product>()
.Include(x => x.ProductFeeSettings)
.Include(x => x.ProductBranchSettings)
.FirstOrDefaultAsync<Product>(x => x.Id == request.ProductId);
if (fromDb == null)
throw ExceptionFactory.BadRequestEx(BaseErrorCodes.ItemDoNotExists, “Product”, request.ProductId);
request.Model.Version = fromDb.Version;
request.Model.IsLatestVersion = fromDb.IsLatestVersion;
var mapped = mapper.Map<Product>(request.Model);
//If Your Entity Inheritance From IFiEntityWithIdentity, you can use this overload method “UpdateList”
mapped.ProductFeeSettings.ForEach(x => x.ProductId = request.ProductId);
await dbContext.UpdateList<ProductFeeSetting, int>(fromDb.ProductFeeSettings.ToList(),
mapped.ProductFeeSettings);
//If your Unique Id different from Default Identity column, you must define “idSelector” like as below, and pass this Func overload method “UpdateList”
Func<ProductBranchSetting, int> idSelectorSourceBranch = src => src.BranchId;
mapped.ProductBranchSettings.ForEach(x => x.ProductId = request.ProductId);
await dbContext.UpdateList<ProductBranchSetting, int>(fromDb.ProductBranchSettings.ToList(),
mapped.ProductBranchSettings,
idSelectorSourceBranch);
//CurrentValues.SetValues method just only update main entity, in this example it is “Product”
await dbContext.UpdatePartial(fromDb, mapped);
await dbContext.SaveChangesAsync();
var productInfoOutputModel = mapper.Map<ProductInfoOutputModel>(fromDb);
return productInfoOutputModel;
}
If you need to update just only some properties on model, you can use Patch API.
You should use ApplyTo method for mapping to model.
public async Task<Account> PatchAccountAsync(long accounNumber, JsonPatchDocument<AccountInputModel> patchModel)
{
sessionDI.ExecutionTrace.InitTrace();
//Get Account From Db By Account Number
var account = await CheckAccount(accounNumber);
//Map From Entity To Input Model & Run Patch ApplyTo
var accountInputModel = mapper.Map<AccountInputModel>(account);
patchModel.ApplyTo(accountInputModel);
//Map Input Model To New Entity & UpdatePartial
var mapped = mapper.Map<Account>(accountInputModel);
await dbContext.UpdatePartial(account, mapped);
await dbContext.SaveChangesAsync();
return account;
}
Your tables are actually your entities. When creating an entity system will create a table in the Db.
Code First approach is used in Data Layer. Click here for detailed information.
Entity (table) designs are not approved by a Data Architect or Chief Architect. Teams are responsible for entity designs. Chief Architect can check entity designs by sampling few of the tables in DBs.
Entities names should be in PascalCase.
Do not use “s” suffix at the end of entity name.
Avoid using Definition suffix for the entities. Only use if the related name is very generic like Api, Action, Resource etc. Samples: ApiDefinition, ActionDefinition, ServiceDefinition, Country, City, Currency.
Before creating your models and entities please check Data Dictionary&Db Rules section.
While creating models and entities, it is important to decide multi language fields from the very beginning. Multi language fields should store Json String. Sample: { “tr”:”Adı”, “en”: “Name”, “ru”: “ади” }
When you create a new entity, you can use below base classes and interfaces for this entity.
This class can be used when default fields in tables are not needed.
In this case, the identity “Id” column does not come from base and you should manage it if you need it.
Generally not recommended for business projects.
As an example, you can review the Document Entity
This class can be used when default fields in tables are needed . But “Id” column is not included.
In this case, the identity “Id” column does not come from base and you should manage it if you need it.
Generally it is used when you need default fields but not an identity column. Or, when you want to use composite key instead of an identity column.
Default Fields are below
These fields value are given automatically by persistence layer.
Contains “Id” column in addition to the base class “EntityBaseWithBaseFields”.
Id column is managed by persistence layer.
Contains State, DeletedBy, and DeleteTime columns in addition to the base class “EntityBaseWithBaseFields”.
If you want to check the state of your record, you can use this class. But not recommended for complex state situations.
When you use this class, your deletions are done as soft delete
Entity States are below
Contains “Id” column in addition to the base class “EntityBaseWithBaseFieldsWithState”.
Id column is managed by persistence layer.
This interface should be added to the entities that need row version control.
After adding Row Version, concurrent update of a record by multiple users is controlled by the persistence layer. Be careful when you want to use it, you should use it for the main table, not for the detail tables.
This interface should be added to the entities that need application code for service banking.
Application Code value are given automatically by persistence layer. If Tenant and Application are service banking, it is automatically added to the filter in your select queries.
For all fields, IsRequired, MaxLength, IsUnique, etc. should be defined.
Related entities (PF/FK) should be bound with required relation rule.
The FK type is added to the table as “Cascade” by default. This means that when a record is deleted in the main table, the related record/s are also deleted in the detail table.
FK is created by EFCore as “Cascade” for navigation properties. Even if you don’t define an FK relationship on your Configurator.
If a relationship is established between the definition table and the transaction table, FK Type “No Action” must be set so that the definition table is not affected when a record is deleted from the transaction table.
Code View For NoAction :
builder.HasOne(p => p.Parent)
.WithMany()
.HasForeignKey(x => x.ParentId)
.IsRequired(false)
.OnDelete(DeleteBehavior.Restrict);
When a record is deleted from the table, the relevant Column is set to NULL in the Main table according to the business need.
Code View For NULL :
builder.HasMany(p => p.POSTransactions)
.WithOne(y => y.Workgroup)
.HasForeignKey(p => p.TransactionWorkgroupId)
.IsRequired(false)
.OnDelete(DeleteBehavior.SetNull);
Do not prefer ForeignKey attribute, because it set Cascade option by default for foreign keys. Use EF Core Fluent API like above.
When you Save a Record, below fields are being saved to DB automatically by the DBContext (The fields vary according to the types of IFiEntity).
Every entity must have an Id field and this field must be PK.
Entities which inherited from EntityBaseWithBaseFieldsWithIdentity has an Id column which is also PK of the table. Do not add another Id column to your entity. Wrong: FecId.
For PK/FK relations, add virtual objects to related entities. And also detail class a foreignkey field like MainEntityNameId.
public virtual List<ApiParameterDefinition> Parameters { get; set; } = new List<ApiParameterDefinition>();
public virtual ApiDefinition ApiDefinition { get; set; }
public virtual List<ApiParameterPropertyDefinition> Properties { get; set; } = new List<ApiParameterPropertyDefinition>();
If the entity is a definition entity like Resource, Action, Event, etc. then Id column is not enough for data transport operations. So also consider to add a Code, Key or UniqueName field in your entity. Try to choose only one of Code, Key or UniqueName fields. While choosing Code, Key or UniqueName, look at entity and your concept to decide the best option.
When creating Database Model you must check this table.
If your data type in the table, you must choose available type, related to your purpose.
Check for other pre-defined data types: Fi.Persistence.Relational.Domain.DataTypes
This section applies to all holding and showing currency decimal values like amount, rate, balance.
In database, always we need to store rounded value.
Rounding must be done according to currency, not per country etc.
Rounding must be done as a last step of all calculations. Rounding is not an optinal step. It is mandatory.
All amount and balance values of a currency.
nvm version ^0.39.1
Fimple uses Git and Azure Devops as a source code repository. As a tool one of them can be chosen.
Before start development basic git commands should be known. Click here to see git fundamental commands.
Developers cannot push code changes directly to the main and master branch.
Create a local branch from main branch
Push the local branch to remote with same name
Make some changes like new feature or bug fixing
Create a merge request and assign it an eligible approver
Create a local branch from master branch
Cherry-pick your changes into the local branch
Push the local branch to remote with same name
Create a merge request and assign it an eligible approver
To reach the internal services(database, redis, minio etc.) of environments as localhost you need to follow these steps.
Kubernetes port forwarding sample command is like below. Most common commands.
kubectl port-forward services/fi-mssql 1433:1433 -n data
kubectl port-forward services/fi-redis-master 6379:6379 -n data
kubectl port-forward services/fi-camunda-zeebe-gateway 26500:26500 -n camunda
kubectl port-forward services/fi-minio 9000:9000 -n data
Partner which has static IP can connect to the database over the IP address that is given by Fimple devops teams.
Fimple uses Rancher to manage the clusters on Kubernetes. It is a complete software stack for teams adopting containers. It addresses the operational and security challenges of managing multiple Kubernetes clusters across any infrastructure, while providing DevOps teams with integrated tools for running containerized workloads.
Fimple uses VSCode to develop frontend and backend software.
For Windows user visual studio can be used.
Fimple developers use tools below. One of them can be chosen.
For Windows developers Sql Server Management Studio can be used.
Fimple uses Git and Azure Devops as a source code repository. As a tool one of them can be chosen.
Before start development basic git commands should be known. Click here to see git fundamental commands.
Developers cannot push code changes directly to the main and master branch.
Create a local branch from main branch
Push the local branch to remote with same name
Make some changes like new feature or bug fixing
Create a merge request and assign it an eligible approver
Create a local branch from master branch
Cherry-pick your changes into the local branch
Push the local branch to remote with same name
Create a merge request and assign it an eligible approver
Fimple has microservice architecture and uses .Net Core 6.0.
Fimple architecture supports On-Premises, Hybrid and Cloud installations.
Project solution file has two C# projects. Api project consists of backend api capabilities and Schema project has DTOs.
dotnet new Fi.Template.Api -n Fi.YourProject.Api -o Fi.YourProject.Api –ModuleName YourProject –Database Relational –StartErrorCode 8000 –EndErrorCode 8500 –LocalHostPort 10099
dotnet new Fi.Template.Schema -n Fi.YourProject.Schema -o Fi.YourProject.Schema
Fimple uses EF Core for ORM with the code first approach.
Fimple uses EF Migration to update the database schema to keep it in sync with the application’s data model while preserving existing data in the database. Click here for more details.
After creating a new Api project and creating required entities you need to prepare your Seed Data and add Initial Migration with dotnet ef commands. This Initial Migration and Seed Data is being updated to the all tenant DB’s in the environment by the pipeline when it is deployed.
After Initial Migration updated in the DBs, you must create Update (Feature) Migrations for DB related code changes like entity update. These update (feature) migrations are also being updated to the all tenant DB’s in the environment by the pipeline when it is deployed.
In the ci-cd pipe of fi.src project, dotnet ef idempotent script generation is used to generate script of all ef core migrations if there is any change under migrations folder of the project.
This generated file Fi.ServiceName.Api.sql is pushed to Migrations/Output folder, to main branch of the project.
As a last step Fi.Util exe is called to execute all migrations.
How Fi.Util exe works;
After you define the initial domain classes and make your project testable than create an initial migration.
dotnet ef migrations add InitialMigration
Then update database with your initial migration (do this only for local database, this command is run by pipeline for environment DBs).
dotnet ef database update
You can use this command whenever you want. This command gets all the initial and update (feature) migrations and applies them to the DB.
There shoul be only one initial migration on the Api project. After updating database, your newly entity changes need a new migration and a new update to the database. Create an update (feature) migration with a well migration name which is clear for your change.
dotnet ef migrations add ChangingCustomerAddressModels
dotnet ef database update
Whenever you merge your code to main, pipeline runs dotnet ef database update command and this command updates related environment DB.
Add your Initial Seed Data into Migrations/Seed/InitialSeed.sql file. Do not put begin tran and commit tran blocks inside these sqls ( forbidden ).
Add this .sql to the end of protected override void Up(MigrationBuilder migrationBuilder) method in date_InitialCreate.cs migration file (or date_InitialMigration.cs).
When you want to add new definitions or seed data related to your change, add another UpdateSeed.sql file and again add this .sql to the end of protected override void Up( MigrationBuilder migrationBuilder) method in update (feature) migration file (sample: date_UpdateMigrationForGenderTypes.cs).
If you need to separate migration data for Test and Production Environment
You can added Test Data to Migrations/Seed/InitialSeedTestExtended.sql file.
Add this .sql to the end of protected override void Up(MigrationBuilder migrationBuilder) method in date_InitialCreate.cs migration file.
Up(MigrationBuilder migrationBuilder) Method Override Code Block
migrationBuilder.Sql(System.IO.File.ReadAllText(System.IO.Path.Combine(AppContext.BaseDirectory, “Migrations/Seed/InitialSeed.sql”)));
var currentEnvironment = Environment.GetEnvironmentVariable(“ASPNETCORE_ENVIRONMENT”);
if (currentEnvironment != “Production”)
{
migrationBuilder.Sql(System.IO.File.ReadAllText(System.IO.Path.Combine(AppContext.BaseDirectory, “Migrations/Seed/InitialSeedTestExtended.sql”)));
}
Add ItemGroup to project .sln for copy .sql files to output directory.
project .sln ItemGroup Code Block
<ItemGroup>
<None Include=”Migrations\Seed\InitialSeed.sql” Condition=”‘$(ExcludeConfigFilesFromBuildOutput)’!=’true'”>
<ExcludeFromSingleFile>true</ExcludeFromSingleFile>
<CopyToOutputDirectory>Always</CopyToOutputDirectory>
<CopyToPublishDirectory>PreserveNewest</CopyToPublishDirectory>
</None>
<None Include=”Migrations\Seed\InitialSeedTestExtended.sql” Condition=”‘$(ExcludeConfigFilesFromBuildOutput)’!=’true'”>
<ExcludeFromSingleFile>true</ExcludeFromSingleFile>
<CopyToOutputDirectory>Always</CopyToOutputDirectory>
<CopyToPublishDirectory>PreserveNewest</CopyToPublishDirectory>
</None>
</ItemGroup>
Add flag “IsEFMigrationEnabled” : true to appsettings.json.
This flag is temporary. Should be set in order to “dotnet-ef” command can run.
Note: Make sure your Seed sql files has where clauses to prevent from duplications.
Warning: When you change the length of a property, make sure your migration is working fine and you do not cause data lose. To prevent data lose, you can add manual sql scripts to the end of the protected override void Up(MigrationBuilder migrationBuilder) method in related migration file.
Important Note: As standart, all the string fields are nvarchar and Unicode. So in seed data, you need to add N sign before string fields to save the value as Unicode to the database. Otherwise your data will not be saved as Unicode and you lose data.
As explained above, for moving new definitions from Development Environment to Production Environment, we are using seed sql files. To generate seed sql scripts easily for a table or for new records, you can use sp_generate_table_seed and sp_generate_detailtable_seed stored procedures in your development db.
exec [dbo].[sp_generate_table_seed] ‘SlipDefinition’, ‘Id’, ‘Code’
exec [dbo].[sp_generate_detailtable_seed] ‘SlipDefinitionDetail’, ‘Id’, ‘SlipDefinitionId’, ‘Order’, ‘SlipDefinition’, ‘Id’, ‘Code’
By using sp_generate_table_seed proc, you generate a script like below for a table:
IF NOT EXISTS (SELECT * FROM dbo.SlipDefinition WHERE Code=’CashDeposit’)
BEGIN
INSERT INTO dbo.SlipDefinition([Code],[CreatedBy],[CreateTime],[Description],[IsGenerateSerialNumber],[IsLatestVersion],[LanguageId],[Name],[UpdateCount],[UpdatedBy],[UpdateTime],[Version])
VALUES (N’CashDeposit’, N’system’, GETDATE(), N’Cash Deposit Slip’, 0, 1, 41, N’CashDeposit’, 0, NULL, NULL, 1)
END
ELSE
BEGIN
UPDATE dbo.SlipDefinition SET [Code] = N’CashDeposit’, [Description] = N’Cash Deposit Slip’, [IsGenerateSerialNumber] = 0, [IsLatestVersion] = 1, [LanguageId] = 41, [Name] = N’CashDeposit’, [UpdateCount] = UpdateCount + 1, [UpdatedBy] = ‘system’, [UpdateTime] = GETDATE(), [Version] = 1
WHERE Code=’CashDeposit’
END
If you are generating records of a detail table, you must use sp_generate_detailtable_seed and this proc generates a script like below:
— ********** Code(CashDeposit). Deleting all old details in the table and inserting new ones for this record. **********
DELETE dbo.SlipDefinitionDetail
FROM dbo.SlipDefinitionDetail d
INNER JOIN dbo.SlipDefinition m ON m.Id=d.SlipDefinitionId
WHERE m.Code=’CashDeposit’
DECLARE @masterTableIdValueForCashDeposit INT
SELECT @masterTableIdValueForCashDeposit=Id FROM dbo.SlipDefinition WHERE Code=’CashDeposit’
INSERT INTO dbo.SlipDefinitionDetail([Area],[CreatedBy],[CreateTime],[Label],[Order],[SlipDefinitionId],[Tags],[UpdateCount],[UpdatedBy],[UpdateTime],[Value])
VALUES (N’Title’, N’system’, GETDATE(), N’Title’, 1, @masterTableIdValueForCashDeposit, NULL, 0, NULL, NULL, N’Cash Deposit’)
INSERT INTO dbo.SlipDefinitionDetail([Area],[CreatedBy],[CreateTime],[Label],[Order],[SlipDefinitionId],[Tags],[UpdateCount],[UpdatedBy],[UpdateTime],[Value])
VALUES (N’LeftHeader’, N’system’, GETDATE(), N’Customer Number’, 2, @masterTableIdValueForCashDeposit, NULL, 0, NULL, NULL, N’#c.Cus.Id’)
INSERT INTO dbo.SlipDefinitionDetail([Area],[CreatedBy],[CreateTime],[Label],[Order],[SlipDefinitionId],[Tags],[UpdateCount],[UpdatedBy],[UpdateTime],[Value])
VALUES (N’LeftHeader’, N’system’, GETDATE(), N’Account Number’, 3, @masterTableIdValueForCashDeposit, NULL, 0, NULL, NULL, N’#c.C.AccountNumber’)
INSERT INTO dbo.SlipDefinitionDetail([Area],[CreatedBy],[CreateTime],[Label],[Order],[SlipDefinitionId],[Tags],[UpdateCount],[UpdatedBy],[UpdateTime],[Value])
VALUES (N’LeftHeader’, N’system’, GETDATE(), N’Transaction Date’, 4, @masterTableIdValueForCashDeposit, NULL, 0, NULL, NULL, N’#m.Today’)
INSERT INTO dbo.SlipDefinitionDetail([Area],[CreatedBy],[CreateTime],[Label],[Order],[SlipDefinitionId],[Tags],[UpdateCount],[UpdatedBy],[UpdateTime],[Value])
VALUES (N’LeftHeader’, N’system’, GETDATE(), N’Identity Number’, 5, @masterTableIdValueForCashDeposit, NULL, 0, NULL, NULL, N’#c.Cus.CustomerIdentifications[0].IdentityNumber’)
INSERT INTO dbo.SlipDefinitionDetail([Area],[CreatedBy],[CreateTime],[Label],[Order],[SlipDefinitionId],[Tags],[UpdateCount],[UpdatedBy],[UpdateTime],[Value])
VALUES (N’LeftHeader’, N’system’, GETDATE(), N’Issue Date’, 6, @masterTableIdValueForCashDeposit, NULL, 0, NULL, NULL, N’#m.Today’)
INSERT INTO dbo.SlipDefinitionDetail([Area],[CreatedBy],[CreateTime],[Label],[Order],[SlipDefinitionId],[Tags],[UpdateCount],[UpdatedBy],[UpdateTime],[Value])
VALUES (N’LeftHeader’, N’system’, GETDATE(), N’Reference’, 7, @masterTableIdValueForCashDeposit, NULL, 0, NULL, NULL, N’#m.BusinessKey’)
INSERT INTO dbo.SlipDefinitionDetail([Area],[CreatedBy],[CreateTime],[Label],[Order],[SlipDefinitionId],[Tags],[UpdateCount],[UpdatedBy],[UpdateTime],[Value])
VALUES (N’RightHeader’, N’system’, GETDATE(), N’Dear’, 8, @masterTableIdValueForCashDeposit, NULL, 0, NULL, NULL, N’#c.Cus.Name’)
INSERT INTO dbo.SlipDefinitionDetail([Area],[CreatedBy],[CreateTime],[Label],[Order],[SlipDefinitionId],[Tags],[UpdateCount],[UpdatedBy],[UpdateTime],[Value])
VALUES (N’RightHeader’, N’system’, GETDATE(), N’Address’, 9, @masterTableIdValueForCashDeposit, NULL, 0, NULL, NULL, N’Istanbul’)
INSERT INTO dbo.SlipDefinitionDetail([Area],[CreatedBy],[CreateTime],[Label],[Order],[SlipDefinitionId],[Tags],[UpdateCount],[UpdatedBy],[UpdateTime],[Value])
VALUES (N’LeftBody’, N’system’, GETDATE(), N’Sender Name’, 10, @masterTableIdValueForCashDeposit, NULL, 0, NULL, NULL, N’#c.Cus.Name’)
INSERT INTO dbo.SlipDefinitionDetail([Area],[CreatedBy],[CreateTime],[Label],[Order],[SlipDefinitionId],[Tags],[UpdateCount],[UpdatedBy],[UpdateTime],[Value])
VALUES (N’LeftBody’, N’system’, GETDATE(), N’Location’, 11, @masterTableIdValueForCashDeposit, NULL, 0, NULL, NULL, N’Mobile Branch’)
INSERT INTO dbo.SlipDefinitionDetail([Area],[CreatedBy],[CreateTime],[Label],[Order],[SlipDefinitionId],[Tags],[UpdateCount],[UpdatedBy],[UpdateTime],[Value])
VALUES (N’LeftBody’, N’system’, GETDATE(), N’Comment’, 12, @masterTableIdValueForCashDeposit, NULL, 0, NULL, NULL, N’#c.C.Comment’)
INSERT INTO dbo.SlipDefinitionDetail([Area],[CreatedBy],[CreateTime],[Label],[Order],[SlipDefinitionId],[Tags],[UpdateCount],[UpdatedBy],[UpdateTime],[Value])
VALUES (N’BottomFooter’, N’system’, GETDATE(), N’Amount’, 13, @masterTableIdValueForCashDeposit, N’Amount’, 0, NULL, NULL, N’#c.C.Amount’)
INSERT INTO dbo.SlipDefinitionDetail([Area],[CreatedBy],[CreateTime],[Label],[Order],[SlipDefinitionId],[Tags],[UpdateCount],[UpdatedBy],[UpdateTime],[Value])
VALUES (N’BottomFooter’, N’system’, GETDATE(), N’Currency’, 14, @masterTableIdValueForCashDeposit, NULL, 0, NULL, NULL, N’#c.C.CurrencyCode’)
LookupDbContext is used for reading other services most demanded data which is copied to a LookupDb which is stored next to the other Dbs in MsSql instance. LookupDbContext also eliminates needs of defining lots of Change Data Capture connectors.
If you need to copy data that belongs to another microservice, you can create a trigger than you can get data as a readonly from lookup api db by using LookUpDbContext. Adding new entity to LookupDb needs approval from Fimple Infra Team.
public class FiLookupDbContext : FiBaseLookupDbContext
{
public FiLookupDbContext(FiOptionsSingleton fiOptionsSingleton, ISessionContextDI sessionContextDi)
: base(fiOptionsSingleton, sessionContextDi)
{
}
public DbSet<CustomerLookup> CustomerLookups { get; set; }
public DbSet<CustomerCommunicationLookup> CustomerCommunicationLookups { get; set; }
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
var sqlServerOptions = optionsBuilder.UseSqlServer(DefaultLookupConnectionString
, sqlServerOptions =>
{
sqlServerOptions.CommandTimeout(TimeoutConstants.DefaultSqlServerCommandTimeout);
sqlServerOptions.EnableRetryOnFailure();
});
}
}
! Note: You do not need to add entity and configuration file to LookupDbContext ModelBuilder. These are will be added automatically.
dotnet ef migrations add test –startup-project Fi.Catalog.Api.csproj –context FiCatalogDbContext
! Note !: LookupDbContext should be use only for readonly operations. Do not save any changes.
! Note !: DO NOT CREATE A MIGRATION by using LookupDbContext! Note !: Do not forget copy old data to Lookup database
Data manupulations and data retrivals are coded Command and Query area of your Api project. All DB access must only be in commands and queries.
Entity and related EntityConfigurator classes are defined under Domain/Entity.
If you want to use other database server like as Postgresql or Couchbase, you must set ConnectionString, HostName, etc. are defined in appsettings.json.
Default Settings are Mssql Server at ApiBase appsettings.json file so you do not need to set this settings.
You can use both ToListAsNoTrackingAsync and ToListAsNoTrackingNoPaginationAsync methods for getting list data. ToListAsNoTrackingAsync and ToListAsNoTrackingNoPaginationAsync get only 1000 rows by default. If you want more than 1000 rows, use ToListAsNoTrackingNoPaginationAsync and set queryMaxDataRowCount as null to get limitless data row.
If you use EFCore ToListAsync method and if your query do not have tracking needs, be sure to add AsNoTracking().
If you use base methods (ToListAsNoTrackingAsync,ToListAsNoTrackingNoPaginationAsync etc…), ‘WITH (NOLOCK) ‘ scripts adds to generated queries by EF automatically.
Sample Generated Query :
If you do not use base methods like above, you can use WithNoLock extension methods
Note: This feature is disabled as a default. You can enable from appsettings.json or environment variables.
When get data for view and you do not need to change data, you must use AsNoTracking at Query API’s.
As you see ToListAsNoTrackingAsync Method has some parameters. All Query API’s are supported pagination, so sessionDI.MessageContext parameter is required for this method.
When update or delete data at Command API’s, AsNoTracking must not be used.
When updating a record, firstly it is preferred to check if the record exists in DB or not. It is essential for EFCore to track the selected entity to be able to update it.
You are sending the model with same values with db entity but only updating SortId. EFCore updates only SortId.
public async Task<ActionDefinitionOutputModel> Handle(UpdateActionDefinitionCommand request, CancellationToken cancellationToken)
{
sessionDI.ExecutionTrace.InitTrace();
request.Model.Id = request.Id;
var mapped = mapper.Map<ActionDefinition>(request.Model);
var fromDb = await dbContext.Set<ActionDefinition>().FirstOrDefaultAsync(x => x.Id == request.Model.Id);
if (fromDb == null)
throw ExceptionFactory.BadRequestEx(BaseErrorCodes.ItemDoNotExists, “ActionDefinition”, request.Model.Id);
await dbContext.UpdatePartial(fromDb, mapped); // Updates only SortId.
// dbContext.Update(fromDb); // Do not write this line or your sql script will be generated to update all the column.
await dbContext.SaveChangesAsync();
return mapper.Map<ActionDefinitionOutputModel>(fromDb);
}
Generated update script (Tracker does not get unchanged values):
SET NOCOUNT ON;
UPDATE [ActionDefinition] SET [SortId] = @p0
WHERE [Id] = @p1;
SELECT @@ROWCOUNT;
Note: If you change the entity in code without any mapping, then you do not need to call Set Values or Update methods. SaveChangesAsync() is enough for updating data.
public async Task<ActionDefinitionOutputModel> Handle(UpdateActionDefinitionCommand request, CancellationToken cancellationToken)
{
sessionDI.ExecutionTrace.InitTrace();
request.Model.Id = request.Id;
var fromDb = await dbContext.Set<ActionDefinition>().FirstOrDefaultAsync(x => x.Id == request.Model.Id);
if (fromDb == null)
throw ExceptionFactory.BadRequestEx(BaseErrorCodes.ItemDoNotExists, “ActionDefinition”, request.Model.Id);
fromDb.Description = “Desc Updated”;
await dbContext.SaveChangesAsync();
return mapper.Map<ActionDefinitionOutputModel>(fromDb);
}
If you use AutoMap, while dbContext.Update you get this error: System.InvalidOperationException: The instance of entity type ‘ActionDefinition’ cannot be tracked because another instance with the key value ‘{Id: 10}’ is already being tracked.
public async Task<ActionDefinitionOutputModel> Handle(UpdateActionDefinitionCommand request, CancellationToken cancellationToken)
{
sessionDI.ExecutionTrace.InitTrace();
request.Model.Id = request.Id;
var fromDb = await dbContext.Set<ActionDefinition>().FirstOrDefaultAsync(x => x.Id == request.Model.Id);
if (fromDb == null)
throw ExceptionFactory.BadRequestEx(BaseErrorCodes.ItemDoNotExists, “ActionDefinition”, request.Model.Id);
fromDb = mapper.Map<ActionDefinition>(request.Model);
dbContext.Update(fromDb);
await dbContext.SaveChangesAsync();
return mapper.Map<ActionDefinitionOutputModel>(fromDb);
}
If you do not read related record from db, EFCore does not track the record and updates all the columns.
public async Task<ActionDefinitionOutputModel> Handle(UpdateActionDefinitionCommand request, CancellationToken cancellationToken)
{
sessionDI.ExecutionTrace.InitTrace();
request.Model.Id = request.Id;
//var fromDb = await dbContext.Set<ActionDefinition>().FirstOrDefaultAsync(x => x.Id == request.Model.Id);
//if (fromDb == null)
// throw ExceptionFactory.BadRequestEx(BaseErrorCodes.ItemDoNotExists, “ActionDefinition”, request.Model.Id);
var entity = mapper.Map<ActionDefinition>(request.Model);
dbContext.Update(entity);
await dbContext.SaveChangesAsync();
return mapper.Map<ActionDefinitionOutputModel>(entity);
}
Generated update script:
SET NOCOUNT ON;
UPDATE [ActionDefinition] SET [ActionType] = @p0, [CommandName] = @p1, [Description] = @p2, [IconPath] = @p3, [Name] = @p4, [OperationType] = @p5, [SortId] = @p6, [UpdateCount] = @p7, [UpdateTime] = @p8, [UpdatedBy] = @p9
WHERE [Id] = @p10;
SELECT @@ROWCOUNT;
Important: If you do not send all columns in your model, and if you do not get record from Db, EFCore does not track record and updates all the columns with default values.
Important: If you have nested objects / lists in your model and changed some of them, and if you do not get record from Db, EFCore does not track record and does changes according to your model. In this case you do not send deleted items in your model, so EFCore keeps this item in Db, this case is not desired.
If you want to update some of the fields of your record then you can create another model class and set to related entity to update only changed data.
public async Task<ActionDefinitionOutputModel> Handle(UpdateActionDefinitionCommand request, CancellationToken cancellationToken)
{
sessionDI.ExecutionTrace.InitTrace();
request.Model.Id = request.Id;
ActionDefinitionInputModelX newModel = new ActionDefinitionInputModelX();
newModel.Id = request.Model.Id;
newModel.SortId = request.Model.SortId;
var fromDb = await dbContext.Set<ActionDefinition>().FirstOrDefaultAsync(x => x.Id == request.Model.Id);
if (fromDb == null)
throw ExceptionFactory.BadRequestEx(BaseErrorCodes.ItemDoNotExists, “ActionDefinition”, request.Model.Id);
await dbContext.UpdatePartialWithModel(fromDb, newModel);
await dbContext.SaveChangesAsync();
return mapper.Map<ActionDefinitionOutputModel>(fromDb);
}
Generated update script:
SET NOCOUNT ON;
UPDATE [ActionDefinition] SET [SortId] = @p0
WHERE [Id] = @p1;
SELECT @@ROWCOUNT;
If you have nesned objects / lists in your model, and there are cases of adding, removing and updating the list according to your business needs. You can use the following metod to updated the nested object.
public async Task<ProductInfoOutputModel> Handle(UpdateProductCommand request, CancellationToken cancellationToken)
{
request.Model.Id = request.ProductId;
sessionDI.ExecutionTrace.InitTrace();
//Select Main Entity with Nested List
var fromDb = await dbContext.Set<Product>()
.Include(x => x.ProductFeeSettings)
.Include(x => x.ProductBranchSettings)
.FirstOrDefaultAsync<Product>(x => x.Id == request.ProductId);
if (fromDb == null)
throw ExceptionFactory.BadRequestEx(BaseErrorCodes.ItemDoNotExists, “Product”, request.ProductId);
request.Model.Version = fromDb.Version;
request.Model.IsLatestVersion = fromDb.IsLatestVersion;
var mapped = mapper.Map<Product>(request.Model);
//If Your Entity Inheritance From IFiEntityWithIdentity, you can use this overload method “UpdateList”
mapped.ProductFeeSettings.ForEach(x => x.ProductId = request.ProductId);
await dbContext.UpdateList<ProductFeeSetting, int>(fromDb.ProductFeeSettings.ToList(),
mapped.ProductFeeSettings);
//If your Unique Id different from Default Identity column, you must define “idSelector” like as below, and pass this Func overload method “UpdateList”
Func<ProductBranchSetting, int> idSelectorSourceBranch = src => src.BranchId;
mapped.ProductBranchSettings.ForEach(x => x.ProductId = request.ProductId);
await dbContext.UpdateList<ProductBranchSetting, int>(fromDb.ProductBranchSettings.ToList(),
mapped.ProductBranchSettings,
idSelectorSourceBranch);
//CurrentValues.SetValues method just only update main entity, in this example it is “Product”
await dbContext.UpdatePartial(fromDb, mapped);
await dbContext.SaveChangesAsync();
var productInfoOutputModel = mapper.Map<ProductInfoOutputModel>(fromDb);
return productInfoOutputModel;
}
If you need to update just only some properties on model, you can use Patch API.
You should use ApplyTo method for mapping to model.
public async Task<Account> PatchAccountAsync(long accounNumber, JsonPatchDocument<AccountInputModel> patchModel)
{
sessionDI.ExecutionTrace.InitTrace();
//Get Account From Db By Account Number
var account = await CheckAccount(accounNumber);
//Map From Entity To Input Model & Run Patch ApplyTo
var accountInputModel = mapper.Map<AccountInputModel>(account);
patchModel.ApplyTo(accountInputModel);
//Map Input Model To New Entity & UpdatePartial
var mapped = mapper.Map<Account>(accountInputModel);
await dbContext.UpdatePartial(account, mapped);
await dbContext.SaveChangesAsync();
return account;
}
Your tables are actually your entities. When creating an entity system will create a table in the Db.
Code First approach is used in Data Layer. Click here for detailed information.
Entity (table) designs are not approved by a Data Architect or Chief Architect. Teams are responsible for entity designs. Chief Architect can check entity designs by sampling few of the tables in DBs.
Entities names should be in PascalCase.
Do not use “s” suffix at the end of entity name.
Avoid using Definition suffix for the entities. Only use if the related name is very generic like Api, Action, Resource etc. Samples: ApiDefinition, ActionDefinition, ServiceDefinition, Country, City, Currency.
Before creating your models and entities please check Data Dictionary&Db Rules section.
While creating models and entities, it is important to decide multi language fields from the very beginning. Multi language fields should store Json String. Sample: { “tr”:”Adı”, “en”: “Name”, “ru”: “ади” }
When you create a new entity, you can use below base classes and interfaces for this entity.
This class can be used when default fields in tables are not needed.
In this case, the identity “Id” column does not come from base and you should manage it if you need it.
Generally not recommended for business projects.
As an example, you can review the Document Entity
This class can be used when default fields in tables are needed . But “Id” column is not included.
In this case, the identity “Id” column does not come from base and you should manage it if you need it.
Generally it is used when you need default fields but not an identity column. Or, when you want to use composite key instead of an identity column.
Default Fields are below
These fields value are given automatically by persistence layer.
Contains “Id” column in addition to the base class “EntityBaseWithBaseFields”.
Id column is managed by persistence layer.
Contains State, DeletedBy, and DeleteTime columns in addition to the base class “EntityBaseWithBaseFields”.
If you want to check the state of your record, you can use this class. But not recommended for complex state situations.
When you use this class, your deletions are done as soft delete
Entity States are below
Contains “Id” column in addition to the base class “EntityBaseWithBaseFieldsWithState”.
Id column is managed by persistence layer.
This interface should be added to the entities that need row version control.
After adding Row Version, concurrent update of a record by multiple users is controlled by the persistence layer. Be careful when you want to use it, you should use it for the main table, not for the detail tables.
This interface should be added to the entities that need application code for service banking.
Application Code value are given automatically by persistence layer. If Tenant and Application are service banking, it is automatically added to the filter in your select queries.
For all fields, IsRequired, MaxLength, IsUnique, etc. should be defined.
Related entities (PF/FK) should be bound with required relation rule.
The FK type is added to the table as “Cascade” by default. This means that when a record is deleted in the main table, the related record/s are also deleted in the detail table.
FK is created by EFCore as “Cascade” for navigation properties. Even if you don’t define an FK relationship on your Configurator.
If a relationship is established between the definition table and the transaction table, FK Type “No Action” must be set so that the definition table is not affected when a record is deleted from the transaction table.
Code View For NoAction :
builder.HasOne(p => p.Parent)
.WithMany()
.HasForeignKey(x => x.ParentId)
.IsRequired(false)
.OnDelete(DeleteBehavior.Restrict);
When a record is deleted from the table, the relevant Column is set to NULL in the Main table according to the business need.
Code View For NULL :
builder.HasMany(p => p.POSTransactions)
.WithOne(y => y.Workgroup)
.HasForeignKey(p => p.TransactionWorkgroupId)
.IsRequired(false)
.OnDelete(DeleteBehavior.SetNull);
Do not prefer ForeignKey attribute, because it set Cascade option by default for foreign keys. Use EF Core Fluent API like above.
When you Save a Record, below fields are being saved to DB automatically by the DBContext (The fields vary according to the types of IFiEntity).
Every entity must have an Id field and this field must be PK.
Entities which inherited from EntityBaseWithBaseFieldsWithIdentity has an Id column which is also PK of the table. Do not add another Id column to your entity. Wrong: FecId.
For PK/FK relations, add virtual objects to related entities. And also detail class a foreignkey field like MainEntityNameId.
public virtual List<ApiParameterDefinition> Parameters { get; set; } = new List<ApiParameterDefinition>();
public virtual ApiDefinition ApiDefinition { get; set; }
public virtual List<ApiParameterPropertyDefinition> Properties { get; set; } = new List<ApiParameterPropertyDefinition>();
If the entity is a definition entity like Resource, Action, Event, etc. then Id column is not enough for data transport operations. So also consider to add a Code, Key or UniqueName field in your entity. Try to choose only one of Code, Key or UniqueName fields. While choosing Code, Key or UniqueName, look at entity and your concept to decide the best option.
When creating Database Model you must check this table.
If your data type in the table, you must choose available type, related to your purpose.
Check for other pre-defined data types: Fi.Persistence.Relational.Domain.DataTypes
This section applies to all holding and showing currency decimal values like amount, rate, balance.
In database, always we need to store rounded value.
Rounding must be done according to currency, not per country etc.
Rounding must be done as a last step of all calculations. Rounding is not an optinal step. It is mandatory.
All amount and balance values of a currency.
nvm version ^0.39.1
Fimple has microservice architecture and uses .Net Core 6.0.
Fimple architecture supports On-Premises, Hybrid and Cloud installations.
nvm version ^0.39.1