CODEWITHSUNDEEP
c#sql-serverlinqstored-procedures
user208662
user208662

Reputation: 10997

Stored Procedures in LINQ-to-SQL

I have a stored procedure in a SQL Server database that returns a list of results. This stored procedure is exposed in the LINQ-to-SQL dbml file. I then 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 is, I don't know how to convert the results of the stored procedure to a List<MyObject>. context.GetObjectsFromDB returns a System.Data.Linq.ISingleResult<sprocName>. How do I convert the result of the stored procedure to my List of strong pre-defined type?

Thank you!

Upvotes: 7

Views: 9038

Answers (7)

haddassah S
haddassah S

Reputation: 177

I was returning from the SQL procedure a anonymes type of just a "txt id" , so i used this :

var connection = _dbContext.Database.GetDbConnection();
await connection.OpenAsync();

using var command = connection.CreateCommand();
command.CommandText = "GetIdsFromRange";
command.CommandType = CommandType.StoredProcedure;

var parameter = new SqlParameter("@changeDate", latestUpdateJob.LastUpadate);
command.Parameters.Add(parameter);

var result = new List<ChatDto>();

using var reader = await command.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
    var dto = new ChatDto
    {
        TxtId = reader.GetInt32(reader.GetOrdinal("TxtId"))
        // Add other properties here as needed
    };

    result.Add(dto);
}

await connection.CloseAsync();

return result;

This is a more verbose method, but it bypasses the Set() requirement and gives you more control over reading from the database. Remember to handle exceptions and ensure proper connection management when using this method.This is a more verbose method, but it bypasses the Set() requirement and gives you more control over reading from the database. Remember to handle exceptions and ensure proper connection management when using this method.

Upvotes: 0

Lester
Lester

Reputation: 1

Yes what would work is 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>();

Upvotes: 0

ASama
ASama

Reputation: 31

i had same problem!

My solution was remake store procedure for replace temporal table by 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

Upvotes: 0

Dave A.
Dave A.

Reputation: 1

I know it's toooo late but....

From LINQ perpective, SPs (not using output for single result) will return DataSets, so to create a list you have to specify the returning field from the SP:

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

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

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

Upvotes: 0

Syeda
Syeda

Reputation: 1205

Try this,

public List<MyObject> GetObjects()
{

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

Updated: By using explicit casting it can be done like this

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

Upvotes: 5

mattypiper
mattypiper

Reputation: 1232

The Enumerable class also has a ToList member function that 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, check that the count is greater than zero before converting to 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 only expect a single result, use

myObject = (MyObject)tmp.ToSingle();

Lastly, you may want to consider wrapping this function in a try-catch block and catch SqlException and handle errors appropriately.

I am only mentioning the additional error handling due to experience developing applications that can crash if you don't have the additional error handling code!

Upvotes: 0

Jordan Parmer
Jordan Parmer

Reputation: 37174

ISingleResult<T> inherits 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 a constructor on MyObject that takes the DB type and creates a MyObject from it.

Have you tried dropping a breakpoint after the SPROC call to see what the debugger says about the object you get back?

Upvotes: 0

Related Questions