1.使用工具生成数据库对应的C# 类

添加包 EF Core design package   NuGet Gallery | Home

使用用于 EF Core 迁移和现有数据库中的反向工程（基架）的工具需要安装相应的工具包：

• 可在 Visual Studio 包管理器控制台中使用的 PowerShell 工具的 Microsoft.EntityFrameworkCore.Tools
• 跨平台命令行工具的 dotnet-ef 和 Microsoft.EntityFrameworkCore.Design

请参阅 Entity Framework Core 工具参考，详细了解如何使用 EF Core 工具，包括如何在项目中或在全局范围内正确安装 dotnet-ef 工具。

2.生成的对应类库

2.1 数据库上下文类

using System;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata;#nullable disablenamespace Packt.Shared.AutoGen
{//DbContext 实例表示与数据库的会话，可用于查询和保存实体的实例。 DbContext 是工作单元和存储库模式的组合。public partial class Northwind : DbContext{public Northwind(){}public Northwind(DbContextOptions<Northwind> options): base(options){}public virtual DbSet<Category> Categories { get; set; } //数据集 类别public virtual DbSet<Product> Products { get; set; }//数据集： 产品/*提供用于配置 Microsoft.EntityFrameworkCore.DbContextOptions 的简单 API 图面。 数据库（和其他扩展）通常在此对象上定义扩展方法，允许您配置要用于上下文的数据库连接（和其他选项）。您可以使用 Microsoft.EntityFrameworkCore.DbContextOptionsBuilder 通过覆盖 Microsoft.EntityFrameworkCore.DbContext.OnConfiguring(Microsoft.EntityFrameworkCore.DbContextOptionsBuilder) 或在外部创建 Microsoft.EntityFrameworkCore.DbContextOptions 并将其传递给上下文构造函数来配置上下文。*/protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder){if (!optionsBuilder.IsConfigured){//为了保护连接字符串中的潜在敏感信息，您应该将其移出源代码。 您可以使用 Name= 语法从配置中读取连接字符串，从而避免构建连接字符串 - 请参阅 https://go.microsoft.com/fwlink/?linkid=2131148。 有关存储连接字符串的更多指导，请参阅 http://go.microsoft.com/fwlink/?LinkId=723263。
#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.UseSqlite("Filename=Northwind.db");}}//创建模型+protected override void OnModelCreating(ModelBuilder modelBuilder){modelBuilder.Entity<Category>(entity =>{entity.Property(e => e.CategoryId).ValueGeneratedNever().HasColumnName("CategoryID");entity.Property(e => e.CategoryName).HasAnnotation("Relational:ColumnType", "nvarchar (15)");entity.Property(e => e.Description).HasAnnotation("Relational:ColumnType", "ntext");entity.Property(e => e.Picture).HasAnnotation("Relational:ColumnType", "image");});modelBuilder.Entity<Product>(entity =>{entity.Property(e => e.ProductId).ValueGeneratedNever().HasColumnName("ProductID");entity.Property(e => e.CategoryId).HasColumnName("CategoryID").HasAnnotation("Relational:ColumnType", "int");entity.Property(e => e.Discontinued).HasDefaultValueSql("0").HasAnnotation("Relational:ColumnType", "bit");entity.Property(e => e.ProductName).HasAnnotation("Relational:ColumnType", "nvarchar (40)");entity.Property(e => e.QuantityPerUnit).HasAnnotation("Relational:ColumnType", "nvarchar (20)");entity.Property(e => e.ReorderLevel).HasDefaultValueSql("0").HasAnnotation("Relational:ColumnType", "smallint");entity.Property(e => e.SupplierId).HasColumnName("SupplierID").HasAnnotation("Relational:ColumnType", "int");entity.Property(e => e.UnitPrice).HasDefaultValueSql("0").HasAnnotation("Relational:ColumnType", "money");entity.Property(e => e.UnitsInStock).HasDefaultValueSql("0").HasAnnotation("Relational:ColumnType", "smallint");entity.Property(e => e.UnitsOnOrder).HasDefaultValueSql("0").HasAnnotation("Relational:ColumnType", "smallint");});OnModelCreatingPartial(modelBuilder);}partial void OnModelCreatingPartial(ModelBuilder modelBuilder);}
}

2.2 类别类：

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using Microsoft.EntityFrameworkCore;#nullable disablenamespace Packt.Shared.AutoGen
{[Index(nameof(CategoryName), Name = "CategoryName")]public partial class Category{public Category(){Products = new HashSet<Product>();}[Key][Column("CategoryID")]public long CategoryId { get; set; }[Required][Column(TypeName = "nvarchar (15)")]public string CategoryName { get; set; }[Column(TypeName = "ntext")]public string Description { get; set; }[Column(TypeName = "image")]public byte[] Picture { get; set; }[InverseProperty(nameof(Product.Category))]public virtual ICollection<Product> Products { get; set; }}
}

