.NET005-EF Core-1(.NET005-EF Core-1)

.NET005-EF Core-1

文档概述

关于EF Core的专题部分目前暂定四篇文章和一个项目(非新手项目),第一篇主要介绍ORM以及EF Core的简单使用和一些工具的使用以及EF Core的增删改查等等;第二篇介绍EF Core一对一、一对多、多对多关系配置;第三篇文章EF Core的优化、乐观并发控制、悲观并发控制等;第四篇文章表达式目录树动态构造。项目使用的数据库主要为SqlServer,但不仅限于此,也会用MySQL、PostgreSql等(主要为演示在不同数据库下EF Core如果转换为AST的差异性)。

本文主要介绍如下几个技术及实现:

  • 什么是ORM?
  • 如何搭建EF Core开发环境
  • 数据库迁移工具Migration如何使用及注意事项
  • 使用EF Core操作数据库(增、删、改、查、批量操作)
  • EF Core反向工程
  • 如何通过代码形式查看C#–>SQL语句以及三种主流数据库的实际情景

什么是ORM?

ORM(Object-Relational Mapping),中文翻译为对象关系映射,是为了解决面向对象和关系数据库匹配的技术,可简单理解为对象和数据库自动关联,修改对象的一些属性等等可同步到数据库中(映射元数据)。
在.NET中常见的ORM框架有如下:

  • EF Core 微软官方,要花较大精力去研究,否则容易出现效率低下等一些列问题 EF Core官方文档
  • Dapper
  • SqlSugar 轻量级框架,个人开发者的优秀框架,适合快速上手 sqlssugar官方文档
  • FreeSql

如何搭建EF Core开发环境

https://cdn.nlark.com/yuque/0/2021/png/957395/1632414095683-d969bd51-0c31-4111-aafb-0cb2850a9367.png

  • 创建实体类,例如:
 public partial class Person
    {
        public long Id { get; set; }
        public string Name { get; set; }
        public int Age { get; set; }
        public string BirthPlace { get; set; }
        public double? Salary { get; set; }
        //?表示可空类型
        public DateTime? BirthDay { get; set; }
    }
 public partial class Dog
    {
        public long Id { get; set; }
        public string Name { get; set; }
    }
  • 创建配置类,继承IEntityTypeConfiguration,可在Configure方法中对属性(数据库中的字段)进行配置,例如下方将表名设置为T_Person
