How can I map the result of a stored procedure to a user class using linq-to-sql?

Question

I have a stored procedure that returns a result set (4 columns xn rows). The data is based on several tables in my database and contains a summary for each department in the corporate environment. Here is an example:

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

I am using linq-to-sql to retrieve data from my database (nb - use sproc since this is what I inherited). I would call the above sproc and map into the 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 my sproc onto the linq-to-sql constructor method panel. When I examine designer.cs , the return type for this sproc is defined as:

 ISingleResult<usp_GetDepartmentSummaryResult> 

What I would like to do is change this so that it returns a Department type so that I can pass the results of 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 tried directly modifying designer.cs and dbml xml file , but with limited success. I admit that I am a little behind my depth when it comes to updating these files directly, and I'm not sure if this is the best practice? It would be nice to get some recommendations.

thanks a lot

Answer 1

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

after that, drag sproc from the server explorer to this class, and the method for sproc will return the result as a collection of this type.

Answer 2

I tried this and it works, but it seems that it does not work if SP uses temporary tables.