2.3 产品类

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using Microsoft.EntityFrameworkCore;#nullable disablenamespace Packt.Shared.AutoGen
{[Index(nameof(CategoryId), Name = "CategoriesProducts")][Index(nameof(CategoryId), Name = "CategoryID")][Index(nameof(ProductName), Name = "ProductName")][Index(nameof(SupplierId), Name = "SupplierID")][Index(nameof(SupplierId), Name = "SuppliersProducts")]public partial class Product{[Key][Column("ProductID")]public long ProductId { get; set; }[Required][Column(TypeName = "nvarchar (40)")]public string ProductName { get; set; }[Column("SupplierID", TypeName = "int")]public long? SupplierId { get; set; }[Column("CategoryID", TypeName = "int")]public long? CategoryId { get; set; }[Column(TypeName = "nvarchar (20)")]public string QuantityPerUnit { get; set; }[Column(TypeName = "money")]public byte[] UnitPrice { get; set; }[Column(TypeName = "smallint")]public long? UnitsInStock { get; set; }[Column(TypeName = "smallint")]public long? UnitsOnOrder { get; set; }[Column(TypeName = "smallint")]public long? ReorderLevel { get; set; }[Required][Column(TypeName = "bit")]public byte[] Discontinued { get; set; }[ForeignKey(nameof(CategoryId))][InverseProperty("Products")]public virtual Category Category { get; set; }}
}

3. 数据库的操作

