初始化数据:
using Microsoft.Extensions.Configuration;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace EFCoreDemon1
{
internal class MyDbContext:DbContext
{
public DbSet<Article> Articles { get; set; } //API中的名字
public DbSet<Comment> Comments { get; set; }
public DbSet<Comment2> Comment2s { get; set; }
public DbSet<Customers> Customers { get; set; } //API中的名字
public DbSet<Orders> Orders { get; set; }
private ConfigurationBuilder cfgBuilder = new ConfigurationBuilder();
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
base.OnConfiguring(optionsBuilder);
//Microsoft.Extensions.Configuration.JsonConfigurationExtensions
cfgBuilder.AddJsonFile("DbCfg.json", optional: true, reloadOnChange: true);
IConfigurationRoot configRoot = cfgBuilder.Build();
string connString = configRoot.GetSection("ConnectionStrings:SqliteConnectionString").Value;
optionsBuilder.UseSqlite(connString);
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
base.OnModelCreating(modelBuilder);
modelBuilder.Entity<Customers>()
.HasData
(
new Customers { Id = 1, Name = "Joe" },
new Customers { Id = 2, Name = "Henry" },
new Customers { Id = 3, Name = "Sam" },
new Customers { Id = 4, Name = "Max" }
);
modelBuilder.Entity<Orders>()
.HasData
(
new Orders { Id = 1, Name = "DDD", CustomerId = 3, CustomerId2 = 3 },
new Orders { Id = 2, Name = "EEE", CustomerId = 1, CustomerId2 = 1 },
new Orders { Id = 3, Name = "FFF", CustomerId = 2 },
new Orders { Id = 4, Name = "GGG", CustomerId = 4 }
);
modelBuilder.Entity<Comment2>()
.HasData
(
new Comment2 { Id = 1, Message = "Orders1的评论", OrdersId = 1 },
new Comment2 { Id = 2, Message = "Orders1的评论", OrdersId = 1 },
new Comment2 { Id = 3, Message = "Orders2的评论", OrdersId = 2 },
new Comment2 { Id = 4, Message = "Orders2的评论", OrdersId = 2 },
new Comment2 { Id = 5, Message = "Orders3的评论", OrdersId = 3 },
new Comment2 { Id = 6, Message = "Orders3的评论", OrdersId = 3 },
new Comment2 { Id = 7, Message = "Orders3的评论", OrdersId = 3 },
new Comment2 { Id = 8, Message = "Orders4的评论", OrdersId = 4 }
);
modelBuilder.ApplyConfigurationsFromAssembly(this.GetType().Assembly);
}
}
}
json配置
{
"ConnectionStrings": {
"SqliteConnectionString": "Data Source=D:\\Db\\DbSqlite.db",
"MySQLConnectionString": "server=127.0.0.1; database=OneToMany; uid=root; pwd=123456;"
}
}
结果:
// See https://aka.ms/new-console-template for more information
global using EFCoreDemon1;
global using EFCoreDemon1.Entities;
global using Microsoft.EntityFrameworkCore;
global using Microsoft.EntityFrameworkCore.Metadata.Builders;
using System.Data.Common;
//某网站包含两个表,Customers 表和 Orders 表。编写一个 SQL 查询,找出所有从不订购任何东西的客户。
using MyDbContext myDb = new MyDbContext();
var customers = myDb.Orders.Where(o=>o.CustomerId2 == null).Select(o=>o.Customers);
//第一次Select 通过外键指向集合,从进入Orders另一张表Customers,第二个select 使用select选择指定字段
var name = myDb.Orders.Where(o => o.CustomerId2 == null).Select(o => o.Customers).Select(c => c.Name);
//第一次Select 通过外键指向集合,从Customers进入另一张表Orders,第二个select,从Orders进入表Comment2
var test = myDb.Customers.Where(c => c.Id > 1).Select(c => c.Orders).Where(o => o.Name == "DDD").Select(o => o.Comment2s);
//var a = myDb.Customers.FromSqlInterpolated($"Select * from Orders").ToList();
//myDb.Orders.FromSqlInterpolated($"Select Name From Customers where Customers.Id not in ()")
foreach (var item in customers)
{
Console.WriteLine(item.Name);
}
Console.WriteLine("=======================");
foreach (var item in name)
{
Console.WriteLine(item);
}
Console.WriteLine("=======================");
//最后两个表的关系是一对多,必须两次循环
foreach (var item in test)
{
foreach (var i in item)
{
Console.WriteLine(i.Message);
}
}
/*
结果
Henry
Max
=======================
Henry
Max
=======================
Orders1的评论
Orders1的评论
*/
//写原生 AdoNetCore
Console.WriteLine("=======================");
DbConnection conn = myDb.Database.GetDbConnection();
if (conn.State != System.Data.ConnectionState.Open)
{
await conn.OpenAsync();
}
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = "select Customers.name from Customers where Customers.Id in (select CustomerId2 from Orders)";
using(var reader = await cmd.ExecuteReaderAsync())
{
while (await reader.ReadAsync())
{
var test1 = reader.GetValue(0);
Console.WriteLine(test1);
}
}
}
/*
结果:
Joe
Sam
*/
实体类和配置:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace EFCoreDemon1.Entities
{
public class Customers
{
public int Id { get; set; }
public string Name { get; set; }
public Orders Orders { get; set; }
}
}
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace EFCoreDemon1.Entities
{
public class Orders
{
public int Id { get; set; }
public string Name { get; set; }
public Customers Customers { get; set; }
public int CustomerId { get; set; }
public int? CustomerId2 { get; set; }
public List<Comment2> Comment2s { get; set; }
}
}
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace EFCoreDemon1.Entities
{
public class Comment2
{
public int Id { get; set; }
public Orders Orders { get; set; }
public int OrdersId { get; set; } //外键
public string Message { get; set; }
}
}
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace EFCoreDemon1.Db
{
public class CustomersConfig : IEntityTypeConfiguration<Customers>
{
public void Configure(EntityTypeBuilder<Customers> builder)
{
builder.ToTable("Customers");
builder.HasKey(c => c.Id); //设置主键
}
}
}
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace EFCoreDemon1.Db
{
public class OrdersConfig : IEntityTypeConfiguration<Orders>
{
public void Configure(EntityTypeBuilder<Orders> builder)
{
builder.ToTable("Orders");
builder.HasKey(a => a.Id); //设置主键
builder.HasOne<Customers>(o => o.Customers)
.WithOne(c => c.Orders).HasForeignKey<Orders>(o => o.CustomerId);
}
}
}
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace EFCoreDemon1.Db
{
public class Comment2Config : IEntityTypeConfiguration<Comment2>
{
public void Configure(EntityTypeBuilder<Comment2> builder)
{
builder.ToTable("Comment2");
builder.HasKey(a => a.Id); //设置主键
builder.HasOne<Orders>(c => c.Orders)
.WithMany(o => o.Comment2s)
.HasForeignKey(o => o.OrdersId);
}
}
}
<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.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="DbCfg.json">
<CopyToOutputDirectory>PreserveNewest</CopyToOutputDirectory>
</None>
</ItemGroup>
<ItemGroup>
<Folder Include="Migrations\" />
</ItemGroup>
</Project>