.NetCore3.1中, EFcore 实现 数据迁移, DbFirst 一对多查询 连接字符串 配置

打开cmd

dotnet publish -r win10-x64 /p:PublishSingleFile=true

生成exe

首先引入包

<Project Sdk="Microsoft.NET.Sdk">

  <PropertyGroup>
    <OutputType>Exe</OutputType>
    <TargetFramework>netcoreapp3.1</TargetFramework>
  </PropertyGroup>

	<ItemGroup>
		<PackageReference Include="Microsoft.CodeAnalysis.Common" Version="4.1.0" />
		<PackageReference Include="Microsoft.EntityFrameworkCore" Version="3.1.22" />
		<PackageReference Include="Microsoft.EntityFrameworkCore.Abstractions" Version="3.1.22" />
		<PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="3.1.22">
			<PrivateAssets>all</PrivateAssets>
			<IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
		</PackageReference>
		<PackageReference Include="Microsoft.EntityFrameworkCore.Relational" Version="3.1.22" />
		<PackageReference Include="Microsoft.EntityFrameworkCore.Tools" Version="3.1.22">
			<PrivateAssets>all</PrivateAssets>
			<IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
		</PackageReference>
		<PackageReference Include="Microsoft.Extensions.Configuration" Version="6.0.1" />
		<PackageReference Include="Microsoft.Extensions.Configuration.Json" Version="6.0.0" />
		<PackageReference Include="Microsoft.Extensions.Logging.Console" Version="6.0.0" />
		<PackageReference Include="Microsoft.Extensions.Logging.Debug" Version="6.0.0" />
		<PackageReference Include="Newtonsoft.Json" Version="13.0.1" />
		<PackageReference Include="Pomelo.EntityFrameworkCore.MySql" Version="3.2.7" />
	</ItemGroup>

	<ItemGroup>
	  <None Update="ConnString.json">
	    <CopyToOutputDirectory>PreserveNewest</CopyToOutputDirectory>
	  </None>
	</ItemGroup>

</Project>

DotNet6配置

<Project Sdk="Microsoft.NET.Sdk">

  <PropertyGroup>
    <OutputType>Exe</OutputType>
    <TargetFramework>net6.0</TargetFramework>
    <ImplicitUsings>enable</ImplicitUsings>
    <Nullable>enable</Nullable>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="Microsoft.EntityFrameworkCore" Version="6.0.3" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Abstractions" Version="6.0.3" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Relational" Version="6.0.3" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Tools" Version="6.0.3">
      <PrivateAssets>all</PrivateAssets>
      <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
    </PackageReference>
    <PackageReference Include="Microsoft.Extensions.Configuration" Version="6.0.1" />
    <PackageReference Include="Microsoft.Extensions.Configuration.Binder" Version="6.0.0" />
    <PackageReference Include="Microsoft.Extensions.Configuration.Json" Version="6.0.0" />
    <PackageReference Include="Newtonsoft.Json" Version="13.0.1" />
    <PackageReference Include="Pomelo.EntityFrameworkCore.MySql" Version="6.0.1" />
  </ItemGroup>

  <ItemGroup>
    <None Update="config.json">
      <CopyToOutputDirectory>PreserveNewest</CopyToOutputDirectory>
    </None>
  </ItemGroup>

</Project>

连接字符串文件:

{
  "DbContext": {
    "ConnectionString": "server=localhost; Database=FakeXiechengDb; User Id=sa; Password=PAssword12!;",
    "MySQLConnectionString": "server=192.168.85.102; database=OneToMany; uid=root; pwd=123456;"
  }
}

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft": "Warning",
      "Microsoft.Hosting.Lifetime": "Information"
    }
  },
  "AllowedHosts": "*",
  "DbContext": {
    "ConnectionString": "server=localhost; Database=FakeXiechengDb; User Id=sa; Password=PAssword12!;",
    "MySQLConnectionString": "server=localhost; database=FakeXiechengDb; uid=root; pwd=123456;"
  },
  "Authentication": {
    "SecretKey": "suibianStringsuibianString",
    "Issuer": "zgWebTestzgWebTest",
    "Audience": "zgWebTestzgWebTest"
  }
}

建立实体类

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<Comment> Comments { get; set; } = new List<Comment>();

    }
}

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; }
    }
}

使用//Fluent API 配置实体类, 这样有利于解耦

using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata.Builders;
using System;
using System.Collections.Generic;
using System.Text;

namespace EFCoreOneToMany
{
    public class ArticleConfig : IEntityTypeConfiguration<Article>
    {
        public void Configure(EntityTypeBuilder<Article> 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<Comment>(a=>a.Comments)
            //    .WithOne(c=>c.Article)
            //    .HasForeignKey(c=>c.ArticleId);
        }
    }
}

using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata.Builders;
using System;
using System.Collections.Generic;
using System.Text;

