Return dynamic resultset from stored procedure using Linq

Question

I have created a stored procedure which returns result set. Now I need to call this stored procedure from Business Layer(.cs file) using Linq.

CREATE PROCEDURE GetApprovedContent 
AS BEGIN 
DECLARE @columns VARCHAR(1000) 
DECLARE @query nVARCHAR(4000)
SELECT @columns = COALESCE(@columns + ',[' + Code + ']','[' + Code + ']') 
FROM (
        SELECT DISTINCT Code
        FROM dbo.tblLanguages
    ) x 
SET @query = '
SELECT *
FROM (
        SELECT c.[id],c.[Content],t.[Tag], l.[Code],CASE WHEN r.[LanguageId] IS NULL THEN '''' ELSE ''YES'' END ''RequestLanguage''
        FROM dbo.tblContents c CROSS JOIN dbo.tblLanguages l
        LEFT OUTER JOIN dbo.tblRequestedLanguages r ON c.id= r.[ContentId] AND l.[id]=r.[LanguageId]
        JOIN dbo.tblTags t ON c.[TagId]= t.[id]
        WHERE [status] = (SELECT id FROM dbo.tblStatus WHERE [Status] =''Approved'')
) as s
PIVOT
(
    MAX(RequestLanguage)
    FOR [Code] IN ('+@columns+')
)AS piv' 
EXECUTE(@query) 
END

This procedure returns below Result-Set where number of column is not fixed

Id Content Tag Lang1 Lang2 Lang3 Lang_n
1  Ball  Sport Y      N     Y     N
2  Bat   Sport N      Y     N     Y

so how can I call this procedure using linq?

see [msdn about it](http://msdn.microsoft.com/en-us/library/vstudio/bb386946(v=vs.100).aspx) — Grundy, Aug 06 '14 at 12:37
do you try this: [How to: Use Stored Procedures to Return Rowsets (LINQ to SQL)](http://msdn.microsoft.com/en-us/library/vstudio/bb386975(v=vs.100).aspx)? — Grundy, Aug 07 '14 at 07:10

score 0 · Answer 1 · edited May 23 '17 at 10:26

0

Leave the pivot off so you have a defined number of columns. Pivot the set in-memory after the query.

Example of in-memory pivot : https://stackoverflow.com/a/167937/8155

Also, don't use dynamic sql (since you're not pivoting in the database you don't need to).

edited May 23 '17 at 10:26

Community

1
1

answered Aug 09 '14 at 22:26

Amy B

108,202
21
135
185

How can I set the column name without using pivot because there can be n number of languages and language name can be 'en-GB','sr-SE' something like this?? – Madhuri Lad Aug 12 '14 at 10:13

Return dynamic resultset from stored procedure using Linq

1 Answers1