如何像平面表一样进行分组 linq 查询

Question

你好 我想为这样的表创建一个查询： 1.我有一个多个问题，我想计算对多个问题结果的每个答案的每个响应的数量，并在格式如下的查询中显示它们： 1.ID 2.问题短语 3.q1 4.q2 5.q3 6.q4 7.count1 8.count2 9.count3 10.count4 我已经创建了一个像这样的 linq 查询，它来自于答案表，之后我可以将它们与 ID 连接到问题表并获取短语和其他：

var q4 = (from x in LinqDB.PTAs
             where x.PTID == int.Parse(DropDownListPeriodID.SelectedValue) &&
                   x.PTUser.PTUserID >= ID1 && x.PTUser.PTUserID <= ID2 
             group x by x.PTQID into GRPA //& x.PTAID
             select new {
                 GRPA.Key,
                 A1=(
                     from f in GRPA
                     group f by f.Answer into FG
                     select FG.Count()
                    )});

但它给出了我不想要的第二层，因为格式 我尝试了这个：

var Q = from x in LinqDB.PTAs
            where x.PTID == int.Parse(DropDownListPeriodID.SelectedValue) &&
                  x.PTUser.PTUserID >= ID1 && x.PTUser.PTUserID <= ID2 
            group x by new { x.PTQID, x.Answer } into gr
            select new {gr.Key.PTQID,gr.Key.Answer,A1=gr.Count() };

那么对此有何建议，或者可能更改后处理中的第二个查询，以便它可以像我的格式一样？ 谢谢您的回答。

Answer 1

  //process user input before going into the database.
int selectedID = int.Parse(DropDownListPeriodID.SelectedValue);

  //go to the database and get the data.
List<PTA> records =
(
  from x in LinqDB.PTAs
  where x.PTID == selectedID
    && ID1 <= x.PTUser.PTUserID && x.PTUser.PTUserID <= ID2
  select x
).ToList();

 // group up the data for presentation.    
var questions =
  from x in records
  group x by new {x.PTQID, x.Answer} into g1
  group g1 by g1.Key.PTQID into g2
  select new
  {
    PTQID = g2.Key,
    Answers =
       from y in g2
       select new
       {
         Answer = y.Key.Answer,
         AnswerCount = y.Count()
       }
  };

---

    // group up the data for presentation.
   var questions =
   from x in records
   group x by x.PTQID into g
   let answers = g.GroupBy(y => y.Answer)
   let answer1 = answers.First()
   let answer2 = answers.Skip(1).First()
   let answer3 = answers.Skip(2).First()
   let answer4 = answers.Skip(3).First()
   select new
   {
     PTQID = g.Key,
     Answer1 = answer1.Key,
     Answer1Count = answer1.Count(),
     Answer2 = answer1.Key,
     Answer2Count = answer1.Count(),
     Answer3 = answer1.Key,
     Answer3Count = answer1.Count(),
     Answer4 = answer1.Key,
     Answer4Count = answer1.Count(),
   };