Left Join with LINQ

Question

I have two tables that come from different repositories. One comes from an API and I have one that comes from BD.

I already have the two tables mapped to classes.

I want to select all records from table A that are not in table B.

Using a foreach would be something like :

var result = new List<A>();

foreach (var item  in A)
{
    if (!B.Any(x => x.codigo == item.codigo))
    {
      result.Add(item );
    }
}

I tried to use LINQ, but I was unsuccessful.

Follows the code used:

 var result = from a in ListA
            join b in ListB 
                on a.Codigo equals b.Codigo
                into tmpMapp
                     from m  in tmpMapp.DefaultIfEmpty()
            select a;

In SQL would be

SELECT a LEFT JOIN b on a.Codigo = b.Codigo

Answer 1

Lambda would be something like that:

 var result = ListA.Where(x => !ListB.Any(y => y.Codigo == x.Codigo));

Answer 2

I would do it this way

   var result= from a in ListA
               join b in ListB on a.Codigo equals b.Codigo into tmpMapp
               from m  in tmpMapp.DefaultIfEmpty()   
               select new { X = a, Y = m }

More information can be obtained through the official microsoft website here

Answer 3

If the lists are the same you can use Except.

Example:

var result = ListA.Except(ListB);

or

var result = ListA.Where(x => !ListB.Exists(y => y.Codigo == x.Codigo));

In some performance test the . Exists works better than the . Any

I hope it helps.