using static System.Console;
using Packt.Shared;
using Microsoft.EntityFrameworkCore;
using System.Linq;
using System;
using Microsoft.EntityFrameworkCore.Infrastructure;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Logging;
using System.Collections.Generic;
using Microsoft.EntityFrameworkCore.Storage;namespace WorkingWithEFCore
{class Program{   //查询类别static void QueryingCategories(){using (var db = new Northwind()){   //从实现 Microsoft.EntityFrameworkCore.Infrastructure.IInfrastructure`1 的类型公开的 System.IServiceProvider 解析服务。// 此方法通常由数据库提供程序（和其他扩展）使用。 它通常不在应用程序代码中使用。 //Microsoft.EntityFrameworkCore.Infrastructure.IInfrastructure`1 用于隐藏不打算在应用程序代码中使用//但可用于数据库提供程序编写的扩展方法等的属性。var loggerFactory = db.GetService<ILoggerFactory>();loggerFactory.AddProvider(new ConsoleLoggerProvider());//将 Microsoft.Extensions.Logging.ILoggerProvider 添加到日志系统。WriteLine("Categories and how many products they have:");// a query to get all categories and their related products IQueryable<Category> cats;//获取所有类别及其相关产品的查询// = db.Categories; // .Include(c => c.Products);//获取或设置一个值，该值指示是否将在首次访问时加载被跟踪实体的导航属性。// 默认值是true。 但是，延迟加载只会发生在实体的导航属性中，这些实体也已在模型中配置为延迟加载。db.ChangeTracker.LazyLoadingEnabled = false;//默认trueWrite("Enable eager loading? (Y/N): ");//启用预加载bool eagerloading = (ReadKey().Key == ConsoleKey.Y);bool explicitloading = false;WriteLine();if (eagerloading)//预加载  产品数量非零{cats = db.Categories.Include(c => c.Products);//指定要包含在查询结果中的相关实体。 要包含的导航属性从被查询实体的类型 (TEntity) 开始指定。}else{cats = db.Categories;Write("Enable explicit loading? (Y/N): ");//Y  显式加载 产品数量非零explicitloading = (ReadKey().Key == ConsoleKey.Y);WriteLine();}foreach (Category c in cats){if (explicitloading)//显式加载每个类别{Write($"Explicitly load products for {c.CategoryName}? (Y/N): "); //Y   产品数量输出非零ConsoleKeyInfo key = ReadKey();WriteLine();if (key.Key == ConsoleKey.Y){var products = db.Entry(c).Collection(c2 => c2.Products);//获取类别c的 产品型号集合if (!products.IsLoaded) products.Load();}}WriteLine($"{c.CategoryName} has {c.Products.Count} products.");}}}//过滤器查询     各类// 对应的各种产品中，库存量大于stock的产品有哪些？static void FilteredIncludes(){using (var db = new Northwind()){Write("Enter a minimum for units in stock: ");string unitsInStock = ReadLine();int stock = int.Parse(unitsInStock);//库存单位//查找 库存量大于stock的产品类别IQueryable<Category> cats = db.Categories.Include(c => c.Products.Where(p => p.Stock >= stock));//要求该类的产品种类中数量大于stockWriteLine($"ToQueryString: {cats.ToQueryString()}");foreach (Category c in cats){   //某类 库存大于stock的产品有哪些WriteLine($"{c.CategoryName} has {c.Products.Count} products with a minimum of {stock} units in stock.");foreach (Product p in c.Products){WriteLine($"  {p.ProductName} has {p.Stock} units in stock.");//输出产品名 产品库存}}}}//查询产品  static void QueryingProducts(){using (var db = new Northwind()){var loggerFactory = db.GetService<ILoggerFactory>();loggerFactory.AddProvider(new ConsoleLoggerProvider());WriteLine("Products that cost more than a price, highest at top.");string input;decimal price; //价格do{Write("Enter a product price: ");input = ReadLine();} while (!decimal.TryParse(input, out price));//输入产品价格IQueryable<Product> prods = db.Products.Where(product => product.Cost > price) //产品单价大于price.OrderByDescending(product => product.Cost); //按照  产品单价  降序排列/*// alternative "fix"IOrderedEnumerable<Product> prods = db.Products.AsEnumerable() // force client-side execution.Where(product => product.Cost > price).OrderByDescending(product => product.Cost);*/foreach (Product item in prods){//38: C?te de Blaye costs $263.50 and has 17 in stock.WriteLine("{0}: {1} costs {2:$#,##0.00} and has {3} in stock.",item.ProductID, item.ProductName, item.Cost, item.Stock);}}}//like查询static void QueryingWithLike(){using (var db = new Northwind()){var loggerFactory = db.GetService<ILoggerFactory>();loggerFactory.AddProvider(new ConsoleLoggerProvider());Write("Enter part of a product name: ");string input = ReadLine();IQueryable<Product> prods = db.Products.Where(p => EF.Functions.Like(p.ProductName, $"%{input}%"));//包含 {input} 的 产品名foreach (Product item in prods){WriteLine("{0} has {1} units in stock. Discontinued? {2}",item.ProductName, item.Stock, item.Discontinued);}}}//添加产品static bool AddProduct(int categoryID, string productName, decimal? price){using (var db = new Northwind()){var newProduct = new Product{CategoryID = categoryID,//类别id    产品id自增加ProductName = productName,Cost = price};// mark product as added in change trackingdb.Products.Add(newProduct);// save tracked changes to database int affected = db.SaveChanges();//将跟踪的更改保存到数据库return (affected == 1);}}//列出产品static void ListProducts(){using (var db = new Northwind()){WriteLine("{0,-3} {1,-35} {2,8} {3,5} {4}","ID", "Product Name", "Cost", "Stock", "Disc.");foreach (var item in db.Products.OrderByDescending(p => p.Cost)){WriteLine("{0:000} {1,-35} {2,8:$#,##0.00} {3,5} {4}",item.ProductID, item.ProductName, item.Cost,item.Stock, item.Discontinued);}}}//增加产品价格static bool IncreaseProductPrice(string name, decimal amount){using (var db = new Northwind()){// get first product whose name starts with nameProduct updateProduct = db.Products.First(p => p.ProductName.StartsWith(name));updateProduct.Cost += amount;int affected = db.SaveChanges();return (affected == 1);}}//删除产品static int DeleteProducts(string name){using (var db = new Northwind()){using (IDbContextTransaction t = db.Database.BeginTransaction())//开始一个新的事务。{WriteLine("Transaction isolation level: {0}",t.GetDbTransaction().IsolationLevel);var products = db.Products.Where(p => p.ProductName.StartsWith(name));db.Products.RemoveRange(products);int affected = db.SaveChanges();t.Commit();return affected;}}}static void Main(string[] args){// QueryingCategories();// FilteredIncludes(); //QueryingProducts();//QueryingWithLike();//if (AddProduct(6, "Bob's Burgers", 500M))//{//    WriteLine("Add product successful.");//}//if (IncreaseProductPrice("Bob", 20M))//{//    WriteLine("Update product price successful.");//}int deleted = DeleteProducts("Bob");WriteLine($"{deleted} product(s) were deleted.");//  ListProducts();ReadLine();}}
}

4. Loggin EF Core

using Microsoft.Extensions.Logging;
using System;
using static System.Console;namespace Packt.Shared
{public class ConsoleLoggerProvider : ILoggerProvider//创建日志提供器{public ILogger CreateLogger(string categoryName){return new ConsoleLogger();//控制台记录器}// if your logger uses unmanaged resources,// you can release the memory here 如果您的记录器使用非托管资源，您可以在此处释放内存public void Dispose() { }}public class ConsoleLogger : ILogger //日志记录器{// if your logger uses unmanaged resources, you can// return the class that implements IDisposable here //如果您的记录器使用非托管资源，您可以在此处返回实现 IDisposable 的类public IDisposable BeginScope<TState>(TState state){return null;}//为避免过度记录，您可以在日志级别上进行过滤public bool IsEnabled(LogLevel logLevel){// to avoid overlogging, you can filter// on the log level 为避免过度记录，您可以在日志级别上进行过滤switch (logLevel){case LogLevel.Trace:case LogLevel.Information:case LogLevel.None:return false;case LogLevel.Debug:case LogLevel.Warning:case LogLevel.Error:case LogLevel.Critical:default:return true;};}//记录日志public void Log<TState>(LogLevel logLevel,EventId eventId, TState state, Exception exception,Func<TState, Exception, string> formatter){if (eventId.Id == 20100){// 记录级别和事件标识符 log the level and event identifierWrite($"Level: {logLevel}, Event ID: {eventId.Id}");//仅在存在时输出状态或异常 only output the state or exception if it existsif (state != null){Write($", State: {state}");}if (exception != null){Write($", Exception: {exception.Message}");}WriteLine();}}}
}

5.  设置sqlite for windows

SQLite Download Page  下载 

解压并设置环境变量（exe 所在目录添加到path）