C#-EF Core使用MySQL数据库

简介

Entity Framework Core (EF Core)是微软推荐的基于.NET Core 的应用程序数据访问技术。开源，轻量级，可扩展并且支持跨平台开发。EF Core是一种对象关系映射器（ORM），通过应用程序实体对象和关系数据库中的数据的映射，使得开发人员能够以面向对象的方式处理数据。

使用

在项目里头安装EF Core和MySQL相关的NuGet包：Microsoft.EntityFrameworkCore，Pomelo.EntityFrameworkCore.MySql ，如果你使用的是其他数据库，那么就换成其他的数据库相关的包即可。

这里创建的是一个web项目，桌面项目其实大同小异，创建一个类继承DbContext，DbContext 是 EF 中非常重要的一个组件，它拥有数据库的会话连接，数据查询，修改保存数据，缓存，事务管理等等作用。

比如我这里创建的是这样的：

using Acg.Models;
using Microsoft.EntityFrameworkCore;

namespace Acg.DataBase
{
    public class AcgbiuDbContext : DbContext
    {
        public DbSet<TopicModel> Acgbiu_Topic { get; set; }
        public DbSet<TopicRelationshipsModel> Acgbiu_Topic_Relationships { get; set; }
        public DbSet<TermModel> Acgbiu_Terms { get; set; }
        public DbSet<FocusModel> Acgbiu_Focus { get; set; }
        public AcgbiuDbContext(DbContextOptions<AcgbiuDbContext> options) : base(options)
        {
            this.Database.Migrate();
        }
        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<TopicModel>();
            modelBuilder.Entity<TopicRelationshipsModel>();
            modelBuilder.Entity<FocusModel>();
        }
    }
}

创建实体类，对应数据库中的表结构，比如我这里的TopicModel。

using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace Acg.Models
{
    [Table("acgbiu_topic")]
    public class TopicModel
    {
        [Key]
        public int topic_id { get; set; }
        public string name { get; set; }
        public string description { get; set; }
        public string icon { get; set; }
        public string remark { get; set; }
    }
}

使用依赖注入的方式使用刚才创建的AcgbiuDbContext ，在Startup.cs中注册AcgbiuDbContext。

public void ConfigureServices(IServiceCollection services)
        {
            services.AddControllersWithViews();
            string connectStr = $"server = 服务器地址; port=端口号;database = 使用的数据库; uid = 数据库连接用户名; password = 密码";
            services.AddDbContext<AcgbiuDbContext>(oprions => oprions.UseMySql(connectStr, new MySqlServerVersion(new Version(10,5,6))));
        }

以一个控制器类的代码为例，看下数据的增删改查。

构造函数注入的方式，获取我们刚才注册的AcgbiuDbContext，然后就可以使用了。

//获取表的所有数据
AcgbiuDbContext.Acgbiu_Topic.ToList();

//添加数据
AcgbiuDbContext.Acgbiu_Topic.Add(topicModel);

//更新数据
AcgbiuDbContext.Acgbiu_Topic.Update(topicModel);

//删除数据
AcgbiuDbContext.Acgbiu_Topic.Remove(topic);

//异步的方式将前面的增删改，保存到数据库中
await AcgbiuDbContext.SaveChangesAsync();
using Acg.DataBase;
using Acg.Models;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Configuration;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace Acg.Controllers
{
    [Route("Api/[controller]/[action]/{id?}")]
    public class ApiTopicController: ControllerBase
    {
        private readonly IConfiguration Configuration;
        private readonly AcgbiuDbContext AcgbiuDbContext;
        public ApiTopicController(IConfiguration configuration, AcgbiuDbContext acgbiuDbContext) 
        {
            Configuration=configuration;
            AcgbiuDbContext = acgbiuDbContext;
        }
        [HttpGet]
        public async Task<IActionResult> Topics(int id)
        {
            List<TopicModel> topicModels = new List<TopicModel>();
            topicModels = AcgbiuDbContext.Acgbiu_Topic.ToList();
            if (id != 0) 
            {
                TopicModel topicModel = topicModels.Find(x=>x.topic_id==id);
                return new JsonResult(topicModel);
            }
            return new JsonResult(topicModels);
        }
        
        [HttpPost]
        public async Task<IActionResult> Topics([FromBody] TopicModel topicModel)
        {
            bool res = true;
            AcgbiuDbContext.Acgbiu_Topic.Add(topicModel);
            int num=await AcgbiuDbContext.SaveChangesAsync();
            if (num <= 0)
            {
                res = false;
            }
            return new JsonResult(num);
        }
        [HttpPut]
        public async Task<IActionResult> Topics(int id,[FromBody]TopicModel topicModel)
        {
            bool res = true;
            if (id != topicModel.topic_id) 
            {
                return BadRequest();
            }
            AcgbiuDbContext.Acgbiu_Topic.Update(topicModel);
            int num = await AcgbiuDbContext.SaveChangesAsync();
            return new JsonResult(num);
        }
        [HttpDelete]
        public async Task<IActionResult> Topics(long id)
        {
            var topic = await AcgbiuDbContext.Acgbiu_Topic.FindAsync(id);
            if (topic == null)
            {
                return NotFound();
            }
            AcgbiuDbContext.Acgbiu_Topic.Remove(topic);
            await AcgbiuDbContext.SaveChangesAsync();
            return NoContent();
        }

        [HttpGet]
        public async Task<IActionResult> TopicTerms(int id)
        {
            if (id > 0)
            {
                var terms = AcgbiuDbContext.Acgbiu_Topic_Relationships.Join(AcgbiuDbContext.Acgbiu_Terms, x => x.term_id, y => y.term_id, (x, y) => new
                {
                    x.id,
                    x.topic_id,
                    x.term_id,
                    y.name,
                    y.slug
                }).Where(p => p.topic_id == id).ToList();
                return new JsonResult(terms);
            }
            else
            {
                var terms = AcgbiuDbContext.Acgbiu_Topic_Relationships.Join(AcgbiuDbContext.Acgbiu_Terms, x => x.term_id, y => y.term_id, (x, y) => new
                {
                    x.id,
                    x.topic_id,
                    x.term_id,
                    y.name,
                    y.slug
                }).ToList();
                return new JsonResult(terms);
            }
        }

        [HttpPost]
        public async Task<IActionResult> TopicTerms([FromBody] TopicRelationshipsModel topicRelationshipsModel)
        {
            try
            {
                if (topicRelationshipsModel==null||topicRelationshipsModel.topic_id==0|| topicRelationshipsModel.term_id==0)
                {
                    return BadRequest();
                }
                bool isExist = AcgbiuDbContext.Acgbiu_Topic_Relationships.Any(x=>x.topic_id==topicRelationshipsModel.topic_id&&x.term_id==topicRelationshipsModel.term_id);
                if (!isExist) 
                {
                    await AcgbiuDbContext.Acgbiu_Topic_Relationships.AddAsync(topicRelationshipsModel);
                    await AcgbiuDbContext.SaveChangesAsync();
                }
                else
                {
                    return BadRequest();
                }
                
            }catch(Exception ex)
            {
                return BadRequest();
            }
            return NoContent();
        }
    }
}