public class PersonConfig : IEntityTypeConfiguration<Person>
    {
        public void Configure(EntityTypeBuilder<Person> builder)
        {
            builder.ToTable("T_Person");
        }
    }
  • 创建继承自DbContext的自己的Context类,例如MyDbContext:DbContext,OnConfiguring()方法,可以配置连接数据库的类型及连接数据库的名称,也可以添加日志的输出(标准日志和简单日志)。OnModelCreating()方法则负责检索是加载当前程序集下面的所有IEntityTypeConfiguration还是其他程序级下面的实体类配置,也可以直接在这边进行实体类属性的约定配置。
 public class MyDbContext:DbContext
    {
        public DbSet<Book> Books { get; set; }
        public DbSet<Person> Persons {  get; set; }
        public DbSet<Dog> Dogs { get;set;  }
        public DbSet<Cat> Cats {  get; set; }
        public DbSet<Rabbit> Rabbits { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            base.OnConfiguring(optionsBuilder);
            string connStr = "Server = .; Database = AlbertBook; Trusted_Connection = True;MultipleActiveResultSets=true";
            //使用SqlServer连接数据库
            optionsBuilder.UseSqlServer(connStr);
            //支持批量删除和操作数据库
            optionsBuilder.UseBatchEF_MSSQL();
        }
        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);
            //从当前程序集加载所有的IEntityTypeConfiguration<T>
            modelBuilder.ApplyConfigurationsFromAssembly(this.GetType().Assembly);
        }
    }

 protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            if (!optionsBuilder.IsConfigured)
            {
                //If you wanna convert the C# code to SQLServer, you should use the following package.
                //EFProvider-SQLServer PackageReference-Microsoft.EntityFrameworkCore.SqlServer
                //optionsBuilder.UseSqlServer("Server = .; Database = AlbertTemp; Trusted_Connection = True;MultipleActiveResultSets=true");

                //If you wanna convert the C# code to MySQL, you should use the following package.
                //EFProvider-MySql PackageReference-Pomelo.EntityFrameworkCore.MySql
                // Replace with your connection string.
                var connectionString = "server=localhost;user=root;password=eason12138.;database=AlbertTBooks";
                var serverVersion = new MySqlServerVersion(new Version(8, 0, 27));

                optionsBuilder.UseMySql(connectionString, serverVersion);


                //标准日志 Package-Microsoft.Extensions.Logging
                //optionsBuilder.UseLoggerFactory(loggerFactory);
                optionsBuilder.LogTo(msg =>
                {
                    if(!msg.Contains("Executing DbCommand")){ return; }
                    //msg是ef输出的消息
                    Console.WriteLine(msg);
                });
            }
        }

 protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.HasAnnotation("Relational:Collation", "Chinese_PRC_CI_AS");

            modelBuilder.Entity<TBook>(entity =>
            {
                entity.ToTable("T_Books");

                entity.Property(e => e.AuthorName)
                    .IsRequired()
                    .HasMaxLength(20)
                    .HasDefaultValueSql("(N'')");

                entity.Property(e => e.Title)
                    .IsRequired()
                    .HasMaxLength(100);
            });

            modelBuilder.Entity<TCat>(entity =>
            {
                entity.ToTable("T_Cats");

                entity.Property(e => e.Name)
                    .IsRequired()
                    .HasMaxLength(20);
            });

            modelBuilder.Entity<TPerson>(entity =>
            {
                entity.ToTable("T_Person");
            });

            modelBuilder.Entity<TRabbit>(entity =>
            {
                entity.ToTable("T_Rabbit");

                entity.Property(e => e.Id).ValueGeneratedNever();

                entity.Property(e => e.Name)
                    .IsRequired()
                    .HasMaxLength(20);
            });

            OnModelCreatingPartial(modelBuilder);
        }

数据库迁移工具Migration如何使用及注意事项

Migration数据库迁移,面向对象的ORM开发中,数据库不是程序员手动创建的,而是由Migration工具生成的。关系数据库库只是盛放模型数据的一个媒介而已,理想状态下,程序员无需关心数据库的操作。而根据对象定义的变化,自动更新数据库中的表以及表结构的操作,叫做Migration(迁移),迁移可以分为项目进化、项目回滚等。EF Core6.0 Migration官方文档

  • 安装NugetPackage(可以通过很多方式,具体请查阅nuget.org,笔者喜欢采用编辑*.proj):Microsoft.EntityFrameworkCore.Tools
  • 当实体类和实体配置类,以及继承的DbContext的MyDbContext都Ok后直接在Package Manager Console中输入Add-Migration InitialCreate(这是注释,类似于git commit -m “注释”),执行完毕后可自动生成C#代码,默认在Migrations文件夹下,可通过指定OutputDir来指定目录名称。
  • 更新提交到数据库,让数据库执行创表等系列操作。Update-database.
  • 回滚到指定版本号:Update-Database “版本号名称,就是上面的注释” 这边务必加上引号
  • 向上回滚一个版本直接执行 Remove-migration
  • 生成Sql执行脚本(适用于生产环境) Script-Migration D F 生成从D到F的Sql脚本语句

使用EF Core操作数据库(增、删、改、查、批量操作)

  • 增删改查:直接new MyDbContext对象即可,通过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.Bathc.MSSQL使用文档
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()
//使用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);
}

EF Core反向工程

从数据库生成实体类–不推荐使用这种方法,直接使用第三方工具。三种建模方式: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

  • 如果新建了一个表,需要强制覆盖,在最后加上-force
Scaffold-DbContext "Server = .; Database = AlbertBook; Trusted_Connection = True;MultipleActiveResultSets=true" Microsoft.EntityFrameworkCore.SqlServer -Force

