Using Linq to get a single column rowset from a stored procedure, How can I clean up this hack job?

Question

I've got a guy who is a wizard at writing stored procs and he can munch 10 tables down to a single column in no time at all. I'm half tempted to invest a couple of days to work with him on returning XML instead of rowsets because I can digest those all day long without any problem. I say that because it is a challenge to get a result of a rowset in that I can easily send down the line with WCF. Here's the hack that I've done to make this work...

public static List<int> GetListOfManagersForRegional(int empId)
    {
        CMSDataContext cms = new CMSDataContext();
        List<cmsManagerList> mgrs = cms.GetRegionalsManagers(empId).ToList();
        List<int> managers = new List<int>();
        foreach (cmsManagerList m in mgrs)
        {
            if(m.ManagerId != null) managers.Add((int)m.ManagerId);
        }
        return managers;
    }

cmsManagerList is a type I created in the ORM to make it possible for the stored proc to return it's value to something I could get Linq to digest. What I really would like to do is go straight from the stored proc, which only returns a single column to a List<int>. Anybody know how to do this better?

Answer 1

Try something like this:

public static IEnumerable<int> GetListOfManagersForRegional(int empId) {
 using (var cms = new CMSDataContext()) {
  return
   from m in cms.GetRegionalsManagers(empId)
   where m.ManagerID != null
   select m.ManagerId;    
 }
}