LINQ with JOIN and Where clause, how to do?

Question

Good afternoon.

I’m trying to make a left outer join (SQL) with LINQ, however, I’m not getting.

The code of my class, is this one below. Can anyone give me a light on how I solve this?

public class PessoaController : Controller
{
    // GET: Pessoa
    public ActionResult Index(string _nome )
    {
        Entities banco = new Entities();

        List<PESSOASFJ> listapessoa = (from pes in banco.PESSOASFJ
            join nf in banco.NOTAFISCALCAPA on pes.IDPESSOAFJ equals nf.PESSOASFJ into GrupoNF
            from b in GrupoNF
            Where(p => p.Contains(_nome.ToUpper())) select pes).ToList();

        return View(listapessoa);
    }
}

Answer 1

To perform LEFT JOIN using Linq you need to use the Instruction Defaultifempty.

The instruction Defaultifempty is used to avoid errors in empty collections replacing by a default value that can be assigned or not according to your need.

Therefore, this instruction meets your need where you can assign the Defaultifempty instruction in the entity where you want to perform LEFT JOIN. ;)

Follow an example below on dotnetfiddle:

https://dotnetfiddle.net/5nhMzK

Answer 2

using(Entities banco = new Entities())
{

    List<PESSOASFJ> listapessoa = (from pes in banco.PESSOASFJ
        join nf in banco.NOTAFISCALCAPA on pes.IDPESSOAFJ equals nf.PESSOASFJ into GrupoNF
        from b in GrupoNF.DefaultIfEmpty()
        Where(p => p.Contains(_nome.ToUpper())) 
        select pes
        ).ToList();

    return View(listapessoa);
}

See more details here.

Answer 3

As has already been said in another reply, to carry out the LEFT JOIN you will need to use the Instruction Defaultifempty.

Below is the code snippet:

public class PessoaController : Controller
{
   public ActionResult Index(string _nome )
   {
     Entities banco = new Entities();
     List<PESSOASFJ> listapessoa = (
       from pes in banco.PESSOASFJ.Where(p => p.Contains(_nome.ToUpper()))
       from nf in banco.NOTAFISCALCAPA.Where(o => o.PESSOASFJ == pes.IDPESSOAFJ).DefaultIfEmpty()
       select pes ).ToList();

     return View(listapessoa);
   }
}