卡在Linq中分组的子查询中 [英] Stuck on a subquery that is grouping, in Linq`

问题描述

我有一些Linq代码，并且工作正常.这是一个在Where子句中具有子查询的查询.此子查询正在执行groupby.效果很好.

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

请参阅最后一行:-

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

我希望在其中包括ReviewCount(来自子查询).我不确定如何.

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.

IdProductCode_Alpha = 1，IdProductCode_Beta = 2，IdProductCode_Gamma = 3，ReviewCount = 10 ...第2行... ...第3行...

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

IdProduct = 69，FullName ='Jon Skeet的神奇香脂'

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.

tblProducts

tblProducts

• IdProductCode
• 全名
• ProductFirstName

tblReviews(每个产品的评论数为零到很多)

tblReviews (each product has zero to many reviews)

• IdProduct
• IdProductCode_Alpha(可以为空)
• IdProductCode_Beta(可以为空)
• IdProductCode_Gamma(可以为空)
• IdPerson

因此，我正在尝试寻找一个人进行过评论的前3种产品.

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

linq完美运行...除了我只是不知道如何在父查询中包括COUNT(即从子查询中提取结果).

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

干杯:)

推荐答案

自己搞定.请在查询开始时注意双精度from，然后将Any()替换为Where()子句.

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

这篇关于卡在Linq中分组的子查询中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！