如何通过代码形式查看C#–>SQL语句以及三种主流数据库的实际情景

  • 标准日志
    optionBuilder.UseLoggerFactory(ILoggerFactory),下方没有通过依赖注入形式,直接使用了静态对象。
//在DbContext继承类中输入以下代码
private static readonly ILoggerFactory loggerFactory = LoggerFactory.Create(builder=>builder.AddConsole());

 protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            if (!optionsBuilder.IsConfigured)
            {
                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);
                }
            }
        }
  • 简单日志
    可以通过if来判断msg,来获取想要的信息。msg是从连接数据库、创建数据库到执行语句以及最后的关闭连接所有的信息。
optionsBuilder.LogTo(msg =>
                {
                    //msg是ef输出的消息
                    Console.WriteLine(msg);
                });
  • ToQueryString
    使用Package:Microsoft.EntityFrameworkCore,无需执行即可拿到,对象.ToQueryString()方便开发者查看。
//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);
     }
}

附录

  • SQLServer中的Sql Server Profiler可以查询所有数据库接收到的Sql语句,在Tools中
  • 关于数据库主键配置,是选择自增还是GUID还是复合主键请自行查阅研究,目前互联网主流使用雪花算法、Hi\Lo算法、混合主键等
  • 注意有些C#语句是EFCore无法转换的,比如将查询一个name是否contains xxx,如果你写成如下形式,是没办法被翻译过去的:
 var books = ctx.Books.Where(b=>IsOk(b.Title));
 private static bool IsOk(string s){
     return s.Contains("Albert");
 }
 //Notes:这边有个小知识,顺便提一下
 //LINQ中的Where实际就是一个委托,来判断条件是否满足,在满足的适合直接使用yield关键字,不会一直查询,通过迭代器来实现提前返回。
————————

.NET005-EF Core-1

Document overview

At present, four articles and one project (non novice project) are tentatively scheduled for the special topic of EF core. The first mainly introduces the simple use of ORM and EF core, the use of some tools, as well as the addition, deletion, modification and query of EF core, etc; The second part introduces the one-to-one, one to many and many to many relationship configuration of EF core; The third article is about EF core optimization, optimistic concurrency control, pessimistic concurrency control, etc; The fourth article is the dynamic construction of expression directory tree. The database used in the project is mainly sqlserver, but it is not limited to this. MySQL and PostgreSQL will also be used (mainly to demonstrate the difference of EF core converted to ast under different databases).

This paper mainly introduces the following technologies and Implementation:

  • What is ORM?
  • How to build EF core development environment
  • How to use the database migration tool and precautions
  • Use EF core to operate the database (add, delete, modify, query, batch operation)
  • EF Core反向工程
  • How to view c#– > sql statements and actual scenarios of three mainstream databases in code form

What is ORM?

ORM (object relational mapping), translated into Chinese as object relational mapping, is to solve the technology of object-oriented and relational database matching. It can be simply understood as automatic association between object and database, modifying some attributes of object, etc. can be synchronized to the database (mapping metadata).
Yes The common ORM frameworks in. Net are as follows:

  • EF core Microsoft official documents need to spend a lot of energy on research, otherwise it is prone to inefficiency and other problems
  • Dapper
  • Sqlsugar lightweight framework, an excellent framework for individual developers, is suitable for quickly starting sqlssugar official documents
  • FreeSql

How to build EF core development environment

https://cdn.nlark.com/yuque/0/2021/png/957395/1632414095683-d969bd51-0c31-4111-aafb-0cb2850a9367.png

  • Create an entity class, for example:
 public partial class Person
    {
        public long Id { get; set; }
        public string Name { get; set; }
        public int Age { get; set; }
        public string BirthPlace { get; set; }
        public double? Salary { get; set; }
        //?表示可空类型
        public DateTime? BirthDay { get; set; }
    }
 public partial class Dog
    {
        public long Id { get; set; }
        public string Name { get; set; }
    }
  • Create a configuration class that inherits ientitytypeconfiguration. You can configure properties (fields in the database) in the configure method. For example, set the table name to T below_ Person
