How can I map stored procedure result into a custom class with linq-to-sql?

Question

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

Answer 1

create the class by hand in the linq2sql builder, so the class is generated as part of the linq2sql data context. name the class Department, add each of the 4 properties with the names you want. in each of the properties' properties, you need to set the type (the .net type), and the Server data type (varchar(100), int) and the source (the name of the field returned by the sproc.)

once that's done, drag the sproc from server explorer onto that class, and the method for the sproc will return a result as a collection of that type.

Answer 2

I tried this and it does work, however it seems it doesn't work if the SP makes use of temporary tables.