Exe net6.0 all runtime; build; native; contentfiles; analyzers; buildtransitive all runtime; build; native; contentfiles; analyzers; buildtransitive PreserveNewest PreserveNewest PreserveNewest
DbContext的配置
using Microsoft.CodeAnalysis;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.Logging;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.IO;
using System.Reflection;
using System.Text;
namespace EFCoreOneToMany
{
public class MyDbContext : DbContext
{
//显示输出SQL日志
//Microsoft.Extensions.Logging.Debug Logging.Console
private ILoggerFactory loggerFactory = LoggerFactory.Create(b=>b.AddConsole());
public DbSet Articles { get; set; } //API中的名字
public DbSet Comments { get; set; }
//通过json 读取数据库连接字符串
//依赖于 Microsoft.Extensions.Configuration; Microsoft.Extensions.Configuration.Json
private ConfigurationBuilder cfgBuilder = new ConfigurationBuilder();
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
base.OnConfiguring(optionsBuilder);
//Microsoft.Extensions.Configuration.JsonConfigurationExtensions
cfgBuilder.AddJsonFile("ConnString.json", optional: true, reloadOnChange: true);
IConfigurationRoot configRoot = cfgBuilder.Build();
//字符串不能有空格
string connString = configRoot.GetSection("DbContext:MySQLConnectionString").Value;
optionsBuilder.UseMySql(connString, new MySqlServerVersion(new Version(5, 7, 35)));
//optionsBuilder.UseMySql("server=192.168.85.102; database=OneToMany; uid=root; pwd=123456");
//显示输出SQL日志
optionsBuilder.UseLoggerFactory(loggerFactory); //旧
// .net5, .ne6
//optionsBuilder.LogTo(msg =>
//{
// Console.WriteLine(msg);
//});
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
//加载初始数据
var iniArticleData = File.ReadAllText(Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location) + @"/Database/iniArticle.json");
IList article = JsonConvert.DeserializeObject>(iniArticleData);
var iniCommentData = File.ReadAllText(Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location) + @"/Database/iniComment.json");
IList comment = JsonConvert.DeserializeObject>(iniCommentData);
modelBuilder.Entity().HasData(article);
modelBuilder.Entity().HasData(comment);
base.OnModelCreating(modelBuilder);
//从当前程序集加载所有的IntityTypeConfiguration 反射
modelBuilder.ApplyConfigurationsFromAssembly(this.GetType().Assembly);
//.net 6.0
//modelBuilder.Configurations.AddFromAssembly(typeof(MyDbContext).Assembly);
}
}
//VS终端下
// 视图-其他窗口-程序包控制台
//选择默认项目
//add-migration initialMigration //创建数据迁移
//add-migration initialMigration1 //创建数据迁移
// update-database
//Remove-migration 删除最后一次脚本
//Script-Migration 显示迁移的sql脚本
//DBfirst
// Scaffold-DbContext "server=192.168.207.107; database=Demon1; uid=root; pwd=123456" Pomelo.EntityFrameworkCore.MySql
//根据已有数据库创建数据模型。在 NuGet 的程序包管理(Package Manager)控制台中(PowerShell)执行命令：
//Scaffold-DbContext "server=数据库服务器;uid=数据库用户名;pwd=数据库密码;database=数据库名;" Pomelo.EntityFrameworkCore.MySql -OutputDir Data -Force
//.Net Core CLi：dotnet ef dbcontext scaffold "server=数据库服务器;uid=数据库用户名;pwd=数据库密码;database=数据库名;" Pomelo.EntityFrameworkCore.MySql -o Data -f
//CMD 命令下 安装EF工具:
//dotnet tool install --global dotnet-ef
//数据迁移:
//dotnet ef migrations add DataSeeding
//数据更新:
//dotnet ef database update
//P56
}
using System;
using System.Collections.Generic;
using System.Text;
namespace EFCoreOneToMany
{
public class Article
{
public long Id { get; set; }
public string Title { get; set; }
public string Message { get; set; }
public List Comments { get; set; } = new List();
}
}
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata.Builders;
using System;
using System.Collections.Generic;
using System.Text;
namespace EFCoreOneToMany
{
public class ArticleConfig : IEntityTypeConfiguration
{
public void Configure(EntityTypeBuilder builder)
{
builder.ToTable("Aticle"); // "Aticle" 表名
builder.HasKey(a => a.Id); //设置主键
builder.Property(a => a.Title).HasMaxLength(100).IsUnicode();
builder.Property(a => a.Message).IsUnicode();
//在此处配也可以
//builder.HasMany(a=>a.Comments)
// .WithOne(c=>c.Article)
// .HasForeignKey(c=>c.ArticleId);
}
}
}
namespace EFCoreOneToMany
{
public class Comment
{
public long Id { get; set; }
public Article Article { get; set; }
public long ArticleId { get; set; } //外键
public string Message { get; set; }
}
}
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata.Builders;
using System;
using System.Collections.Generic;
using System.Text;
namespace EFCoreOneToMany
{
public class CommentConfig : IEntityTypeConfiguration
{
public void Configure(EntityTypeBuilder builder)
{
builder.ToTable("Comment");
builder.HasKey(a => a.Id); //设置主键
builder.Property(a => a.Message).IsUnicode();
//builder.HasOne(c => c.TheArticle).WithMany(a => a.Comments);
//EFcore会自动在Comment中建立一个 TheArticle.Id的属性,是外键
//在多的实体类中创建
//如果指定了外键， 也需要在此声明
builder.HasOne(c => c.Article)
.WithMany(a => a.Comments)
.HasForeignKey(c=>c.ArticleId);
}
}
}
运行:
using Microsoft.EntityFrameworkCore;
using System;
using System.Linq;
namespace EFCoreOneToMany
{
class Program
{
static void Main(string[] args)
{
插入外键实验 一对多的插入
//using (MyDbContext context = new MyDbContext())
//{
// Article a1 = new Article()
// {
// Title = "蜡笔小新",
// Message = "这个蜡笔小新动画好看吗",
// };
// //虽然在CommentConfig里指定了
// //builder.HasOne(c => c.TheArticle).WithMany(a => a.Comments).IsRequired();
// //外键关系不为0 , 但是用此种方法可以不填写Comment中的ArticleId
// Comment c1 = new Comment();
// c1.Message = "蜡笔小新不好看";
// Comment c2 = new Comment();
// c2.Message = "蜡笔小新好看";
// a1.Comments.Add(c1);
// a1.Comments.Add(c2);
// //只需要加入了a1, comment的2个评论自动加入conments表中, 俗称顺杆爬
// context.Articles.Add(a1);
// context.SaveChanges();
//}
using (MyDbContext dbContext = new MyDbContext())
{
//Single返回Article
//不使用Include时候
Article article = dbContext.Articles.Single(a => a.Id == 2); //查询Articles的Id==2 的Article
Console.WriteLine(article.Title);
//返回为空
foreach (var item in article.Comments)
{
Console.WriteLine("=======1=======");
Console.WriteLine(item.Message);
Console.WriteLine(item.Id);
} //即使有外键也是空的
Article art = dbContext.Articles.Include(a => a.Comments).Single(a => a.Id == 2);
foreach (var item in art.Comments)
{
Console.WriteLine("=======2=======");
Console.WriteLine(item.Message);
Console.WriteLine(item.Id);
}
//select选择实体中的部分数据,返回 IQueryable,这样就可以不用Include了, 返回的也是Comments,类型为Comment
var art2 = dbContext.Articles.Where(a => a.Message.Contains("三国演义")).Select(a=>a.Comments);
var com1 = dbContext.Comments.Where(c => c.Message.Contains("三国演义")).Select(c => c.Article); //IQueryable
//因为使用的是Contains, 默认返回的结果是 IQueryable>,所以要两次循环
foreach (var item in art2)
{
foreach (var item1 in item)
{
Console.WriteLine("=======3=======");
Console.WriteLine(item1.Message);
Console.WriteLine(item1.Id);
}
}
//因为Article是一对多的"一", 所以返回的是IQueryable,一次循环即可
foreach (var item in com1)
{
Console.WriteLine("=======4=======");
Console.WriteLine(item.Message);
Console.WriteLine(item.Id);
}
var art3 = dbContext.Articles.Where(a => a.Id == 3).Select(a => a.Comments);
foreach (var item in art3)
{
foreach (var item1 in item)
{
Console.WriteLine("=======5=======");
Console.WriteLine(item1.Message);
Console.WriteLine(item1.Id);
}
}
//加了FirstOrDefault() 就是单体的Comment 了
var art4 = dbContext.Articles.Select(a => a.Comments).FirstOrDefault();
foreach (var item in art4)
{
Console.WriteLine("=======6=======");
Console.WriteLine(item.Message);
Console.WriteLine(item.Id);
}
//此外,Selcet除了返回实体中的部分数据, 还可以避免查询select *，可指定查询合适的字段
var cmt = dbContext.Comments
.Select(c => new { Id = c.Id, AId = c.Article.Id,Art = c.Article.Message }).Single(c => c.Id == 2);
Console.WriteLine("=======7=======");
Console.WriteLine($"{cmt.Id}+{cmt.AId}+{cmt.Art}");
var cmt1 = dbContext.Comments
.Select(c => new { Id = c.Id, AId = c.Article.Id, Art = c.Article.Message }).FirstOrDefault(c => c.Id == 2);
Console.WriteLine("=======8=======");
Console.WriteLine($"{cmt1.Id}+{cmt1.AId}+{cmt1.Art}");
var cmt2 = dbContext.Comments
.Select(c => new { Id = c.Id, AId = c.Article.Id, Art = c.Article.Message }).Where(c => c.Id == 2).FirstOrDefault();
Console.WriteLine("=======9=======");
Console.WriteLine($"{cmt2.Id}+{cmt2.AId}+{cmt2.Art}");
}
}
}
}
结果:
info: Microsoft.EntityFrameworkCore.Infrastructure[10403]
Entity Framework Core 6.0.3 initialized 'MyDbContext' using provider 'Pomelo.EntityFrameworkCore.MySql:6.0.1' with options: ServerVersion 5.7.35-mysql
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (29ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT `a`.`Id`, `a`.`Message`, `a`.`Title`
FROM `Aticle` AS `a`
WHERe `a`.`Id` = 2
LIMIT 2
水浒传
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (2ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECt `t`.`Id`, `t`.`Message`, `t`.`Title`, `c`.`Id`, `c`.`ArticleId`, `c`.`Message`
FROM (
SELECt `a`.`Id`, `a`.`Message`, `a`.`Title`
FROM `Aticle` AS `a`
WHERe `a`.`Id` = 2
LIMIT 2
) AS `t`
LEFT JOIN `Comment` AS `c` ON `t`.`Id` = `c`.`ArticleId`
ORDER BY `t`.`Id`
=======2=======
水浒传评论1
4
=======2=======
水浒传评论2
5
=======2=======
水浒传评论3
6
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECt `a`.`Id`, `c`.`Id`, `c`.`ArticleId`, `c`.`Message`
FROM `Aticle` AS `a`
LEFT JOIN `Comment` AS `c` ON `a`.`Id` = `c`.`ArticleId`
WHERe `a`.`Message` LIKE '%三国演义%'
ORDER BY `a`.`Id`
=======3=======
三国演义好看1
1
=======3=======
三国演义好看2
2
=======3=======
三国演义好看3
3
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECt `a`.`Id`, `a`.`Message`, `a`.`Title`
FROM `Comment` AS `c`
INNER JOIN `Aticle` AS `a` ON `c`.`ArticleId` = `a`.`Id`
WHERe `c`.`Message` LIKE '%三国演义%'
=======4=======
三国演义的故事
1
=======4=======
三国演义的故事
1
=======4=======
三国演义的故事
1
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECt `a`.`Id`, `c`.`Id`, `c`.`ArticleId`, `c`.`Message`
FROM `Aticle` AS `a`
LEFT JOIN `Comment` AS `c` ON `a`.`Id` = `c`.`ArticleId`
WHERe `a`.`Id` = 3
ORDER BY `a`.`Id`
=======5=======
魔方大厦评论1
7
=======5=======
魔方大厦评论2
8
warn: Microsoft.EntityFrameworkCore.Query[10103]
The query uses the 'First'/'FirstOrDefault' operator without 'OrderBy' and filter operators. This may lead to unpredictable results.
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (1ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECt `t`.`Id`, `c`.`Id`, `c`.`ArticleId`, `c`.`Message`
FROM (
SELECt `a`.`Id`
FROM `Aticle` AS `a`
LIMIT 1
) AS `t`
LEFT JOIN `Comment` AS `c` ON `t`.`Id` = `c`.`ArticleId`
ORDER BY `t`.`Id`
=======6=======
三国演义好看1
1
=======6=======
三国演义好看2
2
=======6=======
三国演义好看3
3
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECt `c`.`Id`, `a`.`Id` AS `AId`, `a`.`Message` AS `Art`
FROM `Comment` AS `c`
INNER JOIN `Aticle` AS `a` ON `c`.`ArticleId` = `a`.`Id`
WHERe `c`.`Id` = 2
LIMIT 2
=======7=======
2+1+三国演义的故事
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (2ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECt `c`.`Id`, `a`.`Id` AS `AId`, `a`.`Message` AS `Art`
FROM `Comment` AS `c`
INNER JOIN `Aticle` AS `a` ON `c`.`ArticleId` = `a`.`Id`
WHERe `c`.`Id` = 2
LIMIT 1
=======8=======
2+1+三国演义的故事
=======9=======
2+1+三国演义的故事
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (1ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECt `c`.`Id`, `a`.`Id` AS `AId`, `a`.`Message` AS `Art`
FROM `Comment` AS `c`
INNER JOIN `Aticle` AS `a` ON `c`.`ArticleId` = `a`.`Id`
WHERe `c`.`Id` = 2
LIMIT 1
D:CsharpEFCoreTest1EFCoreTestEFCoreOneToManybinDebugnet6.0EFCoreOneToMany.exe (进程 2372)已退出，代码为 0。
要在调试停止时自动关闭控制台，请启用“工具”->“选项”->“调试”->“调试停止时自动关闭控制台”。
按任意键关闭此窗口. . .
配置文件:
{
"DbContext": {
"ConnectionString": "server=localhost; Database=FakeXiechengDb; User Id=sa; Password=PAssword12!;",
"MySQLConnectionString": "server=192.168.153.101; database=OneToMany; uid=root; pwd=123456;"
}
}
