一、前言1、本教程主要内容ASP.NET Core MVC 集成 EF Core 介绍&操作步骤ASP.NET Core MVC 使用 EF Core + Linq to Entity 访问MySQL数据库ASP.NET Core MVC 使用 EF Core + 原生SQL访问MySql数据库EF Core + MySQL数据库插入数据后获取自增列的值Entity Framework Core 简称为 EF Core2、本教程环境信息软件/环境说明操作系统Windows 10SDK2.1.401ASP.NET Core2.1.3MySQL8.0.xIDEVisual Studio Code 1.30浏览器Chrome 70VS Code插件版本说明C#1.17.1提供C#智能感知， .NET Core 调试、编译等vscdoe-solution-explorer0.3.1提供解决方案视图本篇代码以下代码进行调整：https://github.com/ken-io/asp…3、准备工作安装MySQL数据库：https://dev.mysql.com/downloa…Windows环境下载安装包后双击安装，一直下一步即可。MacOS环境参考：https://ken.io/note/macos-mys…CentOS环境参考：https://ken.io/note/centos-my…4、前置知识控制反转（IOC）原则与依赖注入（DI）ASP.NET Core 默认集成了DI。所有官方模块的引入都要使用DI的方式引入。https://baike.baidu.com/item/IOCLinq使用教程https://docs.microsoft.com/zh…二、EF Core + MySQL 前置准备EF Core 全称：Entity Framework Core，为微软为 .NET Core平台开发的ORM框架。对应是 .NET Framework平台的 Entity Framework（EF），无论是EF还是EF Core都可以说是 .NET 平台开发效率最高的ORM框架。1、引入 EF Core + MySQL ProviderEF Core已经集成在 ASP.NET Core 中，但默认并不支持MySQL，如果需要连接MySQL,需要添加MySQL相关的Provider，这里我选择的是：Pomelo.EntityFrameworkCore.MySql任意命令行操作即可，我用的是VS Code自带的命令行//进入项目根目录： Ken.Tutorial.Webcd Ken.Tutorial.Web//添加Packagedotnet add package Pomelo.EntityFrameworkCore.MySql这里我添加的 MySql.Data.EntityFrameworkCore 版本是 8.0.13，如果你想跟我使用一样的版本，可以使用以下命令：dotnet add package Pomelo.EntityFrameworkCore.MySql –version 2.1.42、创建MySQL库表创建数据库CREATE DATABASE ken_tutorial;创建表USE ken_tutorial;DROP TABLE IF EXISTS user;CREATE TABLE user ( id int(11) NOT NULL AUTO_INCREMENT, name varchar(255) DEFAULT NULL, age int(11) DEFAULT NULL, hobby varchar(500) DEFAULT NULL, PRIMARY KEY (id)) ENGINE=InnoDB DEFAULT CHARSET=utf8;3、配置链接字符串在项目根目录Ken.Tutorial.Web中创建配置文件appsettings.json,并写入以下配置{ “ConnectionStrings”: { “testdb”: “server=localhost;database=Ken.Tutorial;uid=root;pwd=root;” }}4、实体&DbContext准备创建user表对应实体在项目根目录Ken.Tutorial.Web中创建目录Models,并在其中创建类：UserEntity.csusing System;using System.ComponentModel.DataAnnotations;using System.ComponentModel.DataAnnotations.Schema;namespace Ken.Tutorial.Web.Models{ [Table(“user”)] [Serializable] public class UserEntity { [Key]//主键 [DatabaseGenerated(DatabaseGeneratedOption.Identity)]//自增列 [Column(“id”)] public int Id { get; set; } [Column(“name”)] public string Name { get; set; } [Column(“age”)] public int Age { get; set; } [Column(“hobby”)] public string Hobby { get; set; } }}创建DBContext在项目根目录Ken.Tutorial.Web中创建目录Repositories,并在其中创建类：TutorialDbContext.csusing System;using Microsoft.EntityFrameworkCore;using Microsoft.Extensions.Configuration;namespace Ken.Tutorial.Web.Repositories{ public class TutorialDbContext : DbContext { private IConfiguration Configuration { get; } public TutorialDbContext(IConfiguration configuration) { this.Configuration = configuration; } protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) { optionsBuilder.UseMySql(Configuration.GetConnectionString(“testdb”)); } ppublic DbSet<UserEntity> Users { get; set; } }}TutorialDbContext 继承了 DbContext，然后再Context初始化时配置MySQL链接。由于 ASP.NET Core 默认使用了DI组件，所以我们取配置文件，就需要在构造函数中获取 IConfiguration 注入的实例。在Startup.cs配置注入，以便数据访问类可以访问DI方式获取TutorialDbContextpublic void ConfigureServices(IServiceCollection services){ //引入MVC模块 services.AddMvc(); //配置DbContext注入 services.AddTransient<TutorialDbContext>();}三、EF Core + Linq to entity 访问数据库1、创建Linq To Entity 数据访问类using System;using System.Collections.Generic;using System.Linq;using Ken.Tutorial.Web.Models;namespace Ken.Tutorial.Web.Repositories{ public class TutorialRepository { private TutorialDbContext DbContext { get; } public TutorialRepository(TutorialDbContext dbContext) { //在构造函数中注入DbContext this.DbContext = dbContext; } //添加 public int Add(UserEntity user) { using (DbContext) { //由于我们在UserEntity.Id配置了自增列的Attribute，EF执行完成后会自动把自增列的值赋值给user.Id DbContext.Users.Add(user); return DbContext.SaveChanges(); } } //删除 public int Delete(int id) { using (DbContext) { var userFromContext = DbContext.Users.FirstOrDefault(u => u.Id == id); DbContext.Users.Remove(userFromContext); return DbContext.SaveChanges(); } } //更新 public int Update(UserEntity user) { using (DbContext) { var userFromContext = DbContext.Users.FirstOrDefault(u => u.Id == user.Id); userFromContext.Name = user.Name; userFromContext.Age = user.Age; userFromContext.Hobby = user.Hobby; return DbContext.SaveChanges(); } } //查询 public UserEntity QueryById(int id) { using (DbContext) { return DbContext.Users.FirstOrDefault(u => u.Id == id); } } //查询集合 public List<UserEntity> QueryByAge(int age) { using (DbContext) { return DbContext.Users.Where(u => u.Age == age).ToList(); } } //查看指定列 public List<string> QueryNameByAge(int age) { using (DbContext) { return DbContext.Users.Where(u => u.Age == age).Select(u => u.Name).ToList(); } } //分页查询 public List<UserEntity> QueryUserPaging(int pageSize, int page) { using (DbContext) { return DbContext.Users.Skip(pageSize * (page - 1)).Take(pageSize).ToList(); } } //事务：将年龄<0的用户修改年龄为0 public int FixAge() { using (DbContext) { using (var transaction = DbContext.Database.BeginTransaction()) { try { var userListFromContext = DbContext.Users.Where(u => u.Age < 0); foreach (UserEntity u in userListFromContext) { u.Age = 0; } var count = DbContext.SaveChanges(); transaction.Commit(); return count; } catch { transaction.Rollback(); return 0; } } } } }}2、创建测试API在Controllers文件夹下创建EfCoreController.cs作为访问入口using System;using Ken.Tutorial.Web.Models;using Ken.Tutorial.Web.Repositories;using Microsoft.AspNetCore.Mvc;namespace Ken.Tutorial.Web.Controllers{ public class EfCoreController : Controller { public TutorialRepository Repository { get; } public EfCoreController(TutorialRepository repository) { this.Repository = repository; } public IActionResult Add(UserEntity user) { var message = Repository.Add(user) > 0 ? “success” : “failed”; return Json(new { Message = message, User = user }); } public IActionResult Delete(int id) { var message = Repository.Delete(id) > 0 ? “success” : “failed”; return Json(new { Message = message }); } public IActionResult Update(UserEntity user) { var message = Repository.Update(user) > 0 ? “success” : “failed”; return Json(new { Message = message, User = user }); } public IActionResult QueryById(int id) { var user = Repository.QueryById(id); return Json(new { User = user }); } public IActionResult QueryByAge(int age) { var users = Repository.QueryByAge(age); return Json(new { Users = users }); } public IActionResult QueryNameByAge(int age) { var users = Repository.QueryNameByAge(age); return Json(new { Users = users }); } public IActionResult QueryUserPaging(int pageSize, int page) { var users = Repository.QueryUserPaging(pageSize, page); return Json(new { Users = users }); } public IActionResult FixAge() { var count = Repository.FixAge(); return Json(new { FixCount = count }); } }}3、启动测试在Startup.cs中配置Repository注入public void ConfigureServices(IServiceCollection services){ //其他代码省略 //配置Repository注入 services.AddTransient<TutorialRepository>();}启动项目并测试API示例添加用户/efcore/add?name=ken&age=18&hobby=coding删除用户/efcore/delete?id=0更新用户/efcore/update?id=1&name=ken&age=666&hobby=codingOrGaming查询单个用户/efcore/querybyid?id=0查询多个用户/efcore/querybyage?age=18查询多个用户名/efcore/querynamebyage?age=18分页查询用户/efcore/queryuserpaging?pagesize=3&page=1修复异常年龄/efcore/fixage四、EF Core + 原生SQL 访问数据库1、创建EF Core + 原生SQL 数据访问类using System;using System.Collections.Generic;using System.Data;using System.Data.SqlClient;using System.Linq;using Ken.Tutorial.Web.Models;using Microsoft.EntityFrameworkCore;using MySql.Data.MySqlClient;namespace Ken.Tutorial.Web.Repositories{ public class TutorialWithSqlRepository { private TutorialDbContext DbContext { get; } public TutorialWithSqlRepository(TutorialDbContext dbContext) { //在构造函数中注入DbContext this.DbContext = dbContext; } //添加 public int Add(UserEntity user) { using (var connection = DbContext.Database.GetDbConnection()) { connection.Open(); var command = connection.CreateCommand() as MySqlCommand; command.CommandText = “INSERT INTO user (name,age,hobby) VALUES(@name,@age,@hobby)”; command.Parameters.Add(new MySqlParameter() { ParameterName = “@name”, DbType = DbType.String, Value = user.Name }); command.Parameters.Add(new MySqlParameter() { ParameterName = “@age”, DbType = DbType.Int32, Value = user.Age }); command.Parameters.Add(new MySqlParameter() { ParameterName = “@hobby”, DbType = DbType.String, Value = user.Hobby }); var count = command.ExecuteNonQuery(); //获取插入时产生的自增列Id并赋值给user.Id使用 user.Id = (int)command.LastInsertedId; return count; } } //删除 public int Delete(int id) { using (DbContext) { return DbContext.Database.ExecuteSqlCommand( “DELETE FROM user WHERE id={0}”, id); } } //更新 public int Update(UserEntity user) { using (DbContext) { return DbContext.Database.ExecuteSqlCommand( “UPDATE user SET name={0}, age={1}, hobby={2} WHERE id={3}”, user.Name, user.Age, user.Hobby, user.Id); } } //查询 public UserEntity QueryById(int id) { using (DbContext) { return DbContext.Users.FromSql(“SELECT id,name,age,hobby FROM user WHERE id={0}”, id).FirstOrDefault(); } } //查询集合 public List<UserEntity> QueryByAge(int age) { using (DbContext) { return DbContext.Users.FromSql(“SELECT id,name,age,hobby FROM user WHERE age={0}”, age).ToList(); } } //查看指定列 public List<string> QueryNameByAge(int age) { using (DbContext) { return DbContext.Users.FromSql(“SELECT id,name FROM user WHERE age={0}”, age).Select(u => u.Name).ToList(); } } //分页查询 public List<UserEntity> QueryUserPaging(int pageSize, int page) { using (DbContext) { return DbContext.Users.FromSql(“SELECT id,name,age,hobby FROM user LIMIT {0},{1}”, pageSize * (page - 1), pageSize).ToList(); } } //事务：将年龄<0的用户修改年龄为0 public int FixAge() { using (DbContext) { using (var connection = DbContext.Database.GetDbConnection()) { //打开连接 connection.Open(); //开启事务 using (var transaction = connection.BeginTransaction()) { try { //获取命令对象 var command = connection.CreateCommand(); command.Transaction = transaction; command.CommandText = “UPDATE user SET age=@age WHERE age<@age”; command.Parameters.Add(new MySqlParameter() { ParameterName = “@age”, DbType = DbType.Int32, Value = 0 }); var count = command.ExecuteNonQuery(); transaction.Commit(); return count; } catch (Exception ex) { connection.Close(); transaction.Rollback(); return 0; } } } } } }}2、创建测试API在Controllers文件夹下创建EfCoreWithSqlController.cs作为访问入口using System;using Ken.Tutorial.Web.Models;using Ken.Tutorial.Web.Repositories;using Microsoft.AspNetCore.Mvc;namespace Ken.Tutorial.Web.Controllers{ public class EfCoreWithSqlController : Controller { public TutorialWithSqlRepository Repository { get; } public EfCoreWithSqlController(TutorialWithSqlRepository repository) { this.Repository = repository; } public IActionResult Add(UserEntity user) { var message = Repository.Add(user) > 0 ? “success” : “failed”; return Json(new { Message = message, User = user }); } public IActionResult Delete(int id) { var message = Repository.Delete(id) > 0 ? “success” : “failed”; return Json(new { Message = message }); } public IActionResult Update(UserEntity user) { var message = Repository.Update(user) > 0 ? “success” : “failed”; return Json(new { Message = message, User = user }); } public IActionResult QueryById(int id) { var user = Repository.QueryById(id); return Json(new { User = user }); } public IActionResult QueryByAge(int age) { var users = Repository.QueryByAge(age); return Json(new { Users = users }); } public IActionResult QueryNameByAge(int age) { var users = Repository.QueryNameByAge(age); return Json(new { Users = users }); } public IActionResult QueryUserPaging(int pageSize, int page) { var users = Repository.QueryUserPaging(pageSize, page); return Json(new { Users = users }); } public IActionResult FixAge() { var count = Repository.FixAge(); return Json(new { FixCount = count }); } }}3、启动测试在Startup.cs中配置Repository注入public void ConfigureServices(IServiceCollection services){ //其他代码省略 //配置Repository注入 services.AddTransient<TutorialWithSqlRepository>();}启动项目并测试API示例添加用户/efcorewithsql/add?name=ken&age=18&hobby=coding删除用户/efcorewithsql/delete?id=0更新用户/efcorewithsql/update?id=1&name=ken&age=666&hobby=codingOrGaming查询单个用户/efcorewithsql/querybyid?id=0查询多个用户/efcorewithsql/querybyage?age=18查询多个用户名/efcorewithsql/querynamebyage?age=18分页查询用户/efcorewithsql/queryuserpaging?pagesize=3&page=1修复异常年龄/efcorewithsql/fixage五、备注1、附录本文代码示例https://github.com/ken-io/asp…本文参考https://docs.microsoft.com/zh… https://www.learnentityframew… https://mysql-net.github.io/M…本文首发于我的独立博客：https://ken.io/note/asp.net-c…
...