EFCore pgsql Join 查询

1. 安装包

   > Microsoft.EntityFrameworkCore              6.0.35   6.0.35> Microsoft.EntityFrameworkCore.Tools        6.0.35   6.0.35> Npgsql.EntityFrameworkCore.PostgreSQL      6.0.29   6.0.29

2. 定义实体等

using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata.Builders;namespace EFCoreJoinQuery
{public class EFCoreHelperDbContext:DbContext{public EFCoreHelperDbContext(DbContextOptions<EFCoreHelperDbContext> options):base(options){}public DbSet<Student> Students { get; set; }public DbSet<School> Schools { get; set;}public DbSet<StudentHobby> StudentHobbys { get;set; }protected override void OnModelCreating(ModelBuilder modelBuilder){modelBuilder.HasDefaultSchema("ellis"); //因为这里我是将表生成到这个schemabase.OnModelCreating(modelBuilder);modelBuilder.ApplyConfigurationsFromAssembly(this.GetType().Assembly);  }}public class School{public int SchoolId { get; set; }public string Name { get; set; }}public class SchoolConfig : IEntityTypeConfiguration<School>{public void Configure(EntityTypeBuilder<School> builder){builder.ToTable("school");builder.HasKey(x => x.SchoolId);builder.Property(x => x.Name).IsRequired().HasColumnName("name");builder.Property(x=>x.SchoolId).HasColumnName("school_id").ValueGeneratedOnAdd();}}public class Student{public int StudentId { get; set;}public string Name { get; set; }public int SchoolId { get; set; }}public class StudentConfig : IEntityTypeConfiguration<Student>{public void Configure(EntityTypeBuilder<Student> builder){builder.ToTable("student");builder.HasKey(x => x.StudentId);builder.Property(x => x.Name).IsRequired().HasColumnName("name");builder.Property(x => x.SchoolId).HasColumnName("school_id");builder.Property(x => x.StudentId).HasColumnName("student_id").ValueGeneratedOnAdd();}}public class StudentHobby{public int HobbyId { get; set; }public string Name { get; set; }public int StudentId { get; set; }}public class StudentHobbyConfig : IEntityTypeConfiguration<StudentHobby>{public void Configure(EntityTypeBuilder<StudentHobby> builder){builder.ToTable("student_hobby");builder.HasKey(x => x.HobbyId);builder.Property(x=>x.Name).IsRequired().HasColumnName("name");builder.Property(x => x.StudentId).HasColumnName("student_id");builder.Property(x => x.HobbyId).HasColumnName("hobby_id").ValueGeneratedOnAdd();}}
}

3. DI

这里需要说明的是需要配置MigrationsHistoryTable，因为我这里是将表迁移到ellis这个schema，所以迁移的历史表也需要创建到这个schema中

builder.Services.AddDbContext<EFCoreHelperDbContext>((sp, options) =>
{options.UseNpgsql("Host=192.168.214.133;Port=32222;Database=postgresdb;Username=postgresadmin;Password=admin123;SearchPath=ellis;",x=>x.MigrationsHistoryTable(HistoryRepository.DefaultTableName,"ellis")).LogTo(Console.WriteLine, new[] { DbLoggerCategory.Database.Command.Name }, LogLevel.Information).EnableSensitiveDataLogging();
});

4. 迁移

add-migration ellis
update-databse ellis

5. controller使用

using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;namespace EFCoreJoinQuery.Controllers
{[Route("api/[controller]/[action]")][ApiController]public class JoinController : ControllerBase{private readonly EFCoreHelperDbContext _eFCoreHelperDbContext;public JoinController(EFCoreHelperDbContext eFCoreHelperDbContext){_eFCoreHelperDbContext = eFCoreHelperDbContext;}//两张表[HttpGet]public async  Task<IActionResult> Query2Join(){// 单列Joinvar value = from s in _eFCoreHelperDbContext.Schoolsjoin st in _eFCoreHelperDbContext.Studentson s.SchoolId equals st.SchoolIdselect new { SchoolName = s.Name, StudentName = st.Name};//多列Join//on        new { m1.field1, m1.field2 }//      equals new { m2.field1, m2.field2 }return Ok(value);}//三张表[HttpGet]public async Task<IActionResult> Query3Join(){var value = from s in _eFCoreHelperDbContext.Schoolsjoin st in _eFCoreHelperDbContext.Studentson s.SchoolId equals st.SchoolIdjoin h in _eFCoreHelperDbContext.StudentHobbyson st.StudentId equals  h.StudentId//因为hobby是多个，这里需要分组一次group h by new { schollName = s.Name,studentName = st.Name } into groupedselect new{SchoolName = grouped.Key.schollName,StudentName = grouped.Key.studentName,HobbyNames = string.Join(", ", grouped.Select(g => g.Name))};return Ok(value);}}
}

https://www.tektutorialshub.com/entity-framework-core/join-query-in-ef-core/