Entity framework for execute string stored procedure

Question

New to EF... using 6.0. I've have a Stored Proc which has the dynamic build select query inside a string variable that outputs using Execute(@StringQuery). This select has around 20 columns.

After adding this SP in EF, the return type is INT (not sure why). But I think I've to add all the columns manually in Complex types in EDMX. Wanted to know whether there is any better way to handle this as the columns are in huge number.

Please suggest.

Procedure Text:

DECLARE @StringQuery VARCHAR(MAX)
SET @StringQuery = 'SELECT AROUND 20 COLUMNS WITH LOT OF CONDITIONS ADDED'

EXECUTE(@StringQuery)

score 1 · Answer 1 · answered Feb 03 '15 at 06:57

1

Open your model
Go to View->Other Windows->Entity Data Model Browser
In browser expand your Model->Function Imports and double click on Stored Proc
In Returns a Collections off choose Complex and press Get Column Information
Click Create New Complex Type
OK, Save

answered Feb 03 '15 at 06:57

Giorgi Nakeuri

35,155
8
47
75

When I do that, I get message saying 'The selected stored procedure or function returns no columns' and OK button is disabled. – Interstellar Feb 03 '15 at 07:23
Can you paste you Procedure text? – Giorgi Nakeuri Feb 03 '15 at 07:23
I've added a sample of procedure how it looks like – Interstellar Feb 03 '15 at 07:30
1

Do you have logic in Storec proc which builds dynamic query? Try at top of your procedyre `SET FMTONLY OFF` – Giorgi Nakeuri Feb 03 '15 at 07:39
2

Also read this. http://stackoverflow.com/questions/7128747/ef4-the-selected-stored-procedure-returns-no-columns – Giorgi Nakeuri Feb 03 '15 at 07:39

Entity framework for execute string stored procedure

1 Answers1