join list with linq-to-sql query

Question

I have list of MyObject that looks like this:

public class MyObject{
  public int FruitID {get;set;}
  public string FruitName {get;set;}
}

List<MyObject> TheList = new List<MyObject>();

This list is populated with a linq-to-sql query. I'm looking to create a join between this list and a table that contains FruitID as its foreign key.

The table HarvestTimes looks like this:

FruitID   |   HarvestDatetime  |   RipeFactor
   3      |        3/4/2011    |       2
   3      |        4/5/2011    |       4
   3      |        5/5/2011    |       3
   4      |        3/21/2011   |       2
   4      |        4/10/2011   |       2
   4      |        5/10/2011   |       2

This is what I have so far:

var TheQuery = (from list in TheList
                join fruit in MyDC.HarvestTimes on
                list.FruitID equals fruit.FruitID
                where ....
                select new MyObject{... }).ToList();

I'm have some trouble with the Where clause. How do I get only the Fruit where the RipeFactor was always 2. For instance, Fruit 3 has a RipeFactor of 2 but also has 4 and whereas only Fruit4 has only 2s. I tried with Contains but both fruits come up.

Thanks for your suggestions.

Answer 1

Assuming there is a Relationship between the tables HaverstTime and Fruit:

var TheQuery = MyDC.HarvestTimes
    .Where(p => TheList.Select(q => q.FruitID).Contains(p.FruitID))
    .GroupBy(p => p.Fruit)
    .Where(p => p.All(q => q.RipeFactor == 2))
    .Select(p => p.Key);

This will create a IEnumerable<Fruit> which I think can be easily converted to MyObject.

Update: Oops I forgot to add TheList.Select(q => q.FruitID). That's why it didn't compile. Sorry =)

Update2: Do the same, considering Ripefactor = 2 and 3

var TheQuery = MyDC.HarvestTimes
    .Where(p => TheList.Select(q => q.FruitID).Contains(p.FruitID))
    .GroupBy(p => p.Fruit)
    .Where(p => p.All(q => q.RipeFactor == 2 || q.RipeFactor == 3))
    .Select(p => p.Key);

Answer 2

I think this would work

var fruit = (from list in TheList
             join fruit in
               (from fr in MyDc.HarvestTimes
                group fr by fr.FruitID into fg
                where !fg.Any(f => f.RipeFactor != 2)
                select fg)
             on list.FruitID equals fruit.Key
             select new MyObject{... }).ToList();

Update - If you only want to return the distinct list of FruitIDs you need to select fg.Key instead of fg

var fruit = (from list in TheList
             join fruit in
               (from fr in MyDc.HarvestTimes
                group fr by fr.FruitID into fg
                where !fg.Any(f => f.RipeFactor != 2)
                select fg.Key)
              on list.FruitID equals fruit
              select new MyObject{... }).ToList();