【C# .NET 】使用 Entity Framework Core 操作sqlite數(shù)據(jù)庫

這篇具有很好參考價值的文章主要介紹了【C# .NET 】使用 Entity Framework Core 操作sqlite數(shù)據(jù)庫。希望對大家有所幫助。如果存在錯誤或未考慮完全的地方，請大家不吝賜教，您也可以點擊"舉報違法"按鈕提交疑問。

1.使用工具生成數(shù)據(jù)庫對應的C# 類

添加包 EF Core design package? ?NuGet Gallery | Home

使用用于?EF Core 遷移和現(xiàn)有數(shù)據(jù)庫中的反向工程（基架）的工具需要安裝相應的工具包：

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

請參閱?Entity Framework Core 工具參考，詳細了解如何使用 EF Core 工具，包括如何在項目中或在全局范圍內正確安裝?dotnet-ef?工具。

2.生成的對應類庫

2.1 數(shù)據(jù)庫上下文類

using System;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata;

#nullable disable

namespace Packt.Shared.AutoGen
{//DbContext 實例表示與數(shù)據(jù)庫的會話，可用于查詢和保存實體的實例。 DbContext 是工作單元和存儲庫模式的組合。
    public partial class Northwind : DbContext
    {
        public Northwind()
        {
        }

        public Northwind(DbContextOptions<Northwind> options)
            : base(options)
        {
        }

