Executing stored procedure using linq in c#

Question

I made a stored procedure in sql server 2008 which gives me the changes made to a table. I am using Linq to SQL to use this table in C#. my stored procedure is

CREATE PROCEDURE dbo.getlog 
    -- Add the parameters for the stored procedure here
@p1 int = 0, 
@p2 int = 0
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

  -- Insert statements for procedure here
DECLARE @from_lsn binary(10), @to_lsn binary(10)
SET @from_lsn =
 sys.fn_cdc_get_min_lsn('dbo_User_Info')
 SET @to_lsn   = sys.fn_cdc_get_max_lsn()
 SELECT ID_number, Name, Age FROM cdc.fn_cdc_get_all_changes_dbo_User_Info
 (@from_lsn, @to_lsn, N'all');
END
GO

The above procedure runs fine in sql server. However when i run this statement using linq in C#

        mytestDataContext obj = new mytestDataContext();
        var test=obj.ExecuteCommand("dbo.getlog");
        foreach( var abc in test)
        {}

I get this error

Error 1 foreach statement cannot operate on variables of type 'int' because 'int' does not contain a public definition for 'GetEnumerator'

Answer 1

ExecuteCommand returns an int.. not your results.

See MSDN here: http://msdn.microsoft.com/en-us/library/system.data.linq.datacontext.executecommand.aspx

public int ExecuteCommand(
    string command,
    params Object[] parameters
)

I think you're after ExecuteQuery.

Answer 2

ExecuteCommand method returns Int32 and you can't use magical foreach loop using a simple integer.

Return Value
Type: System.Int32
The number of rows modified by the executed command.

I'm not too much familiar with DataContext class but you can use DataContext.ExecuteQuery which returns IEnumerable<TResult> and you can use foreach loop with it.

Return Value
Type: System.Collections.Generic.IEnumerable<TResult>

A collection of objects returned by the query.