views:

313

answers:

3
A: 
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;
David B
This seems like a strange way to do joins with those Any() and First() calls. Even if it works correctly, it is probably creating unnecesary sub-queries in the SQL translation. Why not use a "join" clause, or two "from" clauses with a "where" clause tying them together?
Lucas
OO brain wants to work with the Advisor object and its properties. Data brain wants to join it all together and sort out the type at the end.
David B
A: 
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;
}


Note: I am not able to currently test this on Visual Studio but should work.

smink
There is no need to group the results. In fact, the result of this LINQ query will be an IEnumerable<IGrouping<TSequence, Advisor>>, so this will not compile.
Lucas
A: 

You can add an order by clause like this:

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;
Lucas