How to get a complement list of objects using linq and EntityFramework

Question

I have two types, one is a cut down of the other:

    public class A
    {
        public int ID { get; set; }

        public string Name { get; set; }
    }

    public class B
    {
        public int ID { get; set; }

        public string Name { get; set; }

        public string Description { get; set; }
    }

I have a List<A> called As that was passed to me by a client and I have an EntityFramework DbContext with a DbSet<B> called Bs. The two types can be matched on ID.

What I want to get is all the As that are not in Bs. How would you write that in Linq? I'm trying to use a join but I can't seem to get my head around it.

In T-SQL I would do something like this:

SELECT A.*
FROM A LEFT JOIN B
    ON A.ID = B.ID
WHERE B.ID is NULL

Answer 1

You can do it like this:

List<A> As = ... //This is a list in memory

//Get all ids of As
var A_ids = As.Select(x => x.ID).ToList();

//Get all ids that are in both As (memory) and TableB (database)
//We put them in a HashSet for performance reasons
var ids_in_tableB_also =
   new HashSet<int>(
       db.TableB
       .Where(x => A_ids.Contains(x.ID))
       .Select(x => x.ID));

//Get the A objects that are in `As` but of which IDs are not in ids_in_tableB_also
//This operation would have been slower if we haven't used the HashSet above
var rest_of_As = As.Where(x => !ids_in_tableB_also.Contains(x.ID)).ToList();