List of connections with linq-to-sql query

Question

I have a list of MyObject objects that look 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 the linq-to-sql query. I am looking to create a connection between this list and a table containing FruitID as its foreign key.

The HarvestTimes table 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 still have:

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

I have problems with the Where clause. How to get only Fruit where RipeFactor is always 2. For example, Fruit 3 has RipeFactor of 2, but also has 4, and only Fruit4 has only 2s. I tried with Contains, but both fruits came up.

Thanks for your suggestions.

Answer 1

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

 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 an IEnumerable<Fruit> , which I think can easily be converted to MyObject.

Update: Unfortunately, I forgot to add TheList.Select (q => q.FruitID). That is why it did not compile. Sorry =)

Update2: Do the same, given that 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 it will 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 want to return only a separate FruitID list, 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();