return the rowcount of a dynamic sql query using linq
I would like to return the rowcount of a dynamic sql query using linq similar to mentioned here:

http://weblogs.asp.net/scottgu/archive/2007/08/16/linq-to-sql-part-6-retrieving-data-using-stored-procedures.aspx

![enter image description here][1]


  [1]: http://i.stack.imgur.com/rDGnA.jpg

I'm using dynamic sql to create the where clause and to implement paging on the result set,  The rowcount I want to return is the total number of records that meet the where condition. 

My SQL that is causing me problems:

	-- get row count
				

    SET @SQL = '@TotalRowCount = SELECT COUNT(*) as TotalRowCount'
    				SET @SQL = @SQL + @WHERE
    				
    				IF (LEN(@SUBWHERE) > 0)
    				BEGIN
    				SET @SQL = @SQL + @SUBWHERE
    				END
    							
    				SET @SQL = @SQL + ')) '
    				
    
    				exec sp_executesql @SQL
    			
    				
    			
    				END

 (I need this to be the output param @TotalRowCount in the param list here):

    ALTER PROCEDURE [dbo].[_tournament_GetTournamentsByConveners]
    (
    
    	@LastName varchar(100)   = null ,
    
    	@Username varchar(256)   = null ,
    
    	@Email varchar(100)   = null ,
    
    	@IsWildcard bit = null,
    
    	@PageIndex int ,
    
    	@PageSize int,
    	
    	@TotalRowCount int output 
    )
    AS  
return the rowcount of a dynamic sql query using linq
===

I would like to return the rowcount of a dynamic sql query using linq similar to mentioned here:

http://weblogs.asp.net/scottgu/archive/2007/08/16/linq-to-sql-part-6-retrieving-data-using-stored-procedures.aspx

![enter image description here][1]


  [1]: http://i.stack.imgur.com/rDGnA.jpg

I'm using dynamic sql to create the where clause and to implement paging on the result set,  The rowcount I want to return is the total number of records that meet the where condition. 

My SQL that is causing me problems:

	-- get row count
				

    SET @SQL = '@TotalRowCount = SELECT COUNT(*) as TotalRowCount'
    				SET @SQL = @SQL + @WHERE
    				
    				IF (LEN(@SUBWHERE) > 0)
    				BEGIN
    				SET @SQL = @SQL + @SUBWHERE
    				END
    							
    				SET @SQL = @SQL + ')) '
    				
    
    				exec sp_executesql @SQL
    			
    				
    			
    				END

 (I need this to be the output param @TotalRowCount in the param list here):

    ALTER PROCEDURE [dbo].[_tournament_GetTournamentsByConveners]
    (
    
    	@LastName varchar(100)   = null ,
    
    	@Username varchar(256)   = null ,
    
    	@Email varchar(100)   = null ,
    
    	@IsWildcard bit = null,
    
    	@PageIndex int ,
    
    	@PageSize int,
    	
    	@TotalRowCount int output 
    )
    AS  