c# - Grouping with Linq only when all elements in a group have value in a column -

i try create groupings of lastreadings, can create client’s , competitor’s prices. try below code, approach not eliminate readings client’s. below table need eliminate readingsid 5 , 6, products c , d there no match , pass further comparable ones.

readingid   productid     distributor   price 1                       competitor    8.0 2                       client        8.1 3           b             competitor    8.3 4           b             client        8.4 5           c             client        8.8 6           d             client        8.9 

below far:

    private ienumerable<pricecomparison> getpricecomparisons(string competitor)     {         ienumerable<igrouping<string, latestreading>> groupingsbyproductid =                             latestreading in latestreadings                             group latestreading latestreading.productid;           ienumerable<pricecomparison> pricecomparisons             = grouping in groupingsbyproductid               select new pricecomparison               {                   productid = grouping.key,                   myprice = (from latestreading in grouping                              latestreading.distributor == client                              select latestreading.price).firstordefault(),                   competitorprice = (from latestrading in grouping                                      latestrading.distributor == competitor                                      select latestrading.price).firstordefault()               };         return pricecomparisons;     } 

actually write post, concluded additional created groupings "no competitor", competitor price there 0, later can eliminate such groupings , code works. somehow approach of creating "empty" groupings not feel right, there better way focus on groupings excluding products c , d?

you can this:

//...  ienumerable<pricecomparison> pricecomparisons =               grouping in groupingsbyproductid               grouping.any(p => p.distributor == client)                   && grouping.any(p => p.distributor == competitor)               select new pricecomparison               {                  //..               }; 

this makes sure groups have price both client , competitor.