How can I map stored procedure result into a custom class with linq-to-sql?
- by Remnant
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