        public virtual DbSet<Category> Categories { get; set; } //數(shù)據(jù)集 類別
        public virtual DbSet<Product> Products { get; set; }//數(shù)據(jù)集： 產(chǎn)品
        /*
         提供用于配置 Microsoft.EntityFrameworkCore.DbContextOptions 的簡單 API 圖面。 
        數(shù)據(jù)庫（和其他擴展）通常在此對象上定義擴展方法，允許您配置要用于上下文的數(shù)據(jù)庫連接（和其他選項）。
        您可以使用 Microsoft.EntityFrameworkCore.DbContextOptionsBuilder 通過覆蓋 
        Microsoft.EntityFrameworkCore.DbContext.OnConfiguring(Microsoft.EntityFrameworkCore.DbContextOptionsBuilder) 
        或在外部創(chuàng)建 Microsoft.EntityFrameworkCore.DbContextOptions 并將其傳遞給上下文構造函數(shù)來配置上下文。
         */
        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");
            }
        }
        //創(chuàng)建模型+

        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 產(chǎn)品類

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. 數(shù)據(jù)庫的操作

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())
            {   //從實現(xiàn) Microsoft.EntityFrameworkCore.Infrastructure.IInfrastructure`1 的類型公開的 System.IServiceProvider 解析服務。
                // 此方法通常由數(shù)據(jù)庫提供程序（和其他擴展）使用。 它通常不在應用程序代碼中使用。 
                //Microsoft.EntityFrameworkCore.Infrastructure.IInfrastructure`1 用于隱藏不打算在應用程序代碼中使用
                //但可用于數(shù)據(jù)庫提供程序編寫的擴展方法等的屬性。
                var loggerFactory = db.GetService<ILoggerFactory>();
                loggerFactory.AddProvider(new ConsoleLoggerProvider());//將 Microsoft.Extensions.Logging.ILoggerProvider 添加到日志系統(tǒng)。

                WriteLine("Categories and how many products they have:");

                // a query to get all categories and their related products 
                IQueryable<Category> cats;//獲取所有類別及其相關產(chǎn)品的查詢
                // = db.Categories; 
                // .Include(c => c.Products);

                //獲取或設置一個值，該值指示是否將在首次訪問時加載被跟蹤實體的導航屬性。
                // 默認值是true。 但是，延遲加載只會發(fā)生在實體的導航屬性中，這些實體也已在模型中配置為延遲加載。
                db.ChangeTracker.LazyLoadingEnabled = false;//默認true

                Write("Enable eager loading? (Y/N): ");//啟用預加載
                bool eagerloading = (ReadKey().Key == ConsoleKey.Y);
                bool explicitloading = false;
                WriteLine();
                if (eagerloading)//預加載  產(chǎn)品數(shù)量非零
                {
                    cats = db.Categories.Include(c => c.Products);//指定要包含在查詢結果中的相關實體。 要包含的導航屬性從被查詢實體的類型 (TEntity) 開始指定。
                }
                else
                {
                    cats = db.Categories;
                    Write("Enable explicit loading? (Y/N): ");//Y  顯式加載 產(chǎn)品數(shù)量非零
                    explicitloading = (ReadKey().Key == ConsoleKey.Y);
                    WriteLine();
                }

                foreach (Category c in cats)
                {
                    if (explicitloading)//顯式加載每個類別
                    {
                        Write($"Explicitly load products for {c.CategoryName}? (Y/N): "); //Y   產(chǎn)品數(shù)量輸出非零
                        ConsoleKeyInfo key = ReadKey();
                        WriteLine();

                        if (key.Key == ConsoleKey.Y)
                        {
                            var products = db.Entry(c).Collection(c2 => c2.Products);//獲取類別c的 產(chǎn)品型號集合
                            if (!products.IsLoaded) products.Load();
                        }
                    }
                    WriteLine($"{c.CategoryName} has {c.Products.Count} products.");
                }
            }
        }
        //過濾器查詢     各類
        // 對應的各種產(chǎn)品中，庫存量大于stock的產(chǎn)品有哪些？
        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的產(chǎn)品類別
                IQueryable<Category> cats = db.Categories
                  .Include(c => c.Products.Where(p => p.Stock >= stock));//要求該類的產(chǎn)品種類中數(shù)量大于stock

                WriteLine($"ToQueryString: {cats.ToQueryString()}");

                foreach (Category c in cats)
                {   //某類 庫存大于stock的產(chǎn)品有哪些
                    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.");//輸出產(chǎn)品名 產(chǎn)品庫存
                    }
                }
            }
        }
        //查詢產(chǎn)品  
        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));//輸入產(chǎn)品價格

                IQueryable<Product> prods = db.Products
                  .Where(product => product.Cost > price) //產(chǎn)品單價大于price
                  .OrderByDescending(product => product.Cost); //按照  產(chǎn)品單價  降序排列

                /*
                // 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} 的 產(chǎn)品名

                foreach (Product item in prods)
                {
                    WriteLine("{0} has {1} units in stock. Discontinued? {2}",
                      item.ProductName, item.Stock, item.Discontinued);
                }
            }
        }
        //添加產(chǎn)品
        static bool AddProduct(int categoryID, string productName, decimal? price)
        {
            using (var db = new Northwind())
            {
                var newProduct = new Product
                {
                    CategoryID = categoryID,//類別id    產(chǎn)品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();//將跟蹤的更改保存到數(shù)據(jù)庫
                return (affected == 1);
            }
        }
        //列出產(chǎn)品
        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);
                }
            }
        }
        //增加產(chǎn)品價格
        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);
            }
        }
        //刪除產(chǎn)品
        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//創(chuàng)建日志提供器
    {
        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 
        //如果您的記錄器使用非托管資源，您可以在此處返回實現(xiàn) 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}");

                //僅在存在時輸出狀態(tài)或異常 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? 下載?

sqlite-tools-win32-x86-3410200.zip
(1.91 MiB)

A bundle of command-line tools for managing SQLite database files, including the?command-line shell?program, the?sqldiff.exe?program, and the?sqlite3_analyzer.exe?program.
(SHA3-256: 0ceebb7f8378707d6d6b0737ecdf2ba02253a3b44b1009400f86273719d98f1f)

解壓并設置環(huán)境變量（exe 所在目錄添加到path）文章來源地址http://www.zghlxwxcb.cn/news/detail-634440.html

到了這里，關于【C# .NET 】使用 Entity Framework Core 操作sqlite數(shù)據(jù)庫的文章就介紹完了。如果您還想了解更多內容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關文章，希望大家以后多多支持TOY模板網(wǎng)！