SQL server 多表联合查询

//返回的数据类型为DataTable，称之为弱类型,DataTable 比较灵活，//如果有更新操作，字段不不定的，比较合适。而不是list<string>public DataTable GetProjectMessage(int projectSupervisionUnit)        {            projectSupervisionUnit = 10001;//上海市卫生管理局，这里我是写的一个死数据用来测试           //这里注意，之前我把foreach包裹using，报了一个bug，           //自己没有仔细去想，using会创建数据库连接，           //如果多次查询多次连接数据库，肯定是不对的。被人看到，笑掉大牙了。            using (EF.MSISEntities context = new EF.MSISEntities())            {                //根据当前的监管单位，查到监管单位下所属的项目，得到项目ids                var item = context.Project_SupervisionUnit.Where(o => o.SupervisionUnit_Id == projectSupervisionUnit).ToList();                context.Database.CommandTimeout = 9999;                DataTable ds = new DataTable();                foreach (var i in item)                {                //这里是想一个sql语句查询出项目名称，项目负责人，创建日期，该项目的病例数，该项目的表单数，                //因此用的子查询，但是注意子查询的结果不能返回多条，负责报错。我这里的子查询根据id来查，因此只有一个结果不会报错。                //另外实际项目中最好起有意思的名字，什么p1,p2显得极为不专业                    var testCommand = new SqlCommand(@"SELECT  DISTINCT  p1.Name, p1.OwnerUserCode, p1.CreateDate,                    (SELECT   COUNT(DISTINCT p3.Patient_Id) AS Patient_IdSUM                     FROM    Project AS p1 INNER JOIN                     PatientGroup AS p2 ON p1.Id = p2.Project_Id INNER JOIN                     PatientInGroup AS p3 ON p2.Id = p3.PatientGroup_Id                     WHERE   (p1.Id  =@id)) AS Patient_IdSUMS,                    (SELECT   COUNT(DISTINCT CRFForm_Id) AS CRFForm_IdSUM                     FROM      CRF_Variable                     WHERE   (Project_Id =@id)) AS CRFForm_IdSUMSFROM      Project AS p1 INNER JOIN                PatientGroup AS p2 ON p1.Id = p2.Project_Id INNER JOIN                PatientInGroup AS p3 ON p2.Id = p3.PatientGroup_IdWHERE   (p1.Id =@id)", (SqlConnection)context.Database.Connection);                testCommand.Parameters.Add("@id", SqlDbType.Int);                 testCommand.Parameters["@id"].Value = i.Project_Id;                //testCommand.Parameters.AddWithValue("@id", );                SqlDataAdapter adapter = new SqlDataAdapter(testCommand);                    adapter.Fill(ds);                }                return ds;            }        }