I have a stored procedure that returns a result set (4 columns x n Rows). The data is based on multiple tables within my database and provides a summary for each department within a corporate. Here is sample:

usp_GetDepartmentSummary

DeptName      EmployeeCount      Male      Female
HR            12                 5         7
etc...

I am using linq-to-sql to retrieve data from my database (nb - have to use sproc as it is something I have inherited). I would like to call the above sproc and map into a department class:

public class Department
{
     public string DeptName {get; set;}
     public int EmployeeCount {get; set;}
     public int MaleCount {get; set;}
     public int FemaleCount {get; set;}
}

In VS2008, I can drag and drop my sproc onto the methods pane of the linq-to-sql designer. When I examine the designer.cs the return type for this sproc is defined as:

ISingleResult<usp_GetDepartmentSummaryResult>

What I would like to do is amend this somehow so that it returns a Department type so that I can pass the results of the sproc as a strongly typed view:

<% foreach (var dept in Model) { %>
<ul>
    <li class="deptname"><%= dept.DeptName %></li>
    <li class="deptname"><%= dept.EmployeeCount %></li>
    etc...

Any ideas how to achieve this?

NB - I have tried amending the designer.cs and dbml xml file directly but with limited success. I admit to being a little out of my depth when it comes to updating those files directly and I am not sure it is best practice? Would be good to get some diretion.

Thanks much