具有两个 select 和 where 条件的 Linq 语句

Question

我有这个 linq to SQL，我只想用连接药物返回一种疾病。但这所做的是返回所有疾病并且仅填充满足 where 条件的药物。我是否需要先选择药物，然后选择疾病才能发挥作用？    public List<Model.Disease> GetDiseaseById(Guid DiseaseId)    {       var Disease = (from Diseases in db.Diseases                        join dr in db.DrugDiseases on  equals d.DrugId on dr.DrugId                         join rdDiseases on rdDiseases.Diseases equals rd.DiseaseId on dr.DiseaseId                      where (aDrug.DiseaseId == DiseaseId)                      select new Model.Disease                     {                       DiseaseId = rdDiseases.DiseaseId,                       DiseaseName = rdDiseases.DiseaseName,                       drug = (from d in db.Drug                                join dr1 in db.DrugDiseases on  equals d.DrugId on dr1.DrugId                                 join rd1Diseases on rdDiseases.Diseases equals rd.DiseaseId on dr1.DiseaseId                               where (Diseases.DiseaseId == d.DiseaseId && d.DiseaseId == DiseaseId)                               select new drug                            {                              DrugId = d.drug,                              DrugName = d.drugName}).OrderBy(m => m.DrugName).ToList(),                   }).OrderBy(d => d.DiseaseName).ToList().FirstOrDefault();       return Disease;                            }

Answer 1

既然您只寻找一种疾病，为什么要返回一个列表呢？下面的代码应该可以工作：

public Model.Disease GetDiseaseById(Guid DiseaseId)

{

   return (from Disease in db.Diseases

               where Disease.DiseaseId == DiseaseId

               orderby Disease.DiseaseName

               join d in db.Drug on Disease.DiseaseId equals d.DiseasesId

               select new Model.Disease

               {

                   DiseaseId = Disease.DiseaseId

                   DiseaseName = Disease.DiseaseName

                   drug = new { DrugId = d.DrugId, DrugName = d.DrugName }

               }).FirstOrDefault();

}