Linq Group By clause

Question

I'm triying to reproduce the next sql command:

SELECT
    p.*,
    ec.*
FROM ut_pto_Persona_m p
     INNER JOIN (
        SELECT
            id_persona,
            MAX(fecha_insert) maxfecha_insert
        FROM ut_pto_PuertaPersonaVisita_m
        WHERE puertaId IN (1039, 1040, 1041, 1042, 1043, 1044, 1045)
        GROUP BY id_persona) b
                ON p.id_persona = b.id_persona
     INNER JOIN ut_pto_sexo_m s ON p.id_sexo = s.id_sexo
     LEFT JOIN ut_pto_EmpresaContratista_m ec ON ec.id = p.id_empresa
WHERE p.id_persona NOT IN (SELECT id_persona FROM ut_pto_Transacciones)

in a linq query using query syntax..

so far this is what i have done:

var personaIds = (from p in context.ut_pto_Transacciones
                  select p.id_persona).ToList();

            var visitas = (from pv in context.ut_pto_PuertaPersonaVisita_ms.AsEnumerable()
                           where pv.fecha_insert != null && pv.id_persona != null && (pv.puertaId == 1039 || pv.puertaId == 1040 || pv.puertaId == 1041 || pv.puertaId == 1042 || pv.puertaId == 1043 || pv.puertaId == 1044 || pv.puertaId == 1045)
                           group pv by pv.id_persona.Value into g
                           let maxFechaInsert = g.Select(z => z.fecha_insert).Max()
                           let p2 = g.First(z => z.fecha_insert == maxFechaInsert)
                           select new Visita
                           {
                               idPersona = p2.id_persona.Value,
                               fechaInicio = p2.FechaInicio,
                               fechaTermino = p2.FechaTermino
                           });

            var transacciones = (from p in context.ut_pto_Persona_ms
                                join v in visitas
                                on p.id_persona equals v.idPersona
                                join s in context.ut_pto_sexo_ms
                                on p.id_sexo equals s.id_sexo
                                join e in context.EmpresaContratistas
                                on p.id_empresa equals e.Id
                                where !personaIds.Contains(p.id_persona)
                                select new PersonaGeneraDTO{
                                    Empresa = e.Nombre,
                                    rut_persona = p.rut_persona,
                                    dig_verificador = p.dig_verificador,
                                    nombres = p.nombres,
                                    apellido_paterno = p.apellido_paterno,
                                    apellido_materno = p.apellido_materno,
                                    fecha_nacimiento = p.fecha_nacimiento,
                                    FechaInicio = (DateTime)v.fechaInicio,
                                    FechaTermino = (DateTime)v.fechaTermino,
                                    sexo = s.desc_sexo.Substring(0, 1),
                                    tarjeta = p.rut_persona,
                                    rut_empresa = e.Rut,
                                    dv_empresa = e.Dv,
                                    ficha = p.rut_persona.ToString(),
                                    id_persona = p.id_persona
                                }).ToList();

but, I'm receiving an error response that I can not find a solution until now:

System.InvalidOperationException: The LINQ expression 'DbSet() .Join( inner: __p_0, outerKeySelector: p => p.id_persona, innerKeySelector: v => v.idPersona, resultSelector: (p, v) => new { p = p, v = v })' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to'AsEnumerable','AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

I would really appreciate any help you can give me.