具有Linq、内部Join、Group By、Order By Method语法的实体框架

Question

我有一个SQL查询：

SELECT 
    COUNT(PID.pid) AS Counted,
    (SELECT TOP 1 Collect_id 
     FROM PID_Control  
     WHERE pid_controlY.org_pid = PID_Control.pid) as Collect_id 
FROM 
    PID 
INNER JOIN  
    PID_Control AS pid_controlY ON PID.pid = pid_controlY.pid 
GROUP BY 
    pid_controlY.Collect_id, pid_controlY.org_pid   

我想在Linq中写同样的内容，但我不确定如何处理这个语句：

,(select top 1 Collect_id from PID_Control where pid_controlY.org_pid = 
PID_Control.pid) as Collect_id

我已经这样做了，？？？=线路错误

var list = db.PID_Control
            .Include(t => t.PID1)
            .GroupBy(k => new { k.Collect_id })
            .Select(c => new Grouped
                {
                    Collect_id = ???
                    Counted = c.Select(q => q.PID1.pid1).Count(),
                })
               .ToList();

如何在林克制作？

类

public partial class PID_Control
{
    public int pid_control_id { get; set; }
    public Nullable<int> pid { get; set; }
    public Nullable<int> Collect_id { get; set; }    
    public virtual PID PID1 { get; set; }
}

public partial class PID
{
    public PID()
    {
        this.PID_Control = new HashSet<PID_Control>();
    }
    public int pid1 { get; set; }    
    public virtual ICollection<PID_Control> PID_Control { get; set; }
}

Answer 1

我不知道你想要实现什么，但只看数据就可以了：

from pc in db.PID_Controls
group pc by new { pc.Collect_id, pc.pid } into pcgrp
select new Grouped
{
    Collect_Id = pcgrp.SelectMany(pc => pc.PID.PID_Controls)
                      .Select(pc2 => pc2.Collect_id)
                      .FirstOrDefault(),
    Counted = pcgrp.Count()
}

顺便说一句，帮自己一个忙，为集合使用复数名称（遵守其他C#命名约定也不会有什么害处）。