Tutorial :Stuck on a subquery that is grouping, in Linq`



Question:

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 :)


Solution:1

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();  


Solution:2

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(?).


Solution:3

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.


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »