【C# .NET 】使用 Entity Framework Core 操作sqlite数据库_microsoft.entityframeworkcore.sqlite

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 disable
 
namespace 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 disable
 
namespace 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 disable
 
namespace 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;//默认true
 
                Write("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));//要求该类的产品种类中数量大于stock
 
                WriteLine($"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 tracking
                db.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 name
                Product 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 identifier
                Write($"Level: {logLevel}, Event ID: {eventId.Id}");
 
                //仅在存在时输出状态或异常 only output the state or exception if it exists
                if (state != null)
                {
                    Write($", State: {state}");
                }
                if (exception != null)
                {
                    Write($", Exception: {exception.Message}");
                }
                WriteLine();
            }
        }
    }
}

5.  设置sqlite for windows

SQLite Download Page  下载 

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