Does C# linq support "anti join" semantics?

Question

I've googled a little while and didn't find a direct anti-join semantics example. How to do this in C# LINQ as an example?

Answer 1

An anti-join as basically a set of data that is not conained in another set, can be represented in Linq with a an IEnumerable.Except like this:

double[] numbers1 = { 2.0, 2.0, 2.1, 2.2, 2.3, 2.3, 2.4, 2.5 };
double[] numbers2 = { 2.2 };

IEnumerable<double> onlyInFirstSet = numbers1.Except(numbers2);

foreach (double number in onlyInFirstSet)
    Console.WriteLine(number);

This of course requires the definition of an IEqualityComparer for custom classes.

An alternative syntax using where would be:

var antiJoin = numbers1.Where(number => !numbers2.Contains(number));

Read more on Enumerable.Except Method on Microsoft docs.

Edit:

As for "db driven linq" here is an example that will work for Entity Framework using Except:

var filteredProducts = db.Products.ToList()
    .Except(db.Orders.Where(o => o.OrderId = 123)
        .Select(o => o.Product).ToList())
    .ToList();

as for the where alternative:

var filterProducts = db.Orders.Where(o => o.OrderId = 123)
    .Select(o => o.Product).ToList();
var antiJoinProducts = db.Products.Where(p => !filterProducts.Contains(p));

Answer 2

Assuming this relates to your previous question -

If you want to include in your query employees for which you couldn't find the department (essentially a left outer join) you can do this:

var result = from e in employees
                 join d in departments on e.DeptId equals d.DeptId into gj
                 from subdept in gj.DefaultIfEmpty()
                 select new { e.EmpName, subdept?.DeptName };

If you want to retrieve only the employees for which you couldn't match a department (that would be your anti join I guess) then you just add a subdept is null contition like so:

var result = from e in employees
                 join d in departments on e.DeptId equals d.DeptId into gj
                 from subdept in gj.DefaultIfEmpty()
                 where subdept is null
                 select new { e.EmpName, subdept?.DeptName };

For more info on left outer joins in C# Linq you can check this out.

Answer 3

I think there is no direct method to achieve that, but it is easy with couple of extension methods.

Setup:

public class Class1
{
  public int Id;
  public string Info;
}

public class Class2
{
  public int Id;
  public string Data;
}

Usage:

List<Class1> l1 = new List<Class1>() { new Class1() { Id = 1, Info = "abc" }, new Class1() { Id = 2, Info = "123" } };
List<Class2> l2 = new List<Class2>() { new Class2() { Id = 2, Data = "dsfg" }, new Class2() { Id = 3, Data = "asdfsaf" } };

l1 = l1.Where(c1 => ! l2.Select(c2 => c2.Id).Contains(c1.Id)).ToList();

Also, if you'd have the same lists of entites/types you could use Except methpod (you would need to define own comparator).