namespace EFCoreOneToMany
{
    public class CommentConfig : IEntityTypeConfiguration<Comment>
    {
        public void Configure(EntityTypeBuilder<Comment> builder)
        {
            builder.ToTable("Comment");
            builder.HasKey(a => a.Id); //设置主键
            builder.Property(a => a.Message).IsUnicode();
            //builder.HasOne<Article>(c => c.TheArticle).WithMany(a => a.Comments);
            //EFcore会自动在Comment中建立一个 TheArticle.Id的属性,是外键
            //在多的实体类中创建
            //如果指定了外键， 也需要在此声明
            builder.HasOne<Article>(c => c.Article)
                .WithMany(a => a.Comments)
                .HasForeignKey(c=>c.ArticleId);
        }
    }
}

建立DBContext

using Microsoft.CodeAnalysis;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.Logging;
using Microsoft.Extensions.Logging.Debug;
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<Article> Articles { get; set; } //API中的名字
        public DbSet<Comment> 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);
            //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> article = JsonConvert.DeserializeObject<IList<Article>>(iniArticleData);

            //var iniCommentData = File.ReadAllText(Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location) + @"/Database/iniComment.json");
            //IList<Comment> comment = JsonConvert.DeserializeObject<IList<Comment>>(iniCommentData);

            modelBuilder.Entity<Article>().HasData(article);
            //modelBuilder.Entity<Comment>().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
    /*
     * cmd命令:
     Mysql数据库:
    docker run --name mysqltest -p 3306:3306 -v /usr/local/mysql/data:/var/lib/mysql -v /usr/local/mysql/conf.d:/etc/mysql/conf.d -e MYSQL_ROOT_PASSWORD=123456 -d mysql

    dotnet ef migrations add MySQLInit
    dotnet ef database update
    dotnet ef database update MySQLUpdate3 回滚 对应版本
     */

    //P56
}

加入一对多

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<Article>(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())
            {
                //一对多的查询 Include(a=>a.Comments)
                Article a = dbContext.Articles.Include(a=>a.Comments).FirstOrDefault(a=>a.Id==3);

                foreach (var item in a.Comments)
                {
                    Console.WriteLine(item.Id.ToString()+item.Message);
                }

                //由评论查询对应的Article 1对1的查询
                Comment cmt = dbContext.Comments.Include(c=>c.Article).FirstOrDefault(c=>c.Id==3);
                Console.WriteLine("------------由评论查询对应的Article 1对1的查询--------------");
                Console.WriteLine(cmt.Message);
                Console.WriteLine(cmt.Article.Id + cmt.Article.Message);

                //无和有外键的查询
                Console.WriteLine("---------------无外键的查询----------------");
                //只 new 了一个匿名类型 取id， ArticleId
                var cmt2 = dbContext.Comments.Select(c=> new { c.Id, c.ArticleId }).Single(c=>c.Id==3);
                Console.WriteLine(cmt2.Id + ""+cmt2.ArticleId);

                Console.WriteLine("---------------有外键的查询----------------");
                var cmt3 = dbContext.Comments.Single(c => c.Id == 3);
                Console.WriteLine(cmt2.Id + " " + cmt2.ArticleId);

                Console.WriteLine(cmt3);
                
            }
            //67节


        }
    }
}

结果:

info: Microsoft.EntityFrameworkCore.Infrastructure[10403]
      Entity Framework Core 3.1.22 initialized 'MyDbContext' using provider 'Pomelo.EntityFrameworkCore.MySql' with options: None
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (29ms) [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` = 3
          LIMIT 1
      ) AS `t`
      LEFT JOIN `Comment` AS `c` ON `t`.`Id` = `c`.`ArticleId`
      ORDER BY `t`.`Id`, `c`.`Id`
7魔方大厦好看
8魔方大厦不好看
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (1ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT `c`.`Id`, `c`.`ArticleId`, `c`.`Message`, `a`.`Id`, `a`.`Message`, `a`.`Title`
      FROM `Comment` AS `c`
      INNER JOIN `Aticle` AS `a` ON `c`.`ArticleId` = `a`.`Id`
      WHERE `c`.`Id` = 3
      LIMIT 1
------------由评论查询对应的Article 1对1的查询--------------
三国演义好看
1三国演义的故事
---------------无外键的查询----------------
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT `c`.`Id`, `c`.`ArticleId`
      FROM `Comment` AS `c`
      WHERE `c`.`Id` = 3
      LIMIT 2
31
---------------有外键的查询----------------
3 1
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (1ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT `c`.`Id`, `c`.`ArticleId`, `c`.`Message`
      FROM `Comment` AS `c`
      WHERE `c`.`Id` = 3
      LIMIT 2
EFCoreOneToMany.Comment

webApi配置

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft": "Warning",
      "Microsoft.Hosting.Lifetime": "Information"
    }
  },
  "AllowedHosts": "*",
  "DbContext": {
    "ConnectionString": "server=localhost; Database=FakeXiechengDb; User Id=sa; Password=sasa;",
    "MySQLConnectionString": "server=localhost; database=FakeXiechengDb; uid=root; pwd=123456;"
  }
}