Entity Framework Core

Config

• there are 2 ways for EF core to make configurations on table, 1. data annotation 2. Fluent API

  1. data annotation(easier but high coupling)

     

  2. fluent api(more code but low coupling, recommended)

     

• fluent API and data annotation doc: https://learn.microsoft.com/en-us/ef/core/modeling/

Index

• Add index in EF: https://learn.microsoft.com/en-us/ef/core/modeling/indexes
• Non-cluster Index: a table can have multiple non-cluster index. It is beneficial for data retrieving operations on specific columns
  1. Index
  2. Composite Index
  3. Unique Index
• Cluster Index: determine the actual arrangement of rows on physical disks, helpful for range queries， harmful for insertion, 1 table could only have 1 cluster index

Primary Key

• self-increment primary key: after save into db, ef will automatically update the primary key

  • perform badly in high concurrency insertion situation(all the transaction want to obtain the next primary key value)

  

  print: 0, updatedValue

• GUID primary key

  • good performance in high concurrency insertion situation

  • GUID value is not sequential, so you can not use it as cluster index!!!

  • in MySQL, the InnoDB engine forces primary key as the cluster index, so DO NOT use GUID as primary key when using MySQL!!!!!!!!!!!!!!!!

• Add GUID primary key data:

  

Not like self-increment primary key, the GUID is generated by EF core instead of the database. That’s why the GUID can be printed before the .SaveChangeAsync() method

• GUID-Self-Increment Primary Key Best Practice: use self-increment key as physical primary key, and use GUID key as logical primary key

Migration

• by using migration scripts, you can do up and down migrations to higher or lower version, so do not delete files inside the Migrations folder
• Migration Commands:
  • Update-Database xxx: roll back database into xxx state
  • Remove-migration
  • Script-Migration: used to generate SQL operating script from nothing to the latest migration, it allows you to customize your own SQL scripts. It only generates scripts, it won’t execute scripts for you. (this command is used help manually update database in production environment)
  • Script-Migration D F: generate script for update from migration D to migration F
  • Script-Migration D: generate script for update from migration D to the latest migration

EF and ADO.NET

• EF core translates C# syntax into SQL scripts and pass SQL scripts to the ADO.NET

  

EF Core and DB Compatibility

• Only DB with EF Core Provider can use EF Core to operate on it. If there is no EF Core Provider for a specific DB, you can only use ADO.NET to manipulate it using scripts

EF optimize extra columns

• by using select

  

EF One-to-Many Relationship

• One-to-Many Relationship insertion and config:

  In data model:

  

  In Fluent API(config it in the “many”‘s configuration file)

  

  Add Data

  

• One-To-Many Relationship query

  

• Explicitly name the foreign key:

  the EF will automatically generate the foreign key column for One-To-Many Relationship in database, but you could not use it until you explicitly add it into your FluentAPI and data model

  

  

• One-To-Many single navigation property:

  Sometimes, in some one-to-many relationship, we do not with to add the navigation properties to the one side for each of its many-related tables. In this case, we need to apply single navigation property to omit navigation property in the “one” table

  In Fluent API: pass no argument into WithMany() can omit the navigation property in one‘s side

EF One-to-One Relationship

• For one-to-one relationship in EF, you must explicitly declare at least one foreign key property and config it in the Fluent API. EF core will not automatically generate foreign key property for you like in many-to-many relationship

  In data model:

  

  In Config:

  

EF Many-to-Many Relationship

• How to use m-2-m in EF(only 1 side config is enough)

  In data model:

  

  In config:

  

IEnumerable and IQueryable

• IEnumerable‘s query is executed inside the memory(server side query) while IQueryable‘s query is translated into SQL scripts and executed inside database

• IQueryable has lazy execution, which means, only the terminating method of an IQueryable instance will make the query be truly executed

  Terminating Methods(if the return type of a method is IQueryable , then that method is not terminating method, otherwise, it is):

  • traversing
  • ToArray()
  • ToList()
  • Min()
  • Max()
  • Count()
  • …

• Use the lazy execution of IQueryable to gradually build a query and execute it in the end:

  

• A IQueryable can be reused or be used to construct new IQueryable

  

Pagination

• In EF Core, we can use skip() and take() methods to achieve pagination

  

• Get total page of a table

  

Async Method In EF Core

• Async methods for EF Core are all in the namespace Microsoft.EntityFrameworkCore, so using this namespace before using async EF Core methods

• Some async methods

  

• There are some methods in EF Core do not have corresponding async methods like all the non-terminating methods GroupBy(), OrderBy(), Skip()… The point is: only the IO related or terminating method would have their async methods to optimize their thread behavior

• async foreach

  1. use AsAsyncEnumerable()

  

  2. use ToListAysnc ToArraryAysnc

     

EF with SQL Script

• Insert(Directly execute SQL script in EF Core no longer need save() or saveAsync(), the SQL injection prevention is already embedded into this method )

• Instance Query

• Arbitrary scripts: use ADO.NET or Dapper

Tracking

• You can use Entry() method to get the tracking instance of an DB instance, the State attribute means the tracking state of the instance(unchanged, deleted, updated), and by DebugView.LongView attribute, we can see the changing information of an DB instance

  

• tracking optimization: sometimes we only need to query a data without updating, adding or deleting it. In this case, we can manually cancel the tracking to improve program performance by using AsNoTracking(), after that, the EntityEntry‘s state of that instance would be detached

  

Global Query Filter

• global query filter can be used to achieve soft delete and multi tenancy system

  achieve soft delete: global query filter helps add soft deleted checking criteria(in Fluent API config) automatically for every query

  

  you can also exclude the global query filter for some query by using IgnoreQueryFilters:

  

Expression Tree

• Create and use expression trees

  

• How to dynamically build expression tree(equivalent to e1 = b => b.Price > 5)

  

• Best Practice of dynamically building expression tree: using ToString from ExpressionTreeToString(a 3rd party library: https://github.com/zspitz/ExpressionTreeToString) to generate the code of an expression tree and then, manually modified that code to build your own expression tree

  

  

• The dynamical building of expression tree is not recommended

• Dynamically build query key without expression tree