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

新建.net core webapi项目

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

安装后创建DataContext.cs

usingSystem;usingSystem.Collections.Generic;usingSystem.Linq;usingSystem.Threading.Tasks;usingMicrosoft.EntityFrameworkCore;namespaceWebApplication4

{public classDataContext : DbContext

{public DataContext(DbContextOptions options) : base(options)

{

}public DbSet tbusers { get; set; }protected override voidOnModelCreating(ModelBuilder modelBuilder)

{

modelBuilder.Entity();

}

}

}

TbUser.cs

usingSystem;usingSystem.Collections.Generic;usingSystem.ComponentModel.DataAnnotations.Schema;usingSystem.Linq;usingSystem.Threading.Tasks;namespaceWebApplication4

{public classTbUser

{

[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 voidConfigureServices(IServiceCollection services)

{

services.AddDbContext(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 voidConfigure(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, seehttps://aka.ms/aspnetcore-hsts.

app.UseHsts();

}

app.UseHttpsRedirection();

app.UseMvc();

context.Database.EnsureCreated();

}

添加UserController.cs

usingSystem;usingSystem.Collections.Generic;usingSystem.Linq;usingSystem.Threading.Tasks;usingMicrosoft.AspNetCore.Mvc;namespaceWebApplication4.Controllers

{

[Produces("application/json")]

[Route("api/user")]public classUserController: ControllerBase

{private readonlyDataContext _context;publicUserController(DataContext context)

{this._context =context;

}//POST api/user

[HttpPost]public intPost(TbUser user)

{

_context.Add(user);

_context.SaveChanges();return 200;

}//GET api/user

[HttpGet]public ListGet()

{

_context.Database.EnsureCreated();var users =_context.tbusers;

List items = new List();foreach (var item inusers)

{

items.Add(item);

}returnitems;

}//GET api/user/5

[HttpGet("{id}")]public TbUser Get(intid)

{var u =_context.tbusers.Find(id);returnu;

}//DELETE api/user/5

[HttpDelete("{id}")]public int Delete(intid)

{var u = _context.tbusers.Remove(new TbUser() { ID =id });

_context.SaveChanges();return 200;

}//PUT api/user/5

[HttpPut("{id}")]public int Put(intid, 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 Core2.2.1-servicing-10028 initialized 'DataContext' using provider 'MySql.Data.EntityFrameworkCore'with options: None

Done. To undothis 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"}]