.net core使用ef core操作mysql数据库

新建.net core webapi项目

在NuGet包管理器中搜索 MySql.Data.EntityFrameworkCore并安装，安装的8.0.14版本，只安装这一个就够了

 安装后创建DataContext.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.EntityFrameworkCore;

namespace WebApplication4
{
    public class DataContext : DbContext
    {
        public DataContext(DbContextOptions<DataContext> options) : base(options)
        {

        }

        public DbSet<TbUser> tbusers { get; set; }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<TbUser>();
        }
    }
}

TbUser.cs

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations.Schema;
using System.Linq;
using System.Threading.Tasks;

namespace WebApplication4
{
    public class TbUser
    {
        [DatabaseGenerated(DatabaseGeneratedOption.None)]
        public int ID { get; set; }
        public string NickName { get; set; }
        public string Email { get; set; }
    }
}

 

appsettings.json中添加连接字符串

{
  "Logging": {
    "LogLevel": {
      "Default": "Warning"
    }
  },
  "ConnectionStrings": {
    "dbconn": "server=localhost;user id=root;password=root;persistsecurityinfo=True;database=cnis;SslMode=none"
  },
  "AllowedHosts": "*"
}

 

Startup.cs中注入DbContext

public void ConfigureServices(IServiceCollection services)
{
     services.AddDbContext<DataContext>(options => options.UseMySQL(Configuration.GetConnectionString("dbconn")));
     services.AddMvc().SetCompatibilityVersion(CompatibilityVersion.Version_2_2);
}

在Configure方法后面添加DataContext context参数，添加context.Database.EnsureCreated();这样程序运行时，库表不存在的话，会自动创建，不然会报数据库不存在的错误，不用担心DataContext context的传参，该参数会被.net core框架自动注入

        // This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
        public void Configure(IApplicationBuilder app, IHostingEnvironment env, DataContext context)
        {
            if (env.IsDevelopment())
            {
                app.UseDeveloperExceptionPage();
            }
            else
            {
                // The default HSTS value is 30 days. You may want to change this for production scenarios, see https://aka.ms/aspnetcore-hsts.
                app.UseHsts();
            }

            app.UseHttpsRedirection();
            app.UseMvc();

            context.Database.EnsureCreated();
        }

 

 

 

添加UserController.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Mvc;

namespace WebApplication4.Controllers
{

    [Produces("application/json")]
    [Route("api/user")]
    public class UserController: ControllerBase
    {
        private readonly DataContext _context;

        public UserController(DataContext context)
        {
            this._context = context;
        }

        // POST api/user
        [HttpPost]
        public int Post(TbUser user)
        {
            _context.Add(user);

            _context.SaveChanges();

            return 200;
        }

        // GET api/user
        [HttpGet]
        public List<TbUser> Get()
        {
            _context.Database.EnsureCreated();
            var users = _context.tbusers;
            List<TbUser> items = new List<TbUser>();
            foreach (var item in users)
            {
                items.Add(item);
            }
            return items;
        }

        // GET api/user/5
        [HttpGet("{id}")]
        public TbUser Get(int id)
        {
            var u = _context.tbusers.Find(id);
            return u;
        }

        // DELETE api/user/5
        [HttpDelete("{id}")]
        public int Delete(int id)
        {
            var u = _context.tbusers.Remove(new TbUser() { ID = id });
            _context.SaveChanges();
            return 200;
        }

        // PUT api/user/5
        [HttpPut("{id}")]
        public int Put(int id, TbUser user)
        {
            var u = _context.tbusers.Update(user);
            _context.SaveChanges();
            return 200;
        }
    }
}

 

这时运行程序访问系统，就会自动创建库和表，如果需要做数据库迁移，则执行如下命令：

 

如果用的是vs code，则在终端中执行

dotnet ef migrations add firstMigrations

dotnet ef database update

PS F:\projects\myApi\MyApi> dotnet ef migrations add firstMigrations
info: Microsoft.EntityFrameworkCore.Infrastructure[10403]
      Entity Framework Core 2.2.1-servicing-10028 initialized 'DataContext' using provider 'MySql.Data.EntityFrameworkCore' with options: None
Done. To undo this action, use 'ef migrations remove'

  PS F:\projects\myApi\MyApi> dotnet ef database update
  Build failed.
  PS F:\projects\myApi\MyApi>

 

如果用的是vs，在NuGet程序包管理器控制台中执行迁移命令

Add-Migration FirstMigration
Update-Database

首次进行迁移执行update-database的时候，会报错：

MySql.Data.MySqlClient.MySqlException (0x80004005): Table 'test.__efmigrationshistory' doesn't exit

解决方式是：
手动创建这个 'test.__efmigrationshistory' 表

 CREATE TABLE `__EFMigrationsHistory` 
 (
 `MigrationId` nvarchar(150) NOT NULL,
 `ProductVersion` nvarchar(32) NOT NULL,
PRIMARY KEY(`MigrationId`)
 );

然后执行update-database
这样数据迁移就能完成了，表现表已经创建好了，再运行程序

http://localhost:17800/api/user

返回json数据

[{"id":1,"nickName":"张三","email":"123@abc.com"}]