什么是ORM?
ORM(Object Relational Mapping).让开发者用对象操作的形式操作数据库。
常见的ORM:EF Core\Dapper\SqlSugar\FreeSql
EF Core
搭建EF Core开发环境
- 建实体类
- 引入Nuget包:Microsoft.EntityFrameworkCore.SqlServer
- 建配置类,配置类实现IEntityTypeConfiguration
接口,T是实体类
如果没有配置类TestDbContext则会生成一个默认的表名,表名为属性名。
using EFCoreRelation.Models;
using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace EFCoreRelation.DbContexts
{
public class AlbertDbContext:DbContext
{
public DbSet<Article> Articles;
public DbSet<Comment> Comments;
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder.UseSqlServer(connectionString: "Server=.;Database=albertefcore;" +
"Trusted_Connection=True;MultipleActiveResultSets=true");
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
base.OnModelCreating(modelBuilder);
modelBuilder.ApplyConfigurationsFromAssembly(this.GetType().Assembly);
}
}
}
Migration数据库迁移概念
使用Migration工具将类生成Table
- Nuget安装Microsoft.EntityFrameworkCore.Tools
- 执行Add-Migration InitialCreate(这是注释) 相当于每次提交,可进行回滚至某个操作
- update-datebase
Migration迁移警告
An operation was scaffolded that may result in the loss of data. Please review the migration for accuracy.是因为有些column设置了长度限制,可能会造成数据截断问题。
深入研究Migrations
Update-Database “Add Cat by attribute”回滚版本名称加引号
如果要删除迁移版本,将项目工程中的版本.cs删除,将SnapShot.cs中生成Entity的删除,数据库中对应的历史版本记录也删掉。
Remove-migration
Remove-migration 每次向上删除一个版本
Update-Database只适合更新开发服务器,不适合生产环境(开发人员不可以直接连接生产数据库)
Script-Migration生成Sql执行脚本
Add-Migration -OutputDir来指定不同的数据库生成脚本
修改表结构
使用EFCore
插入数据
查询数据—复习Linq
修改、删除数据
源代码演示
using Microsoft.EntityFrameworkCore;
using System;
using System.Linq;
using System.Threading.Tasks;
namespace _210917_Demon01_EFCoreAlbert
{
internal class Program
{
/// <summary>
/// <see cref="InitDataBase(DbContext)"/>
/// </summary>
/// <param name="args"></param>
/// <returns></returns>
static async Task Main(string[] args)
{
//插入数据 ctx=逻辑上的数据库
using (var ctx = new MyDbContext())
{
var booksTable = ctx.Books;
foreach (var item in booksTable)
{
ctx.Remove(item);
}
await ctx.SaveChangesAsync();
//初始化数据表
await InitDataBase(ctx);
//查询
var books = ctx.Books.Where(e => e.Price > 80);
foreach (var item in books)
{
Console.WriteLine(item.Title);
}
//查询是否存在一本叫Simple algorithm的书籍,如果存在则打印出作者名字
var book = ctx.Books.Single(e => e.Title == "Simple algorithm");
Console.WriteLine(book.AuthorName);
books = ctx.Books.OrderBy(e => e.Price);
foreach (var item in books)
{
Console.WriteLine(item.Title);
}
//通过分组来取每一个作者的书数量和最大价格
var groups = ctx.Books.GroupBy(e => e.AuthorName).Select(g => new
{
Name = g.Key,
BooksCount = g.Count(),
MaxPrice = g.Max(e => e.Price)
});
foreach (var item in groups)
{
Console.WriteLine($"Name:{item.Name}==" +
$"BooksCount:{item.BooksCount}==" +
$"MaxPrice:{item.MaxPrice}.");
}
//修改数据,albert作者的书籍的价格调高
var albertBooks = ctx.Books.Where(e => e.AuthorName == "AlbertZhao");
foreach (var item in albertBooks)
{
item.Price = 198;
}
//删除书籍Top of the ware
var cBook = ctx.Books.Single(e => e.Title == "Top of the ware");
ctx.Remove(cBook);
await ctx.SaveChangesAsync();
}
}
/// <summary>
///
/// </summary>
/// <remarks>Init DataBase</remarks>
/// <param name="ctx"></param>
/// <returns></returns>
static async Task InitDataBase(DbContext ctx)
{
Book b1 = new Book()
{
AuthorName = "AlbertZhao",
Title = "Simple algorithm",
Price = 99,
PubTime = new DateTime(2022, 12, 1)
};
Book b2 = new Book()
{
AuthorName = "ZackYang",
Title = "Zero-Based fun learning C",
Price = 59.8,
PubTime = new DateTime(2019, 3, 1)
};
Book b3 = new Book()
{
AuthorName = "WuJun",
Title = "The beauty of math",
Price = 99,
PubTime = new DateTime(2018, 1, 1)
};
Book b4 = new Book()
{
AuthorName = "WuJun",
Title = "Top of the ware",
Price = 198,
PubTime = new DateTime(2021, 1, 1)
};
Book b5 = new Book()
{
AuthorName = "Liangtongming",
Title = "In-depth upderstanding of asp.net core",
Price = 169,
PubTime = new DateTime(2021, 1, 1)
};
//将对象数据添加到内存逻辑的数据表中
await ctx.AddAsync(b1);
await ctx.AddAsync(b2);
await ctx.AddAsync(b3);
await ctx.AddAsync(b4);
await ctx.AddAsync(b5);
//将内存中的数据同步到数据库里
await ctx.SaveChangesAsync();
}
}
}
批量删除、修改(Zack.EFCore.Batch.MSSQL开源包)
引用杨中科老师的包-Zack.EFCore.Batch(使用文档:https://github.com/yangzhongke/Zack.EFCore.Batch/blob/main/README_CN.md)
或者使用Z.EntityFramework.Extensions
MyDbContext :DbContext
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
base.OnConfiguring(optionsBuilder);
string connStr = "Server = .; Database = AlbertBook; Trusted_Connection = True;MultipleActiveResultSets=true";
optionsBuilder.UseSqlServer(connStr);
optionsBuilder.UseBatchEF_MSSQL();
}
Main()
//使用杨中科老师的Nuget包:Zack.EFCore.Batch
await ctx.DeleteRangeAsync<Book>(e => e.Price > 80 && e.AuthorName == "WuJun");
await ctx.BatchUpdate<Book>()
.Set(b => b.Price, b => b.Price + 3)
.Set(b => b.Title, b => "HelloWorld")
.Set(b => b.AuthorName, b => b.Title.Substring(3, 2) + b.AuthorName.ToUpper())
.Set(b => b.PubTime, b => DateTime.Now)
.Where(b => b.Id > 1 || b.AuthorName.StartsWith("Albert"))
.ExecuteAsync();
//批量插入数据,一个list直接搞定
List<Book> books = new List<Book>();
for (int i = 0; i < 100; i++)
{
books.Add(new Book { AuthorName = "abc" + i, Price = new Random().NextDouble(), PubTime = DateTime.Now, Title = Guid.NewGuid().ToString() });
}
using (TestDbContext ctx = new TestDbContext())
{
ctx.BulkInsert(books);
}
Sql Server小工具—Sql Server Profiler查询所有接收的sql
EFCore实体的配置
特性配置和FluentAPI两种方式查询网址:https://docs.microsoft.com/en-us/ef/core/
主键不是小事
自增主键是由数据库赋值,Guid主键是由EFCore引擎赋值。
雪花算法,分布式唯一ID解决方案 https://cloud.tencent.com/developer/article/1772047
使用Guid作为主键的时候,不要把主键设置为聚集索引,否则插入效率特别低,要进行顺序重排,效率极其低下。
SqlServer中不要把Guid主键设置为聚集索引,在mysql中插入频繁的表不要用Guid做主键。
MySql和MongoDb少用Guid做主键。
雪花算法
Hi/Lo算法 本地向服务器要一组Hi值,例如600-700,服务器一次性给你一组,等本地用完再向服务器要的时候,可能下次给到的就是800-900了。
EFCore反向工程-从数据库生成实体类—不推荐使用这种方法,直接使用第三方工具
三种建模方式:DBFirst数据库优先(数据库先建好) ModelFirst模型优先(图形化先建好) CodeFirst代码优先
已经存在表了,想利用反向工程将存在的表反向生成。
新建项目,将引用的包拷贝到新项目中,设置启动项目为当前项目
<ItemGroup>
<PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="5.0.11" />
<PackageReference Include="Microsoft.EntityFrameworkCore.Tools" Version="5.0.11">
<PrivateAssets>all</PrivateAssets>
<IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
</PackageReference>
</ItemGroup>
执行以下命令,这个命令Scaffold-DbContext 数据库连接字符串 数据库类型
Scaffold-DbContext "Server = .; Database = AlbertBook; Trusted_Connection = True;MultipleActiveResultSets=true" Microsoft.EntityFrameworkCore.SqlServer
如果新建了一个表,需要强制覆盖,在最后加上-force
Scaffold-DbContext "Server = .; Database = AlbertBook; Trusted_Connection = True;MultipleActiveResultSets=true" Microsoft.EntityFrameworkCore.SqlServer -Force
EFCore底层如何操作数据库 EFCore和ADO.NET合作关系
YZK:框架是帮助程序员简化工作的,不是把程序员变成傻瓜的
应用程序—>ADO.NET Core(SQL)—>数据库 EFCore底层依旧是ADO.NET Core
EFCore有那些做不到的事
三种方法通过代码查看SQL语句
标准日志
//在DbContext继承类中输入以下代码
private static readonly ILoggerFactory loggerFactory = LoggerFactory.Create(builder=>builder.AddConsole());
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
if (!optionsBuilder.IsConfigured)
{
#warning To protect potentially sensitive information in your connection string, you should move it out of source code. You can avoid scaffolding the connection string by using the Name= syntax to read it from configuration - see https://go.microsoft.com/fwlink/?linkid=2131148. For more guidance on storing connection strings, see http://go.microsoft.com/fwlink/?LinkId=723263.
optionsBuilder.UseSqlServer("Server = .; Database = AlbertBook; Trusted_Connection = True;MultipleActiveResultSets=true");
optionsBuilder.UseLoggerFactory(loggerFactory);
}
}
//在Program.cs中输入以下代码
static void Main(string[] args)
{
using (var ctx = new AlbertBookContext())
{
var books = ctx.TBooks.OrderBy(e => e.Price);
foreach (var item in books)
{
System.Console.WriteLine(item.Title);
}
}
}
简单日志
optionsBuilder.LogTo(msg =>
{
//msg是ef输出的消息
Console.WriteLine(msg);
});
ToQueryString
We need using Microsoft.EntityFrameworkCore
//We need execute var ctx = new AlbertBookContext:DbContext
//Console.WriteLine(books.ToQueryString())
static void Main(string[] args)
{
using (var ctx = new AlbertBookContext())
{
var books = ctx.TBooks.OrderBy(e => e.Price);
foreach (var item in books)
{
System.Console.WriteLine(item.Title);
}
string sqlServerOrderByPrice = books.ToQueryString();
System.Console.WriteLine(sqlServerOrderByPrice);
}
}
三种方式总结
EFCore-同样的LINQ被翻译为不同的SQL语句
SQLServer
EF Core对于SqlServer的翻译结果:
C#:
var persons = ctx.TPeople.Where(p => p.BirthDay.Value.Year == 1998).Take(3);
SQL Server:
SELECT TOP(@__p_0) [t].[Id], [t].[Age], [t].[BirthDay], [t].[BirthPlace], [t].[Name], [t].[Salary]
FROM [T_Person] AS [t]
WHERE DATEPART(year, [t].[BirthDay]) = 1998
MySQL
- Add Nuget.org
- 增加第二个Context继承类,单独和SQLServer的分开,例如SQLServer_AlbertContext.cs MySQL_AlbertContext.cs
- Add-Migration InitialCreate -Context MySQL_AlbertContext -OutputDir Migrations\MySqlMigrations 这句话注释为InitialCreate 执行的Context类为MySQL继承的Context类
输出到Migrations\MySqlMigrations文件夹下面 相关的Link:https://docs.microsoft.com/zh-cn/ef/core/managing-schemas/migrations/providers?tabs=vs - 执行数据库更新操作,Update-Database -context mysql_albertbookcontext
- 刷新Navicat执行完毕。