具有两个 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();}