Creating a comeback With a sublist

Question

I am trying to write a query in Latin to solve the following problem:

I have a Student List that I have student data like Id, Name, Enrollment, Cpf...

In another entity I have the notes where the Alunoid, Materiaid, Note...

I want a return where the entity returns:

Name,
Enrollment,
Cpf,
Notes <- Banklist

And that can make filters in the set how to return students who had grades below 7.

In Entity I rode

var query = contexto.Aluno
            .Include(i => i.Notas)
            .Select( s => new { s.Nome, s.Matricula, s.Cpf, s.Notas})
            .AsQueryable();

// O que estou tentando fazer algo como:
**query = query.Where(q => q.Notas.Where(qq => qq.Nota < 7))**  

If I bring the notes out of the list I can perform the filter, but the name, license plate, Cpf repeat. Now this way you are select I could not perform the filter.

var query = from aluno in contexto.Aluno
            join nota in contexto.Nota  on aluno.Id equals nota.AlunoId
            where (nota.Nota < 7)
            select new { aluno.Nome, aluno.Matricula, aluno.Cpf, **s.Notas** }

Is there any way?

Answer 1

Remembering that, inside your model, you need to have a property of a list for this your object (Note), something like:

public List<Nota> Notas { get; set; }

Try it like this:

var query = (from aluno in contexto.Aluno
            join nota in contexto.Nota on aluno.Id equals nota.AlunoId
            where (nota.Nota < 7)
            select new { aluno, nota }).ToList();

return query.Select(x=>x.aluno);