LINQ with JOIN and where clause, how to do?

Question

LINQ with JOIN and where clause, how to do?

Navigation

#1 by (1 votes)
#2 by (1 votes)
#3 by (1 votes)

3

Good afternoon.

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

The code for my class, this is below. Can anyone give me a light on how to 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);
    }
}

c# entity-framework linq asp.net-mvc

asked by anonymous 01.01.2018 / 17:46

3 answers

Controller needs to be a class? LINQ using function within select new {} with lambda expression

score 1 · Answer 1

To perform LEFT JOIN using Linq you need to use the DefaultIfEmpty statement.

The DefaultIfEmpty statement is used to avoid errors in collections by replacing them with a default value that can be assigned or not according to your need.

So this statement meets your need where you can assign the DefaultIfEmpty statement to the entity where you want to perform LEFT JOIN. ;)

Take an example below of dotnetfiddle:

link

score 1 · 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 .

score 1 · Answer 3

As stated in another answer, to perform LEFT JOIN you will need to use the DefaultIfEmpty .

The following is the code snippet below:

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);
   }
}