Stuck on a subquery that is grouping, in Linq`

Question

Hi folks,

I have some Linq code and it's working fine. It's a query that has a subquery in the Where clause. This subquery is doing a groupby. Works great.

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

Frst, here's the code. After that, I'll expplain what piece of data i'm wanting 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've changed some field and tables names to protect the innocent. :)

See this last line :-

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

I wish to include in that the ReviewCount (from the subquery). I'm jus not sure how.

To help understand the problem, this is what the data looks like.

Sub Query

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

Parent Query

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

So the subquery grabs the actual data i need. The parent query determines the correct product, based on the subquery filters.

EDIT 1: Schema

tblProducts

• IdProductCode
• FullName
• ProductFirstName

tblReviews (each product has zero to many reviews)

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

So i'm trying to find the top 3 products a person has done reviews on.

The linq works perfectly... except i just don't know how to include the COUNT in the parent query (ie. pull that result from the subquery).

Cheers :)

Answer 1

While I don't understand LINQ completely, but wouldn't the JOIN work?
I know my answer doesn't help but it looks like you need a JOIN with the inner table(?).

Answer 2

I agree with shahkalpesh, both about the schema and the join.

You should be able to refactor...

 r => r.IdProductCode_Alpha == a.IdProductCode_Alpha  && 
      r.IdProductCode_Beta == a.IdProductCode_Beta  &&
      r.IdProductCode_Gamma == a.IdProductCode_Gamma

into an inner join with tblProducts.

Answer 3

Got it myself. Take note of the double from at the start of the query, then the Any() being replaced by a Where() clause.

var results = (from a in db.tblProducts
               from g in (
                  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)
           Where(g.IdProductCode_Alpha== a.IdProductCode_Alpha&& 
               g.IdProductCode_Beta== a.IdProductCode_Beta&&
               g.IdProductCode_Gamma== a.IdProductCode_Gamma)
           where a.ProductFirstName == ""
           select new {a.IdProduct, a.FullName, g.ReviewCount}).ToList();