public class PersonConfig : IEntityTypeConfiguration<Person>
    {
        public void Configure(EntityTypeBuilder<Person> builder)
        {
            builder.ToTable("T_Person");
        }
    }
  • Create your own context class inherited from dbcontext, such as mydbcontext: dbcontext, onconfiguring() method. You can configure the type of database connection and the name of the database connection, or add log output (standard log and simple log). The onmodelcreating () method is responsible for retrieving whether to load all entitytypeconfigurations under the current assembly or entity class configurations under other program levels. You can also directly configure the Convention of entity class properties here.
 public class MyDbContext:DbContext
    {
        public DbSet<Book> Books { get; set; }
        public DbSet<Person> Persons {  get; set; }
        public DbSet<Dog> Dogs { get;set;  }
        public DbSet<Cat> Cats {  get; set; }
        public DbSet<Rabbit> Rabbits { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            base.OnConfiguring(optionsBuilder);
            string connStr = "Server = .; Database = AlbertBook; Trusted_Connection = True;MultipleActiveResultSets=true";
            //使用SqlServer连接数据库
            optionsBuilder.UseSqlServer(connStr);
            //支持批量删除和操作数据库
            optionsBuilder.UseBatchEF_MSSQL();
        }
        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);
            //从当前程序集加载所有的IEntityTypeConfiguration<T>
            modelBuilder.ApplyConfigurationsFromAssembly(this.GetType().Assembly);
        }
    }

 protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            if (!optionsBuilder.IsConfigured)
            {
                //If you wanna convert the C# code to SQLServer, you should use the following package.
                //EFProvider-SQLServer PackageReference-Microsoft.EntityFrameworkCore.SqlServer
                //optionsBuilder.UseSqlServer("Server = .; Database = AlbertTemp; Trusted_Connection = True;MultipleActiveResultSets=true");

                //If you wanna convert the C# code to MySQL, you should use the following package.
                //EFProvider-MySql PackageReference-Pomelo.EntityFrameworkCore.MySql
                // Replace with your connection string.
                var connectionString = "server=localhost;user=root;password=eason12138.;database=AlbertTBooks";
                var serverVersion = new MySqlServerVersion(new Version(8, 0, 27));

                optionsBuilder.UseMySql(connectionString, serverVersion);


                //标准日志 Package-Microsoft.Extensions.Logging
                //optionsBuilder.UseLoggerFactory(loggerFactory);
                optionsBuilder.LogTo(msg =>
                {
                    if(!msg.Contains("Executing DbCommand")){ return; }
                    //msg是ef输出的消息
                    Console.WriteLine(msg);
                });
            }
        }

 protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.HasAnnotation("Relational:Collation", "Chinese_PRC_CI_AS");

            modelBuilder.Entity<TBook>(entity =>
            {
                entity.ToTable("T_Books");

                entity.Property(e => e.AuthorName)
                    .IsRequired()
                    .HasMaxLength(20)
                    .HasDefaultValueSql("(N'')");

                entity.Property(e => e.Title)
                    .IsRequired()
                    .HasMaxLength(100);
            });

            modelBuilder.Entity<TCat>(entity =>
            {
                entity.ToTable("T_Cats");

                entity.Property(e => e.Name)
                    .IsRequired()
                    .HasMaxLength(20);
            });

            modelBuilder.Entity<TPerson>(entity =>
            {
                entity.ToTable("T_Person");
            });

            modelBuilder.Entity<TRabbit>(entity =>
            {
                entity.ToTable("T_Rabbit");

                entity.Property(e => e.Id).ValueGeneratedNever();

                entity.Property(e => e.Name)
                    .IsRequired()
                    .HasMaxLength(20);
            });

            OnModelCreatingPartial(modelBuilder);
        }

How to use the database migration tool and precautions

