Stored Procedures in LINQ-to-SQL

Question

I have a stored procedure in a SQL Server database that returns a list of results. This stored procedure is displayed in the dbml LINQ to SQL file. Then I try to call this stored procedure as such:

public List<MyObject> GetObjects() { List<MyObject> objects = new List<MyObject>(); using (DatabaseDataContext context = new DatabaseDataContext()) { objects = context.GetObjectsFromDB(); // This is my problem line } return objects; } 

My problem: I do not know how to convert the results of the stored procedure to List<MyObject> . context.GetObjectsFromDB returns a System.Data.Linq.ISingleResult<sprocName> . How to convert the result of a stored procedure to my List of strong predefined type?

Thanks!

Answer 1

Try it,

 public List<MyObject> GetObjects() { using (DatabaseDataContext context = new DatabaseDataContext()) { var objects = context.GetObjectsFromDB(); return new List<MyObject>(objects); } } 

Updated: Using explicit casting, this can be done as follows:

 public List<MyObject> GetObjects() { using (DatabaseDataContext context = new DatabaseDataContext()) { List<MyObject> objects = (List<MyObject>)context.GetObjectsFromDB(); return objects; } } 

Answer 2

ISingleResult<T> inherited from IEnumerable<T> . As long as "T" represents MyObject, you should be able to iterate through the sequence. If "T" is a different type, I would put the constructor in MyObject, which takes the DB type and creates the MyObject object from it.

Have you tried resetting the breakpoint after calling SPROC to see what the debugger says about the object you are returning?

Answer 3

The Enumerable class also has a ToList list function, which I usually use for this. http://msdn.microsoft.com/en-us/library/bb342261.aspx

Also, when using Linq to Sql, I always check the result for null. If I expect a list, make sure that the number is greater than zero before converting to a list.

 public List<MyObject> GetObjects() { List<MyObject> objects = null; // no need to "new" here using (DatabaseDataContext context = new DatabaseDataContext()) { var tmp = context.GetObjectsFromDB(); if (tmp != null) { if (tmp.Count() > 0) { objects = (List<MyObject>)tmp.ToList(); } } } return objects; } 

Similarly, if you expect only one result, use

 myObject = (MyObject)tmp.ToSingle(); 

Finally, you may need to wrap this function in a try-catch block and catch a SqlException and handle the errors accordingly.

I only mention additional error handling due to application development experience that may crash if you don't have additional error handling code!

Answer 4

I know this late, but ....

From LINQ perlook, SP (without using output for a single result), DataSets will be returned, so to create a list you need to specify the returned field from SP:

 objects = context.GetObjectsFromDB().Select(x => x.MyObject); 

i.e. name of the field returned by SP, e.g.

 objects = context.GetObjectsFromDB().Select(x => x.Names); 

Answer 5

I had the same problem!

My solution was to redo the storage procedure to replace the temporary table with table variables

DOESN'T AUTO MAP spAA_Result:

 CREATE PROCEDURE spAA AS CREATE TABLE #TABLETMP (ID INT, NAME varchar(50)) ... SELECT * FROM #TABLETMP 

AUTO MAP CORRECT CLASS spBB_Result:

 CREATE PROCEDURE spBB AS DECLARE @TABLETMP AS TABLE (ID INT, NAME varchar(50)) ... SELECT * FROM @TABLETMP 

Answer 6

Yes, that will work as follows:

 List<string> listOfStrings = dbContext.spMyStoredProc().Select(x => x.Value).ToList<string>(); 

or

 List<int> listOfInts = dbContext.spMyStoredProc().Select(x => x.Value).ToList<int>();