Linq List comparing and exclusion

Question

I have two IQueryable<> lists. Say one is filled with objects of Class Bottle and the other is of type Cup.

Both classes have a Guid called DrinkID.

How would I take my Bottle list and find all the items in the Cup list that have a DrinkID that is NOT in the Bottle list?

I am looking for something like this:

var bottles = _context.AllBottles;
var cups = _context.AllCups.Where(cup => cup.DrinkID not in bottles.DrinkID);

I am using Linq to Entities.

Answer 1

cups.Where(c => !bottles.Any(b => c.DrinkID == b.DrinkID) )

Answer 2

var bottles = //....
var cups = // ....

bottleLookup = bottles.ToLookup(b => b.DrinkId);
var cupsNotAvailable = cups.Where(c => !bottleLookup.ContainsKey(c.DrinkId);

Answer 3

First, define an IHasDrinkID interface (choose a better name), and have both classes implement it.

The define a class which implements 'IEqualityComparer' (say DrinkComparer).

Then it's just

var bottleless =  Cups.Except(Bottles, new DrinkComparer());

Answer 4

// pull out the ids from the bottles first to prevent re-evaluating for every comparison
var bottle_drink_ids = BottleList.Select(b => b.DrinkID).ToList();
var cups_not_in_bottles_list = CupList.Where(c => !bottle_drink_ids.Contains(c.DrinkID));

Answer 5

I recommend revisiting the design, and ensuring that the Entity mapping matches the hierarchy you really want to have.

From the wording of your example, I'm infering that all Bottles are Cups, but you are looking for a simple way of retrieving non-Bottle Cups. The fact that you need a collection of base objects that are not a particular type of derived object raises a red flag for the design.

If possible, clean up the design a bit: make Cups an abstract base type, and define other concrete type(s) derived from Cup to fill the gap. Say, Flask.

At that point, it's easy to have the EF mirror the hierarcy (using table-per-concrete-type mapping). And instead of saying you've got Bottles and non-Bottles, you've got Bottles and Flasks (and other types as necessary), with the Cups abstraction still available as the base class.