LinqtoSQL 按语法进行筛选和排序

Question

My Techie Bretheren（当然还有Sisteren！），

我有一个具有以下实体的 LinqToSql 数据模型：数据模型 http://danimal.acsysinteractive.com/images/advisor.jpg

我需要检索特定办公室的所有顾问，按他们在办公室内的顺序排序。我的第一部分使用联接：

public static List<Advisor>GetOfficeEmployees(int OfficeID)
{
    List<Advisor> lstAdvisors = null;
    using (AdvisorDataModelDataContext _context = new AdvisorDataModelDataContext())
    {
        var advisors = from adv in _context.Advisors
                       join advisoroffice in _context.OfficeAdvisors
                           on adv.AdvisorId equals advisoroffice.AdvisorId
                       where advisoroffice.OfficeId == OfficeID
                       select adv;
        lstAdvisors = advisors.ToList();
    }
    return lstAdvisors;
}

但是，我似乎无法将疲惫的大脑包裹在逐句顺序上。谁能给出一些建议？

Answer 1

from adv in _context.Advisors
where adv.OfficeAdvisor.Any(off => off.OfficeId == officeID)
order adv by adv.OfficeAdvisor.First(off => off.OfficeId = officeID).Sequence
select adv;

Answer 2

public static List<Advisor>GetOfficeEmployees(int OfficeID)
{
    List<Advisor> lstAdvisors = null;
    using (AdvisorDataModelDataContext _context = new AdvisorDataModelDataContext())
    {
        var advisors = from adv in _context.Advisors
                       join advisoroffice in _context.OfficeAdvisors
                           on adv.AdvisorId equals advisoroffice.AdvisorId
                       where advisoroffice.OfficeId == OfficeID
                       group adv by adv.OfficeId into g
                       order by g.Sequence
                       select g;
        lstAdvisors = advisors.ToList();
    }
    return lstAdvisors;
}

---

注意：我目前无法在Visual Studio上对此进行测试，但应该可以工作。

Answer 3

您可以像这样添加顺序依据子句：

var advisors = from adv in _context.Advisors
                  join advisoroffice in _context.OfficeAdvisors
               on adv.AdvisorId equals advisoroffice.AdvisorId
               where advisoroffice.OfficeId == OfficeID
               orderby advisoroffice.Sequence //  < -----
               select adv;