My Software Notes

Useful things I discover

Entity Framework 4 – The selected stored procedure returns no columns (part 2)

with 5 comments

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.

Advertisements

Written by gsdwriter

November 4, 2011 at 2:35 pm

5 Responses

Subscribe to comments with RSS.

  1. […] 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 […]

  2. 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

  3. 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

  4. 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


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: