Select a list of two common related elements in sql with entityFramework and linq

Question

Select a list of two common related elements in sql with entityFramework and linq

Navigation

#1 by (0 votes)
#2 by (0 votes)

0

I have a list of people that are related to a committee table and another table types person, I need to return the list of people of a committee and also return the list of people of a type note: return the people that I return from the relationship with the committee do not have to be filtered by type, just return the list without filter

I have something like this:

   var lp = bd.Instituciones
            .Where(x => x.IdInstitucion == idInstitucion)
            .Select(x => x.Comites.Select(c => c.ComitesPersonas.Select(p => p.Persona)).ToList() && x.Personas.ToList());

sql c# linq entity-framework

asked by Juan C. Martinez 27.07.2018 в 19:29

source

2 answers

How to Save Excel with JFileChooser? Does the CSS serif font change depend on the device?

score 0 · Answer 1

I would do it in several steps, if the LINQ is going to be used with lambda expressions:

var comitesInstitucion = Instituciones
    .Where(ins => ins.IdInstitucion == idInstitucion)
    .SelectMany(ins => ins.Comites);

var personasComite = ComiteInstitucion
    .SelectMany(com => com.ComitesPersonas)
    .Select(comPer => comPer.persona); 

var personasComiteTipo = personasComite
    .Where(per => per.tipo == tipoPersona);

The following types would be assigned for each of the LINQs:

IEnumerable<Comite> comitesInstitución;
IEnumerable<Persona> personasComite;
IEnumerable<Persona> personasComiteTipo;

In peopleComment you would have a list of people from all the committees of an institution, although you can add a Where if you are interested in a specific committee just before SelectMany .

In peopleCommitteeType you would have a list of people of a given type from all the committees of an institution. I've had to invent architecture a bit, I hope it's understood.

Remember that you can delay the evaluation of the enumerables if you do not convert them into a list immediately.

score 0 · Answer 2

The solution was to make two queries of the different lists that I needed, then join them and end up grouping them so that they do not repeat values

 //Lista de personas por tipo diferentes de estudiantes
        List<Persona> personasTipo = bdTiposPersona.GetFilter
            (x => x.IdTipoPersona != 7)
            .SelectMany(x => x.Personas.Where(y => y.IdInstitucion == idInstitucion)).ToList();
        //Todas las personas del comite
        List<Persona> personasComites = bdComitesPersona.GetFilter(x => x.Comites.IdInstitucion == idInstitucion).Select(x => x.Persona).ToList();

        //Unir las dos listas
        List<Persona> listaComunidad = personasTipo.Union(personasComites).GroupBy(x => new {
            x.IdPersona,
            x.IdInstitucion,
            x.Nombres,
            x.Apellidos,
            x.NombreCompleto,
            x.RolUsuario,
            x.NombreUser,
            x.Documento,
            x.UrlFoto,
            x.IdArchivoFoto
        }).Select(p=>new Persona
        {
            IdPersona = p.Key.IdPersona,
            IdInstitucion = p.Key.IdInstitucion,
            Nombres = p.Key.Nombres,
            Apellidos = p.Key.Apellidos,
            RolUsuario = p.Key.RolUsuario,
            NombreUser = p.Key.NombreUser,
            Documento = p.Key.Documento,
            UrlFoto = p.Key.UrlFoto,
            IdArchivoFoto= p.Key.IdArchivoFoto
        }).ToList();