Prerequisite knowledge：
microsoft-sql-learning
SQL : Structured Query Language.
Programming languages can be categorized as procedural or declarative. Procedural languages enable you to define a sequence of instructions that the computer follows to perform a task. Declarative languages enable you to describe the output you want, and leave the details of the steps required to produce the output to the execution engine.
One important feature to note about the set theory is that there is no specification regarding any ordering of the members of a set. If you need to return results in a certain order, you must specify it explicitly by using an ORDER BY clause in your SELECT query.
SQL statements are grouped together into several different types of statements.
- Data Manipulation Language(DML) is the set of SQL statements that focus on querying and modifying data.
- Data Definition Language(DDL) is the set of SQL statements that handles the definition and life cycle of database objects, such as tables, views, and procedures.
- Data Control Language(DCL) is the set of SQL statements used to manage security permissions for users and objects.
The order in which a SELECT statement is written is not the order in which it is evaluated and processed by the SQL Server database engine.
Consider the following guidelines to make your T-SQL code easily readable:
- Capitalize T-SQL keywords, like SELECT, FROM, AS and so on. Capitalizing keywords is a commonly used convention that makes it easier to find each clause of a complex statement.
- Start a new line for each major clause of a statement
- If the SELECT list contains more than a few columns, expressions, or aliases, consider listing each column on its own line.
- Indent lines containing subclauses or columns to make it clear which code belongs to each major clause.
A Null value means no value or unknown. It does not mean zero or blank, or even an empty string.
Multiple conditions: AND operators are processed before OR operators, unless parentheses are used.
JOIN or subquery? One restriction you should keep in mind is that when using a nested query, the results returned to the client can only include columns from the outer query. So if you need to return columns from both tables, you should write the query using a JOIN.
GROUP BY errors:
A common obstacle to becoming comfortable with using GROUP BY in SELECT statements is understanding why the following type of error message occurs:
Msg 8120, Level 16, State 1, Line 2 Column <column_name> is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
SELECT CustomerID, PurchaseOrderNumber, COUNT(*) AS OrderCount
FROM Sales.SalesOrderHeader
GROUP BY CustomerID;
The query will return error because PurchaseOrderNumber isn't part of the GROUP BY, and it isn't used with an aggregate function.
Compare HAVING to WHERE:
While both HAVING and WHERE clauses filter data, remember that WHERE operates on rows returned by the FROM clause. If a GROUP BY…HVING section exists in your query following a WHERE clause, the WHERE clause will filter rows before GROUP BY is processed — potentially limiting the groups that can be created.
什么是ORM：
https://learn.microsoft.com/en-us/ef/core/
ORM：Object Relational Mapping。让开发者用对象操作的形式操作关系数据库。
比如插入：User user = new User(){Name="admin", Password="123"}; orm.Save(user);
比如查询：Book b = orm.Books.Single(b => b.id == 3 || b.Name.Contains(".NET")); string bookName = b.Name
有哪些ORM：EF core、Dapper、SqlSugar、FreeSql等。
EF Core 与其他ORM的区别：
1、Entity Framework Core是微软官方的ORM框架。功能强大、官方支持、生产效率高、力求屏蔽底层数据库差异。缺点：复杂、上手门槛高、不熟悉EFCore遇到问题分析比较复杂。
2、Dapper。简单，上手快，行为可预期性强。缺点：生产效率低，需要处理底层数据库差异。
3、EF Core是模型驱动（Model-Driven）的开发思想，Dapper是数据库驱动（DataBase-Driven）的开发思想的。没有优劣，只有比较。
4、性能：Dapper不等于性能高、EF Core不等于性能差。
5、EF Core是官方推荐、推进的框架，尽量屏蔽底层数据库差异，.NET开发者必须熟悉，根据的项目情况再决定具体使用哪个ORM。
选择：
1、对于后台系统、信息系统等和数据库相关开发工作量大的系统，且团队比较稳定用EFCore；对于互联网系统等数据库相关工作量不大的系统，或者团队不稳定，用Dapper。
2、在项目中可以混用，需要注意EF Core的缓存、Tracking等问题即可。
用什么数据库：
1、EFCore是对于底层ADO.NET Core的封装，因此ADO.NET Core支持的数据库不一定被EF Core支持。
2、EF Core支持所有主流的数据库，包括SQL Server、Oracle、MySQL、PostgreSQL、SQLite等。可以自己实现Provider支持其他数据库。
3、对于SQLServer支持最完美，MySQL、PostgreSQL也不错。这三者是.NET中用的最多的三个。
开发环境搭建：
https://youtu.be/cPpim_K2h9g?si=7ro4RNhNvD0gOHob
1、经典步骤：建实体类，建配置类，建DbContext；生成数据库；编写调用EF Core的业务代码。
概念：Migration数据库迁移
VS Code，和ef工具可以参考的：https://learn.microsoft.com/en-us/ef/core/get-started/overview/install
面向对象的ORM开发中，数据库不是程序员手动创建的，而是由Migration工具生成的。关系数据库只是盛放模型数据的一个媒介而已，理想状态下，程序员不用关心数据库的操作。
根据对象的定义变化，自动更新数据库中的表以及表结构的操作，叫做Migration。
迁移可以分为多步（项目进化），也可以回滚。
搭建Migration
1、在CLI中执行如下指令，生产操作数据库的C#代码
dotnet ef migrations add //对类进行自动编译
2、代码需要执行后才会应用对应数据库的操作，执行如下指令
dotnet ef database update [migration_name] // 把类更新到数据库
主要约定配置：
1、表名采用DbContext中的对应的DbSet的属性名。
2、数据表列的名字采用实体类属性的名字，列的数据类型采用和实体类型最兼容的类型。
3、数据列表的可空性取决于对应实体类属性的可空性。
4、名字为Id的属性为主键，如果主键为short，int或者long类型，则默认采用自增字段，如果主键为Guid类型，则默认采用默认的Guid生成机制生成主键值。
两种配置方式（不建议混用，推荐使用FluentAPI）：
1、Data Annotation
把配置以特性（Annotation）的形式标注在实体类中。
简单，但是耦合
[Table("T_Books")]
public class Book
{
}
2、FluentAPI
builder.ToTable("T_Books");
把配置写到单独的配置类中。
复杂但是解耦
Fluent API:
1、视图与实体类映射：modelBuilder.Entity().ToView("blogsView");
2、排除属性映射：modelBuilder.Entity().Ignore(b => b.Age2);
3、配置列名：modelBuilder.Entity().Property(b=>b.BlogId).HasColumnName("blog_id");
4、配置列数据类型：builder.Property(e=>e.Title).HasColumnType("varchar(200)");
5、配置主键：默认把名字Id或者“实体类型+Id”的属性作为主键，可以用HasKey()来配置其他属性作为主键。modelBuilder.Entity().HasKey(c=>c.Number);支持复合主键，但不建议使用。
6、生成列的值：modelBuilder.Entity().Property(b=>b.Number).ValueGeneratedOnAdd();
7、可以用HasDefaultValue()为属性设置默认值：modelBuilder.Entity().Property(b =>b.Age).HasDefaultValue(6);
8、索引：modelBuilder.Entity().HasIndex(b => b.Url);
复合索引：modelBuilder.Entity().HasIndex(p => new {p.FirstName, p.LastName});
唯一索引：IsUnique(); 聚集索引：IsClustered()
………………
9、用EF Core高级性能的时候，尽量不要和业务逻辑混合一起。
primary key:
自增主键：
1、EF Core支持多种主键生成策略：自动增长、Guid、Hi/Lo算法等。
2、自动增长。优点：简单；缺点：数据迁移以及分布式系统中比较麻烦；并发性能差。long、int等类型的主键，默认是自增。因为是数据库生成的值，所以SaveChanges后会自动把主键的值更新到Id属性。
3、自增字段的代码中不能为Id赋值，不许保持默认值0，否则运行的时候就会报错。
Guid主键：
1、Guid算法（或UUID算法）生成一个全局唯一的Id。适合于分布式系统，在进行多数据库数据合并的时候很简单。优点：简单，高并发（不用锁），全局唯一；缺点：磁盘空间占用大。
2、Guid值不连续。使用Guid类型做主键的时候，不能把主键设置为聚集索引。因为聚集索引是按照顺序保存主键的，因此用Guid做主键性能差。比如MySQL的InnoDB引擎中主键是强制使用聚集索引的。有的数据库支持部分的连续Guid，比如SQLServer中的NewSequentialId()，但也不能解决问题。在SQLServer等中，不要把Guid主键设置为聚集索引；在MySQL中，插入频繁的表不要用Guid做主键。
3、演示Guid用法：既可以让EF Core给赋值，也可以手动赋值（推荐）。
其他方案：
1、混合自增和Guid（非复合主键）。用自增列做物理的主键，而用Guid列做逻辑上的主键。把自增列设置为表的主键，而在业务上查询数据时候把Guid当主键用。在和其他表关联以及和外部系统通讯的时候（比如前端显示数据标识的时候）都是使用Guid列。不仅保证了性能，而且利用了Guid的优点，而且减轻了主键自增性导致主键值可被预测带来的安全性问题。
2、Hi/Lo算法：EF Core支持Hi/Lo算法来优化自增列。主键值由两部分组成：高位Hi和低位Lo，高位由数据库生成，两个高位之间间隔若干个值，由程序在本地生成低位，低位的值在本地自增生成。不同进程或者集群中不同服务器获取的Hi值不会重复，而本地进程计算的Lo则可以保证可以在本地高效率的生成主键值。但是HiLo算法不是EfCore的标准。
深入研究Migrations：
1、使用迁移脚本，可以对当前连接的数据库执行变化的迁移，这个操作叫做“向上迁移”UP，也可以执行向下迁移DOWN，把数据库回退到旧的迁移。
2、除非有特殊需要，否则不要删除Migrations文件夹下的代码。
3、数据库中会有个__EFMigrationsHistory表，记录每次更新的记录和名字。
可以使用script migration相关的指令生成两个版本或者某个版本之后的SQL语句。
反向工程：
1、根据数据库表来反向生成实体类，例如，旧项目更新的时候，先有数据库表，生成实体类。
// 依据使用的不同的库有不同的命令，需要自己进行查询。
dotnet ef dbcontext scaffold "Server=localhost;User=root;Password=1234;Database=ef" "Pomelo.EntityFrameworkCore.MySql"
注意：
1、生成的实体类可能不能满足项目的要求，可能需要手工修改或者增加配置。
2、再次运行反向工程工具，对文件所做的任何更改都将丢失。
3、不建议吧反向工具当成日常开发工具使用，只适合旧工程改造成新技术。
EFCore如何操作数据库：
应用程序 --(C#)--> EFCore/Dapper --(SQL)--> ADO.NET Core -> 数据库
EF Core把C#代码转化为SQL语句的框架。
C# -> EF Core -> AST（抽象语法树） -> SQLServer EF Core Provider -> SQL Server ADO.NET Provider
-> MySQL EF Core Prover -> MySQL ADO.NET Provider
查看生成的SQL语句：
1、SQL Server Profiler(付费版本)查看SQLServer数据库所有执行的SQL语句。
2、标准日志：
public static readonly ILoggerFactory;
MyLoggerFactory = LoggerFactory.Create(Builder => Builder.AddConsole(););
optionsBuilder.UseLoggerFactory(MyLoggerFactory);
3、简单日志：
// 相当于把整个EF Core的日志加了一个新的输出流
optionsBuilder.LogTo(msg => Console.WriteLine(msg););
4、TOQUERYSTRING
上面的方法无法直接得到一个操作的SQL语句，而且在操作很多的情况下，容易混乱。
EF Core的Where方法返回的是IQueryable类型，DbSet也实现了IQueryable接口。IQueryable有扩展方法ToQueryString可以获取SQL。
不用执行语句就能获取需要的SQL语句，但是只能获取查询操作的SQL语句。
EF Core做不到的事：
C#有的符合C#语法的语句无法被翻译成为SQL语句。
同样LINQ被翻译成不同的SQL语句：
1、不同数据库方言不同，同样的C#语句在不同数据库中被EF Core翻译成不同的语句。
postgresql的使用者越来越多
EF Core关系配置：
EF Core不仅支持单实体操作，更支持多实体的关系操作。
三部曲：实体类中关系属性；FluentAPI关系配置；使用关系操作；
EF Core中实体之间关系的配置（配置在任何一方都可以）：
考虑到有单项导航属性的可能，我们一般用HasOne().WithMany()配置在多的一端。
HasXXXX().WithXXXX();
一对多：HasOne().WithMany();
// 在comment类中添加属性Article是一对多关系
builder.HasOne<Article>(c => c.TheArticle).WithMany(a => a.Comments).IsRequired();
一对一：HasOne().WithOne();
必须显示的在其中一个实体类中声明一个外键属性。
builder.HasOne(x => x.Order).WithOne(x => x.Delivery).HasForeignKey<Delivery>(x => x.OrderId);
多对多：HasMany().WithMany();
需要中间表，从EF Core5.0开始支持。usingEntity是用来设置表名；
builder.HasMany<Teacher>(e => e.Teachers).WithMany(e => e.Students).UsingEntity(e => e.ToTable("T_Students_Teachers"));
设置外键属性，这样可以直接获取外键（除非必要，不要引入）：
1、在实体类中显式声明一个外键属性；
2、关系配置中通过HasForeignKey(c => c.ArticleId)指定这个属性为外键；
3、除非必要，否则不用声明，因为会引入重复；
双向导航属性：Article <——> Comments
单项导航属性：User <——— Employee
<——— HR
对于主从结构的一对多表关系，一般是声明双向导航属性。而对于其他的一对多表关系：如果表属于被很多表引用的基础表，则用单项导航属性，否则可以自由决定是否用双向导航属性。
单项属性配置方法：
不设置反向的属性，然后配置的时候WithMany()不设置参数即可。
IEnumerable and IQueryable:
1、对普通集合和SbSet调用的Where方法，虽然用起来一样，但是“转到定义”后看到的是不同接口的方法。
2、普通集合的版本（IEnumerable）实在内存中过滤（客户端评估），而IQueryable版本则是把查询操作翻译成SQL语句（服务器端评估）。通常情况下，如果数据量比较大，服务端评估的效率更高。但是偶尔有一些操作本地更快，或者无法翻译成SQL的C#可以考虑先获取到内存，再进行客户端评估。
List<Comments> comments = new List();
// IEnumrable这里所有的操作在内存中取出每条Message，然后进行Contains对比。
comments.Where(c => c.Message.Contains("microSoft"));
// IQueryable这里只是把以下这句C#语句翻译成SQL，然后丢给对应的数据库处理，本身不在内存中处理。
var cs = ctx.Comments.Where(c => c.Message.Contains("microSoft"));
IQueryable:
1、Qqueryable只是代表一个“可以放到数据库服务器去执行的查询”，它没有立即执行，只是“可以被执行”而已。
2、对于IQueryable接口调用非终结方法的时候不会执行查询，而调用终结方法的时候会立即执行查询。
3、终结方法：遍历、ToArray、ToList、Min、Max、Count等
4、非终结方法：OrderBy、Include、Skip、Take等
5、简单判断：一个方法的返回值类型如果是IQueryable类型，那么这个方法一般就是非终结方法，否则就是终结方法。
为什么延迟执行：
1、可以在实际执行之前，分步构建IQueryable，等所有条件都确定之后，再执行最后的查询；例如：根据不同的情况来构建不同的查询，就可以分步构建查询。
void QueryArticles(string searchWords, bool searchAll, bool orderByPrice, double upperPrice)
{
using (MyDbContext ctx = new MyDbContext())
{
IQueryable<Article> articles = ctx.Articles.Where(x => x.Price <= upperPrice);
if (searchAll == true)
{
articles = articles.Where(x => x.Title.Contains(searchWords) || x.Message.Contains(searchWords));
}
else
{
articles = articles.Where(x => x.Title.Contains(searchWords));
}
if (orderByPrice == true)
{
articles = articles.OrderBy(x => x.Price);
}
foreach (var article in articles)
{
Console.WriteLine(article.Title);
}
}
}
分页加载的实现：
1、Skip Take最好显示指定排序规则
2、需要知道满足条件的数据的总条数：用IQueryable的复用
3、页数
IQueryable底层是如何读取数据的？
1、DataReader：分批从数据库服务器读取数据。内存占用小、DB连接占用时间长。
2、DataTable：把所有数据都一次性从数据库服务器都加载到客户端内存中。内存占用大，节省DB连接。
验证IQueryable用了那种方式：读取数据库的过程中，关闭数据库，查看是否会报错。
结论：IQueryable内部默认就是在调用DataReader的方式。优点：节省内存资源；缺点：如果处理的慢，会长时间占用连接。
是否可以一次性把所有资源都加载到内存中？可以，使用ToArray、ToList等方法或异步方法，一次性把数据读取到内存中。
为何要一次性把资源加载到内存中？
1、遍历IQueryable进行数据处理的过程很耗时；
2、如果方法需要返回查询结果（IQueryable对象），并且在方法里要销毁DBContext（方法已经返回，释放了DBContext，但是真正的遍历没有开始）。
3、多个IQueryable的遍历嵌套。很多数据库的ADO.NET Core Provider是不支持多个DataReader同时执行的。MultipleActiveResultSets=true可以支持，但是只在SQL Server中有效。
IQueryable的这些异步的扩展方法都是“立即执行”方法，而GroupBy、OrderBy、Join、Where等“非立即执行”方法则没有对应的异步方法。因为“非立即执行”方法并没有实际执行SQL语句，并不是消耗IO的操作。
EF Core执行非查询原生SQL语句：
1、尽管EF Core已经非常强大，但是仍然存在着无法被写成标准EF Core调用方法的SQL语句，少数情况下仍然需要写原生SQL；
2、可能无法跨数据库；
3、三种情况：非查询语句、实体查询、任意SQL查询；
使用dbCtx.Database.ExecuteSqlInterpolated()方法即可。
并且不会有SQL注入攻击漏洞：字符串内插值如果赋值给string变量，就是字符串拼接；字符串内插如果赋值给FormattableString变量，编译器就会构造FormattableString对象。
实体相关SQL：
如果要执行的原生SQL是一个查询语句，并且查询的结果也能对应一个实体，就可以调用对应实体的DbSet的FromSqlInterpolated()方法来执行一个SQL查询语句，同样适用字符串内插来传递参数。
1、FormSqlInterpolated()方法的返回值是IQueryable类型的，因此我们可以在实际执行IQueryable之前，对IQueryable进行进一步的处理；
2、把只能用原生SQL语句写的逻辑用FromSqlInterpolated()去执行，然后把分页、分组、二次过滤、排序、include等其他逻辑尽可能仍然使用EF Core的标准操作去实现；
局限性：
1、SQL查询必须返回实体类型对应数据库表的所有列；
2、结果集中的列名必须与属性映射到的列名称匹配；
3、只能单表查询，不能使用Join语句进行关联查询。但是可以在查询后使用Include来进行关联数据的获取；
执行任意SQL：
dbCtx.Database.GetDbConnect()获得ADO.NET Core的数据库连接对象。推荐使用Dapper等框架执行原生复杂查询SQL。注意不要用存储过程，项目复杂查询很多，存储过程建很多类在DbSet中，会导致DbSet中的非实体对象过多。
总结：一般Linq操作就够了，尽量不用写原生SQL
1、非查询SQL用ExecuteSqlinterpolated
2、针对实体的SQL查询用FromSqlInterpolated
3、复杂SQL查询用ADO.NET或者Dapper
如何知道实体数据变化？
1、实体类没有实现属性值改变的通知机制，EF Core是通过“快照更改跟踪”机制来检测数据变化。首次跟踪一个实体的时候，EF Core会创建这个实体的快照。执行SaveChanges等方法的时候，EF Core会把存储的快照中国呢的值与实体的当前值进行比较。
只要一个实体对象和DbContext发生任何的关系（查询、add等），都默认被DbContext跟踪。
实体的状态：
1、已添加（Added）：DbContext正在跟踪实体，但数据库中尚不存在该实体；
2、未改变（Unchanged）：DbContext正在跟踪此实体，该实体存在于数据库中，其属性值和数据库中读取到的值一致，未发生改变；
3、已修改（Modified）：DbContext正在跟踪此实体，并存在于数据库中，并且其部分或全部属性值已修改；
4、已删除（Deleted）：DbContext正在跟踪此实体，并存在于数据库中，但在下次调用SaveChanges要从数据库中删除对应数据；
5、已分离（Detached）：DbContext未跟踪该实体；
如果确认只是进行查询和展示，不涉及任何的改变，可以调用AsNoTracking不进行跟踪，节省内存。
EF Core批量删除和更新，EF Core7.0之后：
https://learn.microsoft.com/en-us/ef/core/saving/execute-insert-update-delete
全局查询筛选器：
EF Core会自动将这个查询筛选器应用于涉及这个实体类型的所有LINQ查询，也就是查询的时候会自动加上此查询条件。
配置全局过滤器：builder.HasQueryFilter(a => a.IsDeleted == false)
单挑查询忽略全局过滤器：ctx.Articles.IgnoreQueryFilter();
缺点：全局筛选器的性能陷阱，可能会在不同的数据库中索引和查询条件导致的性能降低。
EF Core的并发控制：
1、并发控制：避免多个用户同时操作资源造成的逼ing发冲突问题。例如：统计点击量。
2、最好的解决方案：非数据库解决方案。
3、数据库层面的两种策略：悲观、乐观。
最好的方式是不在数据库中进行并发控制，如果必须在数据库进行控制：
- 悲观控制：总是认为在操作的时候，其他人也会操作，所以操作之前先加锁，等操作完成后才解锁。悲观并发控制一般采用行锁、表锁等排他锁对资源进行锁定，确保同时只有一个操作使用者操作被锁定的资源。EF Core没有封装悲观并发控制的使用，需要开发人员编写原生SQL语句来使用悲观并发控制（不同数据库的语法不一样）。悲观锁并发控制使用比较简单、但是系统并发量大的话，由于锁是独占、排他的，会严重影响性能，使用不当还可能造成死锁。
MYSQL方案：select * from
Top comments (0)