How do I get Linq to SQL to recognize the result set of a dynamic Stored Procedure?

First IMPORTANT your SQL is vulnerable to injection; the inner command should be parameterized: if len(@l_author) > 0 set @sql = @sql + ' and author like ''%''+@author+''%''' EXECUTE sp_executesql @sql, N'@author varchar(100)', @L_author This passes the value of L_author in as the author parameter in the dynamic command - preventing injection attacks Second - you don't really need the temp table. It isn't doing anything for you... you just INSERT and SELECT. Perhaps just EXEC and let the results flow to the caller naturally?

In other circumstances a table-variable would be more appropriate, but this doesn't work with INSERT/EXEC Are the columns the same for every call? If so, either write the dbml manually, or use a temp SP (just with "WHERE 1=0" or something) so that the SET FMT_ONLY ON can work If not (different columns per usage), then there isn't an easy answer. Perhaps use regular ADO.NET in this case ( ExecuteReader IDataReader and perhaps even DataTable.

Fill ) Of course, you could let LINQ take the strain... (C#): if(!string. IsNullOrEmpty(author)) { query = query. Where(row => row.Author.

Contains(author)); } etc.

First - IMPORTANT - your SQL is vulnerable to injection; the inner command should be parameterized: if len(@l_author) > 0 set @sql = @sql + ' and author like ''%''+@author+''%''' EXECUTE sp_executesql @sql, N'@author varchar(100)', @L_author This passes the value of @L_author in as the @author parameter in the dynamic command - preventing injection attacks. Second - you don't really need the temp table. It isn't doing anything for you... you just INSERT and SELECT.

Perhaps just EXEC and let the results flow to the caller naturally? In other circumstances a table-variable would be more appropriate, but this doesn't work with INSERT/EXEC. Are the columns the same for every call?

If so, either write the dbml manually, or use a temp SP (just with "WHERE 1=0" or something) so that the SET FMT_ONLY ON can work. If not (different columns per usage), then there isn't an easy answer. Perhaps use regular ADO.NET in this case (ExecuteReader/IDataReader - and perhaps even DataTable.

Fill). Of course, you could let LINQ take the strain... (C#): ... if(!string. IsNullOrEmpty(author)) { query = query.

Where(row => row.Author. Contains(author)); } ... etc.

The first two points are important to this particular question, but in general I think SET FMTONLY ON msdn.microsoft. Com/en-us/library/ms173839. Aspx is a core part of the issue when LINQ to SQL generated code isn't returning the expected results.In some cases I've set it to OFF fishofprey.

Com/2009/08/detecting-when-nettiers-is. Html when I know the stored proc won't have any affect on the data.I.e. Selects only.

– Daniel Ballinger Aug 23 '10 at 9:28.

There's no real easy way to do this. I've had the same problem in the past. I think the issue is that Linq to Sql has no way of "figuring out" which type will be returned since you're building up the SELECT statement at execution time.

What I did to get around this, was in the stored proc, I did just a select and selected all the columns that I possibly needed. Then, I had Linq to Sql generate the function based on that. Then, I went back to SQL and changed the stored proc back to the way it's supposed to be.

The trick here is not to regenerate your DBML.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.