Migration database migration: in object-oriented ORM development, the database is not created manually by programmers, but generated by migration tools. Relational database is just a medium to hold model data. Ideally, programmers don’t need to care about database operation. The operation of automatically updating the table and table structure in the database according to the change of object definition is called migration. Migration can be divided into project evolution, project rollback, etc. EF Core6. 0 migration official documentation

  • 安装NugetPackage(可以通过很多方式,具体请查阅nuget.org,笔者喜欢采用编辑*.proj):Microsoft.EntityFrameworkCore.Tools
  • 当实体类和实体配置类,以及继承的DbContext的MyDbContext都Ok后直接在Package Manager Console中输入Add-Migration InitialCreate(这是注释,类似于git commit -m “注释”),执行完毕后可自动生成C#代码,默认在Migrations文件夹下,可通过指定OutputDir来指定目录名称。
  • The update is submitted to the database to perform a series of operations such as creating a table. Update-database.
  • Roll back to the specified version number: the “version number name is the above comment” side of update database must be enclosed in quotation marks
  • 向上回滚一个版本直接执行 Remove-migration
  • Generate SQL execution script (for production environment) script migration d f generate SQL script statements from D to f

Use EF core to operate the database (add, delete, modify, query, batch operation)

  • Add, delete, modify and query: you can directly create a new mydbcontext object through LINQ operation
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.Bathc.MSSQL使用文档
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()
//使用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);
}

EF Core反向工程

Generate entity classes from the database — this method is not recommended. Use third-party tools directly. Three modeling methods: dbfirst database first (database built first), modelfirst model first (graphical built first), codefirst code first. The existing table already exists. I want to use reverse engineering to generate the existing table in reverse.

  • Create a new project, copy the referenced package to the new project, and set the startup project as the current project.
<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>
  • Execute the following command: scaffold dbcontext database connection string database type
Scaffold-DbContext "Server = .; Database = AlbertBook; Trusted_Connection = True;MultipleActiveResultSets=true" Microsoft.EntityFrameworkCore.SqlServer
  • If you create a new table, you need to force overwrite, and add – force at the end
  • If you create a new table, you need to force overwrite, and add – force at the end
Scaffold-DbContext "Server = .; Database = AlbertBook; Trusted_Connection = True;MultipleActiveResultSets=true" Microsoft.EntityFrameworkCore.SqlServer -Force

How to view c#– > sql statements and actual scenarios of three mainstream databases in code form

  • 标准日志
    optionBuilder.UseLoggerFactory(ILoggerFactory),下方没有通过依赖注入形式,直接使用了静态对象。
//在DbContext继承类中输入以下代码
private static readonly ILoggerFactory loggerFactory = LoggerFactory.Create(builder=>builder.AddConsole());

 protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            if (!optionsBuilder.IsConfigured)
            {
                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);
                }
            }
        }
  • Simple log
    You can judge MSG by if to get the desired information. MSG is all the information from connecting to the database, creating the database to executing the statement and finally closing the connection.
optionsBuilder.LogTo(msg =>
                {
                    //msg是ef输出的消息
                    Console.WriteLine(msg);
                });
  • ToQueryString
    使用Package:Microsoft.EntityFrameworkCore,无需执行即可拿到,对象.ToQueryString()方便开发者查看。
//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);
     }
}

appendix

  • SQLServer中的Sql Server Profiler可以查询所有数据库接收到的Sql语句,在Tools中
  • For database primary key configuration, whether to select self increment or GUID or composite primary key, please consult and study by yourself. At present, the mainstream Internet uses snowflake algorithm, hi \ Lo algorithm, hybrid primary key, etc
  • Note that some c# statements cannot be converted by efcore. For example, you will query whether a name contains XXX. If you write it in the following form, it cannot be translated:
 var books = ctx.Books.Where(b=>IsOk(b.Title));
 private static bool IsOk(string s){
     return s.Contains("Albert");
 }
 //Notes:这边有个小知识,顺便提一下
 //LINQ中的Where实际就是一个委托,来判断条件是否满足,在满足的适合直接使用yield关键字,不会一直查询,通过迭代器来实现提前返回。