EntityFramework之旅-Hello MySql
作为起始这篇博客我想从搭配环境说起，目标是看完博客之后初学者能够快速构建起EntityFramework与MySql之间的连接。我们会使用CodeFirst的模式去做。首先我先介绍一下我的电脑环境：Winodws 10 专业版mysql-community-5.5.62.0MySql Connector Net 8.0.13Visual Studio 2017.NET Fr...
·
作为起始这篇博客我想从搭配环境说起，目标是看完博客之后初学者能够快速构建起EntityFramework与MySql之间的连接。我们会使用CodeFirst的模式去做。
首先我先介绍一下我的电脑环境：
Winodws 10 专业版
mysql-community-5.5.62.0
MySql Connector Net 8.0.13
Visual Studio 2017
.NET Framework 4
读者请尽量按照同样的环境进行操作，在EntityFramework与MySql建立连接作者吃了很多的亏，最惨的时候一个多星期没有搞通，至于什么原因就不方便透露了，说出来比较丢人。让我们回到主题上来。
创建一个控制台项目，我命名为EntityFrameworkBlogHelloMySql
在程序包管理控制器中执行以下命令，请注意一定要使用对应的版本号。
install-package entityframework -version 6.4.0
install-package MySql.Data -version 6.9.12.0
install-package MySql.Data.Entity -version 6.9.12.0
在App.config文件中添加链接数据库的字符串。
<connectionStrings>
<add name="con" providerName="MySql.Data.MySqlClient"
connectionString="server=localhost;port=3306;database=Code;uid=root;password=123456;Allow Zero Datetime=True;Convert Zero Datetime=True;pooling=true;CharSet=utf8;"/>
</connectionStrings>App.config文件中的整体代码如下：
<?xml version="1.0" encoding="utf-8"?>
<configuration>
<configSections>
<!-- For more information on Entity Framework configuration, visit http://go.microsoft.com/fwlink/?LinkID=237468 -->
<section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
</configSections>
<connectionStrings>
<add name="con" providerName="MySql.Data.MySqlClient"
connectionString="server=localhost;port=3306;database=Code;uid=root;password=123456;Allow Zero Datetime=True;Convert Zero Datetime=True;pooling=true;CharSet=utf8;"/>
</connectionStrings>
<entityFramework>
<providers>
<provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
<provider invariantName="MySql.Data.MySqlClient" type="MySql.Data.MySqlClient.MySqlProviderServices, MySql.Data.Entity.EF6, Version=6.9.12.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d"></provider>
</providers>
</entityFramework>
<system.data>
<DbProviderFactories>
<remove invariant="MySql.Data.MySqlClient" />
<add name="MySQL Data Provider" invariant="MySql.Data.MySqlClient" description=".Net Framework Data Provider for MySQL" type="MySql.Data.MySqlClient.MySqlClientFactory, MySql.Data, Version=6.9.12.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" />
</DbProviderFactories>
</system.data>
</configuration>创建一个文件夹Models，并添加上下文类内容如下：
namespace EntityFrameworkBlogHelloMySql.Models
{
using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Data.Entity.ModelConfiguration.Conventions;
using System.Linq;
using System.Text;
public class CodeDbContext:DbContext
{
public CodeDbContext() : base("con")
{
Database.SetInitializer<CodeDbContext>(null);
}
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
base.OnModelCreating(modelBuilder);
base.OnModelCreating(modelBuilder);
//EF4.1~4.3 Code First模式级联删除是默认打开的，在同一个实体多次引用另一个实体时，
//需要单独设置关闭某个外键关系下的级联删除，需要写Fluent API代码，而且级联删除有时会造成麻烦。干脆整个关了吧：
modelBuilder.Conventions.Remove<OneToManyCascadeDeleteConvention>();
modelBuilder.Conventions.Remove<ManyToManyCascadeDeleteConvention>();
}
}
}
接下来需要创建两个类，BookType、Book代码如下
namespace EntityFrameworkBlogHelloMySql.Models
{
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Linq;
using System.Text;
public class BookType
{
public long Id { get; set; }
[StringLength(20),Required]
public string Name { get; set; }
}
}
namespace EntityFrameworkBlogHelloMySql.Models
{
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations.Schema;
using System.Linq;
using System.Text;
public class Book
{
public long Id { get; set; }
public string Title { get; set; }
public string Descr { get; set; }
public DateTime CreateDateTime { get; set; } = DateTime.Now;
[ForeignKey(nameof(BookType))]
public long BookTypeId { get; set; }
public BookType BookType { get; set; }
}
}
在上下文中添加DbSet<BookType>、DbSet<Book>，代码如下：
namespace EntityFrameworkBlogHelloMySql.Models
{
using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Data.Entity.ModelConfiguration.Conventions;
using System.Linq;
using System.Text;
public class CodeDbContext:DbContext
{
public CodeDbContext() : base("con")
{
Database.SetInitializer<CodeDbContext>(null);
}
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
base.OnModelCreating(modelBuilder);
base.OnModelCreating(modelBuilder);
//EF4.1~4.3 Code First模式级联删除是默认打开的，在同一个实体多次引用另一个实体时，
//需要单独设置关闭某个外键关系下的级联删除，需要写Fluent API代码，而且级联删除有时会造成麻烦。干脆整个关了吧：
modelBuilder.Conventions.Remove<OneToManyCascadeDeleteConvention>();
modelBuilder.Conventions.Remove<ManyToManyCascadeDeleteConvention>();
}
public DbSet<BookType> BookTypes { get; set; }
public DbSet<Book> Books { get; set; }
}
}
到此为止，我们完成了CodeFirst的编码部分，接下来我们要进行迁移的工作。程序包管理控制器中执行以下命令。
启动迁移： enable-migrations
需要注意的是路径不能包含汉字
执行完启动迁移后，会自动在项目目录下创建Migrations文件夹，Configuration.cs文件代码如下
namespace EntityFrameworkBlogHelloMySql.Migrations
{
using System;
using System.Data.Entity;
using System.Data.Entity.Migrations;
using System.Linq;
internal sealed class Configuration : DbMigrationsConfiguration<EntityFrameworkBlogHelloMySql.Models.CodeDbContext>
{
public Configuration()
{
AutomaticMigrationsEnabled = false;
}
protected override void Seed(EntityFrameworkBlogHelloMySql.Models.CodeDbContext context)
{
// This method will be called after migrating to the latest version.
// You can use the DbSet<T>.AddOrUpdate() helper extension method
// to avoid creating duplicate seed data.
}
}
}
其中Seed方法可以创建一些默认的数据，如创建以下数据
namespace EntityFrameworkBlogHelloMySql.Migrations
{
using System;
using System.Data.Entity;
using System.Data.Entity.Migrations;
using EntityFrameworkBlogHelloMySql.Models;
using System.Linq;
internal sealed class Configuration : DbMigrationsConfiguration<EntityFrameworkBlogHelloMySql.Models.CodeDbContext>
{
public Configuration()
{
AutomaticMigrationsEnabled = false;
}
protected override void Seed(EntityFrameworkBlogHelloMySql.Models.CodeDbContext context)
{
// This method will be called after migrating to the latest version.
// You can use the DbSet<T>.AddOrUpdate() helper extension method
// to avoid creating duplicate seed data.
context.BookTypes.AddOrUpdate(new BookType() { Name = "散文" });
context.BookTypes.AddOrUpdate(new BookType() { Name = "科幻" });
context.BookTypes.AddOrUpdate(new BookType() { Name = "悬疑" });
}
}
}
创建迁移，并将其命名为Createdb，命令如下：
add-migration 'Createdb'
这个时候我遇到了这样的提示
No MigrationSqlGenerator found for provider 'MySql.Data.MySqlClient'. Use the SetSqlGenerator method in the target migrations configuration class to register additional SQL generators.
这个时候需要对CodeDbContext.cs、Configuration.cs文件进行修改，分别添加[DbConfigurationType(typeof(MySqlEFConfiguration))]、SetSqlGenerator("MySql.Data.MySqlClient", new MySql.Data.Entity.MySqlMigrationSqlGenerator());具体代码如下
namespace EntityFrameworkBlogHelloMySql.Models
{
using System;
using System.Collections.Generic;
using System.Data.Entity;
using MySql.Data.Entity;
using System.Data.Entity.ModelConfiguration.Conventions;
using System.Linq;
using System.Text;
[DbConfigurationType(typeof(MySqlEFConfiguration))]
public class CodeDbContext:DbContext
{
public CodeDbContext() : base("con")
{
Database.SetInitializer<CodeDbContext>(null);
}
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
base.OnModelCreating(modelBuilder);
base.OnModelCreating(modelBuilder);
//EF4.1~4.3 Code First模式级联删除是默认打开的，在同一个实体多次引用另一个实体时，
//需要单独设置关闭某个外键关系下的级联删除，需要写Fluent API代码，而且级联删除有时会造成麻烦。干脆整个关了吧：
modelBuilder.Conventions.Remove<OneToManyCascadeDeleteConvention>();
modelBuilder.Conventions.Remove<ManyToManyCascadeDeleteConvention>();
}
public DbSet<BookType> BookTypes { get; set; }
public DbSet<Book> Books { get; set; }
}
}
namespace EntityFrameworkBlogHelloMySql.Migrations
{
using System;
using System.Data.Entity;
using System.Data.Entity.Migrations;
using EntityFrameworkBlogHelloMySql.Models;
using System.Linq;
internal sealed class Configuration : DbMigrationsConfiguration<EntityFrameworkBlogHelloMySql.Models.CodeDbContext>
{
public Configuration()
{
AutomaticMigrationsEnabled = false;
SetSqlGenerator("MySql.Data.MySqlClient", new MySql.Data.Entity.MySqlMigrationSqlGenerator());
}
protected override void Seed(EntityFrameworkBlogHelloMySql.Models.CodeDbContext context)
{
// This method will be called after migrating to the latest version.
// You can use the DbSet<T>.AddOrUpdate() helper extension method
// to avoid creating duplicate seed data.
context.BookTypes.AddOrUpdate(new BookType() { Name = "散文" });
context.BookTypes.AddOrUpdate(new BookType() { Name = "科幻" });
context.BookTypes.AddOrUpdate(new BookType() { Name = "悬疑" });
}
}
}
再次执行创建迁移命令即可，生成有时间戳的迁移文件，202005040512083_Createdb.cs其代码如下
namespace EntityFrameworkBlogHelloMySql.Migrations
{
using System;
using System.Data.Entity.Migrations;
public partial class Createdb : DbMigration
{
public override void Up()
{
CreateTable(
"dbo.Books",
c => new
{
Id = c.Long(nullable: false, identity: true),
Title = c.String(unicode: false),
Descr = c.String(unicode: false),
CreateDateTime = c.DateTime(nullable: false, precision: 0),
BookTypeId = c.Long(nullable: false),
})
.PrimaryKey(t => t.Id)
.ForeignKey("dbo.BookTypes", t => t.BookTypeId)
.Index(t => t.BookTypeId);
CreateTable(
"dbo.BookTypes",
c => new
{
Id = c.Long(nullable: false, identity: true),
Name = c.String(nullable: false, maxLength: 20, storeType: "nvarchar"),
})
.PrimaryKey(t => t.Id);
}
public override void Down()
{
DropForeignKey("dbo.Books", "BookTypeId", "dbo.BookTypes");
DropIndex("dbo.Books", new[] { "BookTypeId" });
DropTable("dbo.BookTypes");
DropTable("dbo.Books");
}
}
}
接下来我们将执行更新数据库的命令
update-database
执行完更新数据库命令后，数据库就被创建出来了。
其中__migrationhistory为迁移历史
主要的工作都完成了，接下来带过一下使用上的一些操作
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using EntityFrameworkBlogHelloMySql.Models;
namespace EntityFrameworkBlogHelloMySql
{
class Program
{
static void Main(string[] args)
{
using (CodeDbContext codeDbContext=new CodeDbContext())
{
//以下是采用5态方式来删除数据，将EF对象的状态为删除数据的办法，解决每次删除数据都需要查询数据库而导致效率低的问题
//var type = new Models.BookType() { Id = 2 };
//codeDbContext.Entry(type).State = System.Data.Entity.EntityState.Deleted;
//codeDbContext.SaveChanges();
//Console.WriteLine(codeDbContext.Entry(type).State);
//查看生成的Sql语句
codeDbContext.Database.Log = (msg) => { Console.WriteLine(msg); };
var type = new Models.BookType() { Id = 4 };
//保证修改时只修改被修改的属性
codeDbContext.Entry(type).State = System.Data.Entity.EntityState.Deleted;
type.Name = "修改后的名字";
codeDbContext.SaveChanges();
foreach (var item in codeDbContext.BookTypes)
{
Console.WriteLine(item.Name);
}
}
Console.ReadLine();
}
}
}
EntityFramework 中的对象有五个状态：Detached（游离态，脱离态）、UnChange（未改变）、Added（新增）、Deleted（删除）、Modified（被修改）。
无需安装部署，在线快速体验 Byzer
更多推荐
0
0- 0
已为社区贡献1条内容
所有评论(0)