Create a Join between two classes in my repository

Question

I have a repository for my class PessoaCadastro that relates to the class Pessoa. I need to create a method GetJoinAll(...) show me all the table records PessoaCadastro that are related to Pessoa through the field of relationship PessoaId.

Summarizing: "Get all the records that are of the type People". It would be, more or less, like the SQL below.

I don’t know very well from Latin and lambda...

SELECT PC.Id, PC.PessoaTipo, PC.PessoaId, PC.FilialId, P.PessoaNatureza PC.DataInclusao 
FROM PessoaCadastro AS PC 
JOIN Pessoa AS P ON PC.PessoaId = p.PessoaId 
WHERE PC.PessoaTipo = 1

My Repository

using Microsoft.EntityFrameworkCore;
using SistemaComercial.Domain.Interfaces;
using SistemaComercial.Infra.Data.Context;
using System;
using System.Linq;

namespace SistemaComercial.Infra.Data.Repository
{
    public class Repository<TEntity> : IRepository<TEntity> where TEntity : class
    {
        protected readonly SistemaComercialContext Db;
        protected readonly DbSet<TEntity> DbSet;

        public Repository(SistemaComercialContext context)
        {
            Db = context;
            DbSet = Db.Set<TEntity>();
        }

        public virtual void Add(TEntity obj)
        {
            DbSet.Add(obj);
        }

        public virtual TEntity GetById(int id)
        {
            return DbSet.Find(id);
        }

        public virtual IQueryable<TEntity> GetAll()
        {
            return DbSet;
        }

        public virtual void Update(TEntity obj)
        {
            DbSet.Update(obj);
        }

        public virtual void Remove(int id)
        {
            DbSet.Remove(DbSet.Find(id));
        }

        public int SaveChanges()
        {
            return Db.SaveChanges();
        }

        public void Dispose()
        {
            Db.Dispose();
            GC.SuppressFinalize(this);
        }
    }
}

Repository of PessoaCadastro where I need to implement the function GetJoinAll(...):

using SistemaComercial.Domain.Interfaces;
using SistemaComercial.Domain.Models;
using SistemaComercial.Infra.Data.Context;
using System.Collections.Generic;

namespace SistemaComercial.Infra.Data.Repository
{
    public class PessoaCadastroRepository : Repository<PessoaCadastro>, IPessoaCadastroRepository
    {
        public PessoaCadastroRepository(SistemaComercialContext context)
            :base(context)
        {

        }

        public IEnumerable<PessoaCadastro> GetJoinAll()
        {
            return DbSet.Include...
        }
    }
}

Answer 1

Follow an example using Linq:

 var result = from x in PessoaCadastro
                     join y in Pessoas on new { X1 = x.PessoaId } equals new { X1 = y.PessoaId  }
                     where x.PessoaTipo == 1
                     select y;
        return result.ToList();

Comment if it worked for you

Answer 2

var lista = Entity<PessoaCadastro>()
            .GetAll()
            .Select(a=>a.Pessoas)
            .Where(a=> a.PessoaTipo == 1 && a.PessoaId == "seuID")
            .ToList();

Or if it’s a list of Ids:

var lista = Entity<PessoaCadastro>()
            .GetAll()
            .Select(a=> a.Pessoas)
            .Where(a=> a.PessoaTipo == 1 && listaIDS.Contains(a.PessoaId))
            .ToList();