不止于连接：在WPF应用中用Entity Framework Core 7操作MySQL数据库

不止于连接在WPF应用中用Entity Framework Core 7操作MySQL数据库当WPF开发者需要处理数据库时很多人第一反应是直接使用MySqlConnection和MySqlCommand——这就像用螺丝刀组装家具虽然能完成任务但效率远不如电动工具。Entity Framework Core 7EF Core 7就是这样的电动工具它让数据操作从机械的连接/命令模式升级到面向对象的优雅范式。我仍然记得第一次在WPF项目中使用EF Core时的震撼原本需要几十行代码的CRUD操作现在几行就能完成原本需要手动维护的SQL脚本现在通过迁移Migration自动生成。更重要的是代码的可读性和可维护性大幅提升——数据操作不再隐藏在字符串拼接的SQL中而是变成了直观的对象方法和属性。1. 为什么选择EF Core 7而非原生ADO.NET在比较两种数据库操作方式前我们先看一个典型场景向Products表插入一条记录并查询所有单价大于100的产品。原生ADO.NET实现// 插入 using var conn new MySqlConnection(connectionString); var insertCmd new MySqlCommand( INSERT INTO Products (Name, Price) VALUES (name, price), conn); insertCmd.Parameters.AddWithValue(name, Premium Coffee); insertCmd.Parameters.AddWithValue(price, 120); conn.Open(); insertCmd.ExecuteNonQuery(); // 查询 var selectCmd new MySqlCommand( SELECT * FROM Products WHERE Price threshold, conn); selectCmd.Parameters.AddWithValue(threshold, 100); var reader selectCmd.ExecuteReader(); while (reader.Read()) { Console.WriteLine(${reader[Name]} - {reader[Price]}); }EF Core 7实现// 插入 await _dbContext.Products.AddAsync(new Product { Name Premium Coffee, Price 120 }); await _dbContext.SaveChangesAsync(); // 查询 var expensiveProducts await _dbContext.Products .Where(p p.Price 100) .ToListAsync();对比之下EF Core的优势显而易见代码简洁性无需手动编写SQL避免字符串拼接错误强类型检查编译时就能发现属性名拼写错误LINQ集成可以使用熟悉的LINQ语法进行复杂查询异步支持原生支持async/await避免UI线程阻塞变更追踪自动跟踪实体状态变化简化更新操作提示对于简单的查询ADO.NET可能更快但在大多数业务应用中EF Core的生产力优势远大于微小的性能差异。2. 配置WPF项目使用EF Core 72.1 环境准备首先创建新的WPF项目并通过NuGet安装必要包dotnet add package Microsoft.EntityFrameworkCore dotnet add package Microsoft.EntityFrameworkCore.Design dotnet add package Pomelo.EntityFrameworkCore.MySql为什么选择Pomelo而不是官方的MySQL提供程序因为它支持EF Core的所有最新功能活跃的社区维护更好的性能优化2.2 配置DbContext创建继承自DbContext的应用程序数据库上下文public class AppDbContext : DbContext { public DbSetProduct Products { get; set; } public DbSetCustomer Customers { get; set; } protected override void OnConfiguring(DbContextOptionsBuilder options) { options.UseMySql( serverlocalhost;databaseShopDB;userroot;passwordyour_pwd, new MySqlServerVersion(new Version(8, 0, 34)), options options.EnableRetryOnFailure()); } }但在WPF中更好的做法是使用依赖注入// App.xaml.cs public partial class App : Application { public static IServiceProvider ServiceProvider { get; private set; } protected override void OnStartup(StartupEventArgs e) { var services new ServiceCollection(); services.AddDbContextAppDbContext(options options.UseMySql(Configuration.GetConnectionString(Default), ServerVersion.AutoDetect(Configuration.GetConnectionString(Default)))); ServiceProvider services.BuildServiceProvider(); } }3. 数据建模与迁移3.1 定义实体模型采用Code First方式我们先定义业务实体public class Product { public int Id { get; set; } [Required] [MaxLength(100)] public string Name { get; set; } [Column(TypeName decimal(18,2))] public decimal Price { get; set; } public int CategoryId { get; set; } public Category Category { get; set; } } public class Category { public int Id { get; set; } public string Name { get; set; } public ICollectionProduct Products { get; set; } }3.2 数据库迁移通过包管理器控制台执行Add-Migration InitialCreate Update-Database这会生成并执行如下SQLCREATE TABLE Categories ( Id int NOT NULL AUTO_INCREMENT, Name longtext CHARACTER SET utf8mb4 NULL, CONSTRAINT PK_Categories PRIMARY KEY (Id) ); CREATE TABLE Products ( Id int NOT NULL AUTO_INCREMENT, Name varchar(100) CHARACTER SET utf8mb4 NOT NULL, Price decimal(18,2) NOT NULL, CategoryId int NOT NULL, CONSTRAINT PK_Products PRIMARY KEY (Id), CONSTRAINT FK_Products_Categories_CategoryId FOREIGN KEY (CategoryId) REFERENCES Categories (Id) ON DELETE CASCADE );4. 在WPF中实现CRUD操作4.1 依赖注入DbContext在ViewModel中注入DbContextpublic class MainViewModel { private readonly AppDbContext _dbContext; public MainViewModel(AppDbContext dbContext) { _dbContext dbContext; LoadProducts(); } public ObservableCollectionProduct Products { get; } new(); private async void LoadProducts() { var products await _dbContext.Products .Include(p p.Category) .ToListAsync(); Products.Clear(); foreach (var p in products) { Products.Add(p); } } }4.2 增删改查实现添加新产品private async Task AddProductAsync(string name, decimal price) { var product new Product { Name name, Price price }; _dbContext.Products.Add(product); await _dbContext.SaveChangesAsync(); LoadProducts(); // 刷新列表 }批量更新private async Task ApplyDiscountAsync(decimal discountRate) { var products await _dbContext.Products .Where(p p.Price 100) .ToListAsync(); foreach (var p in products) { p.Price * (1 - discountRate); } await _dbContext.SaveChangesAsync(); }复杂查询public async TaskListProduct GetTopExpensiveProductsAsync(int count) { return await _dbContext.Products .OrderByDescending(p p.Price) .Take(count) .Include(p p.Category) .AsNoTracking() // 只读场景提升性能 .ToListAsync(); }5. 性能优化与高级技巧5.1 批量操作优化EF Core 7新增的批量操作API// 批量删除 await _dbContext.Products .Where(p p.Price 10) .ExecuteDeleteAsync(); // 批量更新 await _dbContext.Products .Where(p p.CategoryId 1) .ExecuteUpdateAsync(setters setters.SetProperty(p p.Price, p p.Price * 1.1m));5.2 并发控制使用并发令牌处理并发更新public class Product { // ... [Timestamp] public byte[] RowVersion { get; set; } } try { await _dbContext.SaveChangesAsync(); } catch (DbUpdateConcurrencyException ex) { var entry ex.Entries.Single(); var databaseValues await entry.GetDatabaseValuesAsync(); // 解决策略合并更改或提示用户 entry.OriginalValues.SetValues(databaseValues); await _dbContext.SaveChangesAsync(); }5.3 查询性能分析使用TagWith记录查询目的var results await _dbContext.Products .TagWith(获取首页推荐商品) .Where(p p.IsFeatured) .ToListAsync();在MySQL日志中会看到/* 获取首页推荐商品 */ SELECT p.Id, p.Name, p.Price FROM Products AS p WHERE p.IsFeatured TRUE6. 实战在WPF中实现主从视图结合WPF的数据绑定特性我们可以轻松实现主从视图!-- MainWindow.xaml -- Grid Grid.ColumnDefinitions ColumnDefinition Width*/ ColumnDefinition Width2*/ /Grid.ColumnDefinitions ListBox ItemsSource{Binding Categories} SelectedItem{Binding SelectedCategory} ListBox.ItemTemplate DataTemplate TextBlock Text{Binding Name}/ /DataTemplate /ListBox.ItemTemplate /ListBox DataGrid Grid.Column1 ItemsSource{Binding SelectedCategory.Products} AutoGenerateColumnsFalse DataGrid.Columns DataGridTextColumn Header名称 Binding{Binding Name}/ DataGridTextColumn Header价格 Binding{Binding Price}/ /DataGrid.Columns /DataGrid /GridViewModel只需正确加载包含关联的数据public ObservableCollectionCategory Categories { get; } new(); public async Task LoadDataAsync() { var categories await _dbContext.Categories .Include(c c.Products) .AsNoTracking() .ToListAsync(); Categories.Clear(); foreach (var c in categories) { Categories.Add(c); } }