.NET6中, 实现一对多关系数据库的配置和查询

<Project Sdk="Microsoft.NET.Sdk"><PropertyGroup><OutputType>Exe</OutputType><TargetFramework>net6.0</TargetFramework></PropertyGroup><ItemGroup><Pack

罗迪尼亚的熔岩

1642人浏览 · 2022-04-18 12:29:03

罗迪尼亚的熔岩 · 2022-04-18 12:29:03 发布

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

  <PropertyGroup>
    <OutputType>Exe</OutputType>
    <TargetFramework>net6.0</TargetFramework>
  </PropertyGroup>

	<ItemGroup>
		<PackageReference Include="Microsoft.EntityFrameworkCore" Version="6.0.3" />
		<PackageReference Include="Microsoft.EntityFrameworkCore.Abstractions" Version="6.0.3" />
		<PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="6.0.3">
			<PrivateAssets>all</PrivateAssets>
			<IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
		</PackageReference>
		<PackageReference Include="Microsoft.EntityFrameworkCore.Relational" Version="6.0.3" />
		<PackageReference Include="Microsoft.EntityFrameworkCore.Sqlite" 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="Microsoft.Extensions.Logging.Console" Version="6.0.0" />
		<PackageReference Include="Microsoft.Extensions.Options" Version="6.0.0" />
		<PackageReference Include="Newtonsoft.Json" Version="13.0.1" />
		<PackageReference Include="NModbus4.NetCore" Version="2.0.1" />
		<PackageReference Include="Pomelo.EntityFrameworkCore.MySql" Version="6.0.1" />
	</ItemGroup>

	<ItemGroup>
	  <None Update="ConnString.json">
	    <CopyToOutputDirectory>PreserveNewest</CopyToOutputDirectory>
	  </None>
	  <None Update="Ini\iniArticle.json">
	    <CopyToOutputDirectory>PreserveNewest</CopyToOutputDirectory>
	  </None>
	  <None Update="Ini\iniComment.json">
	    <CopyToOutputDirectory>PreserveNewest</CopyToOutputDirectory>
	  </None>
	</ItemGroup>

</Project>

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<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, 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> 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 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>();

    }
}

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

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

运行:

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())
            {
                //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<List<Comment>>,所以要两次循环
                foreach (var item in art2)
                {
                    foreach (var item1 in item)
                    {
                        Console.WriteLine("=======3=======");
                        Console.WriteLine(item1.Message);
                        Console.WriteLine(item1.Id);
                    }
                }
                //因为Article是一对多的"一", 所以返回的是IQueryable<Article>,一次循环即可
                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:\Csharp\EFCoreTest1\EFCoreTest\EFCoreOneToMany\bin\Debug\net6.0\EFCoreOneToMany.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;"
  }
}