String command in SQL stored procedure return int result not records when called from LINQ TO SQL

unchecked

Since I found it hard for me to create Join using LINQ, I created a Stored Procedure for my select statement. I would just call the Store Procedure and presto I have my result sets. That is all fine if I have a straight select statement. The problem happens when I have a dynamic statement and I need to create command variable and then execute it using EXEC command from inside the Stored Procedure. When a statement is build in this manner, the LINQ TO SQL treated the result as int and not the INUMERABLE result.

CREATE PROCEDURE dbo.GetAlumni (
@batch int, @branch int, @fname varchar(25), @lname varchar(25), @location varchar(25)
)
AS
declare @cmd varchar(8000)

set @cmd = "select t.*, c.name as Cur_Country_Desc, b.name as Branch_Desc "
set @cmd = @cmd + "from entiretable t "
set @cmd = @cmd + "left join countries c on t.n_cur_country = c.code "
set @cmd = @cmd + "left join branches b on t.branch_code = b.code "
set @cmd = @cmd + "where t.batch = " + convert(char(4), @batch) + " "

IF @branch IS NOT NULL
BEGIN
SET @cmd = @cmd + "AND t.branch_code = " + convert(char(1), @branch) + " "
END

IF @fname IS NOT NULL OR LEN(@fname) > 0
BEGIN
SET @cmd = @cmd + "AND t.N_FIRST_NAME LIKE '" + @fname + "%' "
END

IF @lname IS NOT NULL OR LEN(@lname) > 0
BEGIN
SET @cmd = @cmd + "AND t.N_LAST_NAME LIKE '" + @lname + "%' "
END

IF @location IS NOT NULL OR LEN(@location) > 0
BEGIN
SET @cmd = @cmd + "AND (t.N_CUR_TOWN_CITY LIKE '" + @location + "%' OR t.N_CUR_STATE_PROVINCE LIKE '" + @location + "%') "
END

exec (@cmd)

I am calling that stored procedure using

    var db = new WITWAY\_DATADataContext();
    IEnumerable result = db.GetAlumni(batch, branch, fname, lname, location);

and it throws an error 'Cannot implicitly convert type 'int' to System.Collections.Generic.IEnumerable' What's your implementation of this scenario? Thanks!

On Error Jump Off the Building

Pete OHanlon

The SQL you execute isn't exactly the SQL you think you are executing. You should take a look at using SET NOCOUNT ON in your SQL to supress record counts.

Deja View - the feeling that you've seen this post before.

My blog | My articles | MoXAML PowerToys

unchecked

Thanks for the reply but LINQ still read the result of the Stored Procedure as INT.

On Error Jump Off the Building

User 3792082

i am also facing the same problem can some one help me in solving these issues

cSaRebel

Did anyone ever find a resolution to using LINQ with SQL stored procs that use Dynamic SQL? 'SET NOCOUNT ON/OFF' does not seem to have any affect what-so-ever.

Trent G. Fryar Architect/Lead Software Consultant Trent.Fryar@csa-rebel.com

ParvezShaikh

hi friends, i had faced this problem and i found a temporary solution. Before u drag and drop your sp from .net server explorer to .dbml file just comment the body of SP and write only select statement without double quote and where condition. e.g. (in your case) select t.*, c.name as Cur_Country_Desc, b.name as Branch_Desc from entiretable t left join countries c on t.n_cur_country = c.code left join branches b on t.branch_code = b.code and make actual sp code comment and now drag and drop sp from .net server explorer to .dbml file, which will creates a class file with the SPName+"Result" in .designer.cs file which have all the fields. now just remove above code from your sp and make uncomment your actual code. now i hope this will work becouse i have tried. again remember it will return class that u can find in .designer.cs file with your SPName+"Result". Let me know u have any doubts. Regards, Tabrez Shaikh Software Developer tabrezshaikh11@gmail.com