I have Linq code and it works fine. It is a query that has a subquery in the sentence Where

. This subquery is executed by groupby. Works great.

The problem is I don't know how to grab one of the subquery results from the subquery to the parent.

Frst, here's the code. After that, I will talk about which piece of data I want to extract.

    var results = (from a in db.tblProducts
               where (from r in db.tblReviews
                      where r.IdUserModified == 1
                      group r by
                          new
                              {
                                  r.tblAddress.IdProductCode_Alpha,
                                  r.tblAddress.IdProductCode_Beta,
                                  r.tblAddress.IdProductCode_Gamma
                              }
                      into productGroup
                          orderby productGroup.Count() descending
                          select
                          new
                              {
                                  productGroup.Key.IdProductCode_Alpha,
                                  productGroup.Key.IdProductCode_Beta,
                                  productGroup.Key.IdProductCode_Gamma,
                                  ReviewCount = productGroup.Count()
                              }).Take(3)
                   .Any(
                   r =>
                   r.IdProductCode_Alpha== a.IdProductCode_Alpha&& 
                       r.IdProductCode_Beta== a.IdProductCode_Beta&&
                       r.IdProductCode_Gamma== a.IdProductCode_Gamma)
               where a.ProductFirstName == ""
               select new {a.IdProduct, a.FullName}).ToList();

      

        
        
        
      

    

Ok. I changed the field and table names to protect the innocent. :)

See the last line: -

select new {a.IdProduct, a.FullName}).ToList();

      

        
        
        
      

    

I want to include ReviewCount (from a subquery) in this. I do not know how to do that.

To understand the problem, here's what the data looks like.

Sub Query

IdProductCode_Alpha = 1, IdProductCode_Beta = 2, IdProductCode_Gamma = 3, ReviewCount = 10 ... line 2 ... ... line 3 ...

Parent request

IdProduct = 69, FullName = 'Jon Skeet Wonder Balm'

This way the subquery grabs the actual data I need. The parent query identifies the correct product based on subquery filters.

EDIT 1: Schematic

tblProducts

• IdProductCode
• Full name
• ProductFirstName

tblReviews (each product has zero for many reviews)

• IdProduct
• IdProductCode_Alpha (may be null)
• IdProductCode_Beta (may be null)
• IdProductCode_Gamma (may be null)
• IdPerson

So, I'm trying to find the top 3 products that a person has reviewed.

Linq works great ... also, I just don't know how to include COUNT in the parent query (i.e., get that result out of the subquery).

Cheers :)