EF Core 兼容多数据库 保姆级教程 SqlServer PostgreSQL SQLite
EF Core 兼容多种数据 包括 SQLServer PostgreSQL SQLite 从包括创建数据库环境，生成代码并调优，最终对三类数据库测试 保姆级教程
·
本文环境 Microsoft Visual Studio 2022、.Net6、SQLServer2019、PostgreSQL 14.4、SQLite 3.5
一、准备数据库环境
SQLServer 建表，按绿色箭头
★如果没有到本链接下载开发版即可，上面这软件是单独装的安装向导里面有（https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads）
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'UserData2Extension') AND type in (N'U'))
DROP TABLE UserData2Extension;
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'UserData2') AND type in (N'U'))
DROP TABLE UserData2;
CREATE TABLE UserData2(
ID int IDENTITY(1,1) PRIMARY KEY ,
LoginName nvarchar(50) UNIQUE,
Password nvarchar(50),
DisplayName nvarchar(50),
CreateDate datetime,
);
insert into UserData2(LoginName,Password,DisplayName,CreateDate) values('admin','pwtest','管理员',getdate());
create table UserData2Extension(
ID int IDENTITY(1,1) PRIMARY KEY,
UserId int ,
Code nvarchar(50),
Value nvarchar(50),
FOREIGN KEY(UserId) REFERENCES UserData2 (ID)
);
insert into UserData2Extension(UserId,Code,Value) values(1,'LastUpdateTime',CONVERT(varchar(100), GETDATE(), 21));
PostgreSQL 建表，按黑色箭头
★如没数据库可点击本链接到官方网站下载200多M，带上面哪个软件（https://www.postgresql.org）
DROP TABLE IF EXISTS "UserData2Extension";
DROP TABLE IF EXISTS "UserData2";
CREATE TABLE IF NOT EXISTS "UserData2"
(
"ID" serial PRIMARY KEY,
"LoginName" character varying(50) UNIQUE,
"Password" character varying(50),
"DisplayName" character varying(50),
"CreateDate" timestamp without time zone
);
INSERT INTO "UserData2"( "LoginName", "Password", "DisplayName", "CreateDate") VALUES ('admin','pwtest','管理员',statement_timestamp());
create table IF NOT EXISTS "UserData2Extension"(
"ID" serial PRIMARY KEY,
"UserId" integer ,
"Code" character varying(50),
"Value" character varying(50),
FOREIGN KEY("UserId") REFERENCES "UserData2"("ID")
);
insert into "UserData2Extension"("UserId","Code","Value") values(1,'LastUpdateTime',statement_timestamp());
SQLite 建表
先创建一个叫 DefaultDb.db 的文件，在你的工程目录里，然后按蓝色箭头执行SQL如下图
上面的 DB Browser for SQLite 软件到管网下载，数据库支持软件和VS都自带（https://sqlitebrowser.org）
DROP TABLE IF EXISTS "UserData2Extension";
DROP TABLE IF EXISTS "UserData2";
CREATE TABLE "UserData2" (
"ID" INTEGER PRIMARY KEY AUTOINCREMENT,
"LoginName" TEXT UNIQUE,
"Password" TEXT,
"DisplayName" TEXT,
"CreateDate" DATETIME
);
insert into UserData2(LoginName,Password,DisplayName,CreateDate) values('admin','pwtest','管理员',DateTime('now','localtime'));
create table "UserData2Extension"(
"ID" INTEGER PRIMARY KEY AUTOINCREMENT,
"UserId" INTEGER ,
"Code" TEXT,
"Value" TEXT,
FOREIGN KEY("UserId") REFERENCES "UserData2"("ID")
);
insert into "UserData2Extension"("UserId","Code","Value") values(1,'LastUpdateTime',DateTime('now','localtime'));
二、准备代码工程环境
新建一个控制台工程，添加包如下，tools是生成代码的工具，其他几个是EF数据库包
Microsoft.EntityFrameworkCore.SqlServer
Npgsql.EntityFrameworkCore.PostgreSQL
Microsoft.EntityFrameworkCore.Sqlite
Microsoft.EntityFrameworkCore.Tools
启动程序包管理控制台
把之前步骤创建的 DefaultDb.db 包括到项目中，设置复制到输出目录
三、生成与处理上下文、实体代码
运行如下命令，生成所有数据库对应的上下文，实体代码
注意：用户名，密码，数据库根据自己的库改
命令代码
Scaffold-DbContext "Data Source=.;Initial Catalog=Demo20220707;Integrated Security=true; pooling=false;" Microsoft.EntityFrameworkCore.SqlServer -OutputDir ModelsSQLServer
Scaffold-DbContext "Host=localhost;Username=postgres;Password=pg2022sql;Database=postgres" Npgsql.EntityFrameworkCore.PostgreSQL -OutputDir ModelsNpgsql
Scaffold-DbContext "Data Source=DefaultDb.db" Microsoft.EntityFrameworkCore.Sqlite -OutputDir ModelsSqlite
这样会得到多个对应不同数据库的上下文，实体代码。如下图
注意：这些代码最终删除，最终我们只用一套上下文与实体。这样容易复制代码优化成一套
先看 xxxContext 的，找差异。
看看 SQLServer的
看看 PostgreSQL的，大约有三类差异
看看 SQLite的。大约有两类差异
总结一下，3个数据库的差异
1）日期类型
2）unique 哪里有些差异，就是索引的名字，如果开发需要就改成统一的否则不需要这参数
3）还有就是postgresql多一行，HasPostgresExtension(“pg_catalog”, “adminpack”); 把这些差异合并成一套，这行可要可不要其实。
其余的都差不多，然后开始处理成一套，处理 OnModelCreating 如下图
再处理 OnConfiguring （其实顺序无所谓先 OnConfiguring 也行）
上下文、实体代码如下
using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Reflection;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Infrastructure;
using Microsoft.EntityFrameworkCore.Metadata;
using Microsoft.EntityFrameworkCore.SqlServer.Infrastructure.Internal;
using Microsoft.EntityFrameworkCore.Storage;
using Microsoft.Extensions.Configuration;
using Npgsql.EntityFrameworkCore.PostgreSQL.Infrastructure;
using Npgsql.EntityFrameworkCore.PostgreSQL.Infrastructure.Internal;
namespace ConsoleApp6.Models
{
public partial class DefaultDbContext : DbContext
{
string _connectionString=string.Empty;
string _providerType = string.Empty;
public DefaultDbContext()
{
//如果在这里 Thorw 可以禁止某些设计时操作如在程序包管理器运行 Script-DbContext 生成脚本
//throw new ApplicationException("禁止 DefaultDbContext");
}
public DefaultDbContext(string connectionString,string providerType)
{
_connectionString = connectionString;
_providerType = providerType;
}
public DefaultDbContext(DbContextOptions<DefaultDbContext> options)
: base(options)
{
}
public virtual DbSet<UserData2> UserData2s { get; set; } = null!;
public virtual DbSet<UserData2Extension> UserData2Extensions { get; set; } = null!;
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
//日志输出到 Console.WriteLine 可以看到代码到SQL转换过程等，测试没问题了关闭即可。
optionsBuilder.LogTo(Console.WriteLine);
if (!optionsBuilder.IsConfigured)
{
if ("Npgsql.EntityFrameworkCore.PostgreSQL" == _providerType)
optionsBuilder.UseNpgsql(_connectionString);
else if("Microsoft.EntityFrameworkCore.SqlServer" == _providerType)
optionsBuilder.UseSqlServer(_connectionString);
else if ("Microsoft.EntityFrameworkCore.Sqlite" == _providerType)
optionsBuilder.UseSqlite(_connectionString);
else
throw new ApplicationException("未知的数据库 Provider");
}
base.OnConfiguring(optionsBuilder);
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
var db = this.Database;
if (db.IsNpgsql())
modelBuilder.HasPostgresExtension("pg_catalog", "adminpack");
modelBuilder.Entity<UserData2>(entity =>
{
entity.ToTable("UserData2");
entity.HasIndex(e => e.LoginName).IsUnique();
entity.Property(e => e.Id).HasColumnName("ID");
if (db.IsNpgsql())
entity.Property(e => e.CreateDate).HasColumnType("timestamp without time zone");
else if (db.IsSqlServer())
entity.Property(e => e.CreateDate).HasColumnType("datetime");
else if (db.IsSqlite())
entity.Property(e => e.CreateDate).HasColumnType("DATETIME");
else
throw new ApplicationException("未知的数据库 Provider");
entity.Property(e => e.DisplayName).HasMaxLength(50);
entity.Property(e => e.LoginName).HasMaxLength(50);
entity.Property(e => e.Password).HasMaxLength(50);
});
modelBuilder.Entity<UserData2Extension>(entity =>
{
entity.ToTable("UserData2Extension");
entity.Property(e => e.Id).HasColumnName("ID");
entity.Property(e => e.Code).HasMaxLength(50);
entity.Property(e => e.Value).HasMaxLength(50);
entity.HasOne(d => d.User)
.WithMany(p => p.UserData2Extensions)
.HasForeignKey(d => d.UserId);
});
OnModelCreatingPartial(modelBuilder);
}
partial void OnModelCreatingPartial(ModelBuilder modelBuilder);
}
}
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
namespace ConsoleApp6.Models
{
public partial class UserData2
{
public UserData2()
{
UserData2Extensions = new HashSet<UserData2Extension>();
}
public int Id { get; set; }
public string? LoginName { get; set; }
public string? Password { get; set; }
public string? DisplayName { get; set; }
public DateTime? CreateDate { get; set; }
public virtual ICollection<UserData2Extension> UserData2Extensions { get; set; }
}
}
using System;
using System.Collections.Generic;
namespace ConsoleApp6.Models
{
public partial class UserData2Extension
{
public int Id { get; set; }
public int? UserId { get; set; }
public string? Code { get; set; }
public string? Value { get; set; }
public virtual UserData2? User { get; set; }
}
}
四、测试代码、执行效果
代码逻辑很简单，就是用不同的数据库链接串与类型，创建不同的上下文然后循环输出，代码如下
using ConsoleApp6.Models;
using Microsoft.EntityFrameworkCore;
using System.Reflection;
namespace ConsoleApp6
{
internal class Program
{
static void Main(string[] args)
{
string[] csTable = {
"Data Source=.;Initial Catalog=Demo20220707;Integrated Security=true; pooling=false;"
, "Host=localhost;Username=postgres;Password=pg2022sql;Database=postgres"
, "Data Source=DefaultDb.db"
};
string[] dbTypeTable ={
"Microsoft.EntityFrameworkCore.SqlServer"
, "Npgsql.EntityFrameworkCore.PostgreSQL"
, "Microsoft.EntityFrameworkCore.Sqlite"
};
for (int i = 0; i < csTable.Length; i++)
{
Program.ExecTest(csTable[i], dbTypeTable[i]);
Console.WriteLine("━━━━━━━━━━━━");
}
}
private static void ExecTest(string constr, string ptype)
{
using var context = new DefaultDbContext(constr
, ptype);
//创建建库脚本
var sql = context.Database.GenerateCreateScript();
Console.WriteLine("数据库类型:{0}", context.Database.ProviderName);
Console.WriteLine("建库脚本");
Console.WriteLine(sql);
//添加数据
var d1 = DateTime.Now;
var rn1 = d1.GetHashCode();
var d2 = DateTime.Now;
var rn2 = d2.GetHashCode();
var adduser1 = new UserData2()
{
LoginName = "usertest" + rn1,
Password = "upsw",
DisplayName = "测试用户",
CreateDate = d1
};
var adduser2 = new UserData2()
{
LoginName = "usertest" + rn2,
Password = "upsw",
DisplayName = "测试用户" + +rn2,
CreateDate = d2
};
context.UserData2s.AddRange(adduser1, adduser2);
var changCount = context.SaveChanges();
Console.WriteLine("Insert自动编号：{0}、{1}", adduser1.Id, adduser2.Id);
//查询数据，且包括子表数据，且生成的sql包括代码路径与行号
var users = from user in context.UserData2s.Include(e=>e.UserData2Extensions).TagWithCallSite()
where user.Id > 1 || user.Id <= adduser2.Id
orderby user.Id descending
select user;
Console.WriteLine("查询数据");
foreach (var u in users)
{
var extCount = u.UserData2Extensions.Count();
Console.WriteLine($"{u.Id}\t{u.LoginName}\t{u.Password}\t{u.DisplayName}\t{u.CreateDate}\t{extCount}");
}
}
}
}
在optionsBuilder.LogTo(Console.WriteLine); 不生效时，可看见建库与数据
optionsBuilder.LogTo(Console.WriteLine); 生效时可以看见调试信息如下
建表脚本
INSERT 语句
SELECT 语句
五、收尾
测试无误把之前生成的多余的类都删了即可
本文完
腾讯云面向开发者汇聚海量精品云计算使用和开发经验，营造开放的云计算技术生态圈。
更多推荐
0
0- 0
已为社区贡献1条内容
所有评论(0)