Entity Framework 4 – The selected stored procedure returns no columns (part 2)
My original post on this topic, Entity Framework 4 – The selected stored procedure returns no columns, has been getting quite a lot of hits recently and I realize that of all the solutions I gave on that page the one I use the most is the one that’s mentioned way down the page in a comment. So I thought it would be a good idea to highlight it.
The simplest solution to this issue is based on some data given by Frank Lan, a guy from MS Online Community Support on the SilverLight forums. He said:
I did some research on how Visual Studio gets the column information. If you open SQL Server profiler when you click “Get Column Information”, you will find that Visual Studio queries against database to get the column information. One query I notice is that Visual Studio tries to execute the store procedure with all parameters set to be null. I am not sure if this is the way Visual Studio determines whether there’s column information or not. But I suggest you trying to execute your query in this way to see if there’s any column returned by your store procedure. Of course you can monitor the profiler your own to find more useful information.
That suggested a simple way to handle any situation where you have applied the solutions I gave in the original article (don’t just ignore those solutions and do this easy one) or where you are forced to use temp tables because temp variables won’t do and you still can’t get the columns:
Stick an “if” statement at the top of your sproc that checks if all parameters are null and if true returns the structure.
For Example:
if @param1 is null and @param2 is null then
begin
select
cast(null as varchar(10)) as Column1,
cast(null as bit) as Column2,
cast(null as decimal) as Column3
end
Just be aware that the casts are required otherwise EF won’t be able to tell what the data types are supposed to be. Even if you return values instead of nulls you still need the casts.
Hope that helps.
[...] most is in one of the comments way down the page. So I have written an additional article here, Entity Framework 4 – The selected stored procedure returns no columns (part 2), to highlight it. I suggest you do read the other solutions because they may apply and could [...]
Entity Framework 4 – The selected stored procedure returns no columns « My Software Notes
November 4, 2011 at 2:48 pm
yup i read the content . you should to describe it more.
if you want more visitors for your site then youcan add your site
into my social bookmark site.which is
Latest technology, ASP.net, SEO, Internet Marketing, SQL Server, AJAX, Silver Light, JQuery, Entity framework, PHP
sabbir ahamed sawon
November 27, 2011 at 6:05 am
Hello,
I have a stored procedure as follows which gets the result after joining data from two tables.
CREATE PROCEDURE spGetPartnerInterests(@iCompanyId int)
AS
BEGIN
SELECT CP.PartnerCompanyID, CM.CompanyName, isnull(CM.LogoFileName,”) AS LogoFileName
FROM tblCompanyPartners CP INNER JOIN tblCompany CM ON CP.PartnerCompanyID=CM.CompanyID
WHERE CP.companyid=@iCompanyId
END
As of now, we are creating a new table in SQL Server with these three fields and in edmx file, we are ‘mapping this table’ to the stored procedure using ‘Add Function Import’ -> ‘Entities’.
Every time a stored proc with join is created and called, we are in need of creating a new table. Is there any alternative to call stored proc without creating a new table every time a join is made.
Thanks & Regards,
Santosh Varma.
Santosh Varma
December 20, 2011 at 6:13 am
In Version 1 (aka 3.5) of Enity Framework you had to create a table in the database then pull it into your edmx so an entity would be generated by EF that you could then use in the Function Import dialog. Or you had to manually edit the edmx file to add the entity – but then you couldn’t use the “Refresh from database” feature or it would overwrite your manually added entities.
In Version 4 of EF the Function Import dialog gives you the option of creating an entity that matches the result set coming from your stored procedure. So the EF 4 designer (in VS2010) handles it for you.
In Version 4.2 of Entity Framework you can use “Code First” and the “SqlQuery” method and you don’t even need to use the designer.
If you are using version 1 then you can either upgrade to EF 4 or you are stuck with creating a new table everytime you create a new result set from a stored procedure.
One thing to remember: you have to create the table to pull it into your edmx so an entity class is created but (assuming you point your EF designer at your test or QA area) you don’t need to create the “fake” table in production. In production EF doesn’t care about the actual table if you are only calling the stored procedure to get data, so the fact that it doesn’t exist in production has no effect on EF or your application.
I hope that helps.
gsdwriter
December 21, 2011 at 9:40 am
Thanks!
Added a check for null parameters and, if so, return the columns from my table variable and now the EF sees the columns and I can create a Complex Type.
I was going to manually create about 20 columns in the designer file. This saved me a lot of work!
Jerry T.
January 19, 2012 at 1:26 pm