How to bring all items that do not have reference to certain table [SQL]

Question

How to bring all items that do not have reference to certain table [SQL]

Navigation

#1 by (1 votes)

0

I'm using NHibernate to do a sql query in C #, however I have a question.

I have the following structure:

public class Pessoa {
   public long Id {get; set;}
   public string Nome {get; set;}
}

public class Usuario {
   public long Id { //Utiliza o id da pessoa }
   public Pessoa pessoa {get; set;}
}

What I would like to do is get a listing of the people that exist, but they are not linked to the user, ie all people who can become a user.

I'm currently using the following code:

[HttpGet]
public IList<Pessoa> getPessoasSemUsuarios()
{
    var usuarios = Domain.Query(); //Lista de usuários
    var pessoas = pessoaDomain.Query(); //Lista de pessoas

    var pessoasSemUsuarios = new List<Pessoa>();

    pessoas.ToList().ForEach(pessoa =>
    {
        //Adiciona todas as pessoas que não são usuários.
        if (!usuarios.Any(x => x.Pessoa.Id == pessoa.Id))
            pessoasSemUsuarios .Add(pessoa);
    });

    return pessoasSemUsuarios ;
}

Note: I thought of something related to left outer join, but I'm not sure, any ideas?

sql c# nhibernate fluent-nhibernate

asked by anonymous 19.02.2018 / 19:21

1 answer

Query works in MySQL, but not in Laravel onActivityResult in another class

score 1 · Answer 1

You can make a Linq with left join, so it will bring people who are not yet users of your system.

The code snippet would look like this:

pessoaDomain.GroupJoin(usuarioDomain,
                       pessoa => pessoa.Id,
                       usuario => usuario.IdPessoa,
                       (pessoa, usuario) => new
                       {
                            Pessoa = pessoa,
                            Usuario = usuario.DefaultIfEmpty()
                       })
           .Where(w => w.Usuario == null)
           .ToList();