Implement SELECT Stored Procedures that return miscellaneous data in CTP2 of EF Designer

Data Access

Note: This blog post explains theory as well as steps to implementing the stored proc. If you read my blog regularly,  you are used to this.

11/30/09:Anoter note! Don’t miss the fact that this is for the verison of EF in VS2008 SP1. Things have been greatly improved in VS2010. Check out this *much newer* post: http://thedatafarm.wpengine.com/blog/data-access/a-big-step-for-stored-procedures-in-ef4/

As with the previous CTP Wizard, the Wizard in the new EF Tools (CTP2) will create functions in the Store Layer of the model to represent stored procedures in the database that the wizard is building a model from.

And as with the previous CTP, that’s all you get. It doesn’t implement it all the way through to the conceptual layer to be used in your applications. The documentation for the previous CTP showed how to complete DML stored procedures (Insert, Update & Delete) but not SELECT stored procedures that return data. So, I wrote a blog post in September that describes how to implement a SELECT stored procedure in EDM.

With the new tools, the Insert/Update/Delete functions are very simple to create now. Which is great. Select stored procedures with results that match an entity is also a breeze (see Guy Burstein’s blog post that walks through these).

But as of yet, there is no help for the Select stored procs that return miscellaneous results.

Therefore I wanted to update the instructions since you can do a few more things with the wizard for this. But you’ll still need to work with the raw XML of the EDMX file.

Let’s start with a simple example.

There is a stored procedure that I added to the AdventureWorksLT db that takes a year as a parameter and returns a list of Company Names with their total sales for the year. The resulting schema contains TotalSales, CustomerID and CompanyName. This result set doesn’t line up with any tables/views in the db or entities in my conceptual layer.

When the EDM is generated from the database the stored proc is realized in the StoreLayer. You can see this in two ways.

The Model Browser

Or in the raw xml of the EDMX file, in the Storage Section:

    <Function Name="annualCustomerSales" Aggregate="false"                 BuiltIn="false" NiladicFunction="false"                 IsComposable="false"                 ParameterTypeSemantics="AllowImplicitConversion"                 Schema="dbo">         <Parameter Name="fiscalyear" Type="char" Mode="In" />       </Function>

You can see that it does not determine the output parameters, which would be a daunting task to build a parser for.

So you need to create the following:

1) An entity that matches the schema of the results (TotalSales, CustomerID, CompanyName)
2) A function in the conceptual layer that maps back to the function in the store layer (called an import function)
3) Every entity needs to map to something in the store layer, therefore you need a fake table in the store layer that matches the entity in the conceptual layer .
4) The entity in the store layer needs an entity set
5) Lastly, the entity in the conceptual layer needs to map back to the entity in the store layer.

#3 through #5 are extraneous to what we are trying to do, but EDM has rules that need to be followed, so we need to throw all that extra stuff in there. It would be great if we could somehow just map the function to the entity – or if we could just use the function and return an anonymous type. But we can’t. So, let’s just move forward.

This seems like a lot of steps, but once you do it, it will make sense.

Step 1) You can create entities using the designer, so this isn’t so bad. Create a new entity and add the properties that are returned in the results. Here’s what mine looks like:

The customerID is flagged as a Key for the entity. An EntitySet will automatically be created. In my model it was named "result_annualCustomerSalesSet". Be sure to set the properties of the properties correctly (type, length, etc.) You can use properties of other entities in the model for clues as to what those values should be.

Save the model.

Step 2) Create an import function. In the Model Browser (right click in the designer and choose Model Browser if it isn’t visible), locate the stored procedure inside the Store layer (see screenshot above for a reminder). Right click on the stored proc and choose Create Function Import. You’ll get a screen where you need to fill out the Function Import Name and select an entity from a drop down list.

After you hit okay, the EDM will be updated and you will see the function import in the Model Browser.

Note that if the results matched an existing entity (a mapped entity, that is) you would be finished here.

Step 3) Create an Entity in the Store Layer that the result_annualCustomerSales entity will get mapped to. I cheat a little here by copying and pasting the Entity from the conceptual layer into the store layer then make appropriate edits.

In the conceptual layer it looks like this:

    After pasting this into the store layer section with the other EntityType elements, I edit so that the 
store entity looks like this
        <EntityType Name="result_annualCustomerSales">            <Key>                <PropertyRef Name="CustomerID" />            </Key>            <Property Name="CustomerID" Type="Int32" Nullable="false" />            <Property Name="TotalSales" Type="Decimal" Nullable="true" 
 Precision="19" Scale="4" />            <Property Name="CompanyName" Type="String" Nullable="true" 
 MaxLength="128" Unicode="true" FixedLength="false" />        </EntityType>
 

          <EntityType Name="result_annualCustomerSales">
              <Key>
                  <PropertyRef Name="CustomerID" />
              </Key>
              <Property Name="CustomerID" Type="int" Nullable="false" />
              <Property Name="TotalSales" Type="money" Nullable="true" />
              <Property Name="CompanyName" Type="nvarchar" Nullable="true" />
          </EntityType>

If you need help determining what the property properties (eg type) should be, look at some of the other Entity Types in the store model for clues. I use the same trick with the conceptual entity to figure out precision and scale for the TotalSales property.

Step 4) You also need the EntitySet in the store layer. Copy and paste that, too.

Entity Set in Conceptual Layer


<EntitySet Name="result_annualCustomerSalesSet" 


 EntityType="AdventureWorksLTModel.result_annualCustomerSales" />


 


Entity Set in Store Layer 


<EntitySet Name="result_annualCustomerSalesSet" 
 EntityType="AdventureWorksLTModel.Store.result_annualCustomerSales" 


 Schema="dbo"/>


 




Almost done!


Step 5) Map the Conceptual Entity to the Store Entity.


If the Mapping Details are not showing, click anywhere in the designer and choose Mapping Details. By default it will show up in the same area as the error list, etc. in the IDE.


In the designer, select the new Entity. You will see a little info about it in the Mapping Details window. Drop down the list for "Add a Table or View" and choose the table definition you created in the store layer.




All matching properties will automatically be mapped.




FINALLY YOU ARE DONE!


Now let’s go to the code and see the sproc in action!


    Dim aw As New AdventureWorksLTModel.AdventureWorksLTEntities

    Dim qsales = aw.annualCustomerSales("2004")

    For Each cust In qsales

      Console.WriteLine(String.Format("{0}: Sales= {1:C}", cust.CompanyName, cust.TotalSales))

    Next




Here’s a bit of the output


Aerobic Exercise Company: Sales= $2,361.64

Bulk Discount Store: Sales= $98,138.21

Eastside Department Store: Sales= $92,663.56

Coalition Bike Company: Sales= $2,669.32

Instruments and Parts Company: Sales= $70,698.99

Many Bikes Store: Sales= $81,834.98

Closest Bicycle Store: Sales= $39,531.61

West Side Mart: Sales= $87.09

Riding Cycles: Sales= $86,222.81

Futuristic Bikes: Sales= $272.65

Vigorous Sports Store: Sales= $1,170.54

Thrilling Bike Tours: Sales= $15,275.20

Trailblazing Sports: Sales= $45,992.37

Paints and Solvents Company: Sales= $14,017.91

Channel Outlet: Sales= $608.18

Thrifty Parts and Sales: Sales= $1,379.17

Engineered Bike Systems: Sales= $3,754.97

Essential Bike Works: Sales= $45.20


  Sign up for my newsletter so you don't miss my conference & Pluralsight course announcements!  

32 thoughts on “Implement SELECT Stored Procedures that return miscellaneous data in CTP2 of EF Designer”

  1. Thanks for the article Julia, we implemented this in our solution. Although we’ve come up with a problem if we rename any of the conceptual side properties? The data reader is incompatible with the specified ‘StoredProcedureEntityModel.CaseEnquiry’. A member of the type, ‘SystemReference’, does not have a corresponding column in the data reader with the same name.

    SystemReference is system_reference on the sp. If I leave the conceptual name to be system_reference we don’t have a problem but we need names to be the same throughout our application on certain fields. Any reason we can’t change property names?

    Reply

  4. Hi Julie

    Nice article. Wish I had found it when I was force-feeding myself the MS Koolaid at the beginnings of our "project". My workaround for this issue was to use views instead of stored procedures for regularly required select statements. This way everything was created with no hand-coding and we had the additional DB benefit of not having to compile the soterd procedures…

    Right? Wrong? I appreciate your comments. You are, after all the guru…

    Thanks,

    jax

    Reply

  6. Julie,

    I am having a lot of difficulty getting my stored procedures to work. Could you please post or send me a copy of the select stored procedure.

    Thanks,

    David

    Reply

  7. Julie,

    Excellent article, but is there anything to take it to the next step in RIA Services ??.

    Followed your guide and have a function created in my DomainService class :

    public IQueryable<PropertyList> GetPropertyListSet()

    {

    return this.Context.PropertyListSet;

    }

    My Entity is a ‘PropertyList’ (just Id and name) and the SP takes a UserId as an Int. This parameter is not referred to in the generated code.

    Just can’t seem to find anywhere to close this final step in getting RIA data from an SP :-(.

    Thanks

    Graham

    Reply

  8. @Graham, if you are using Astoria behind RIA Services, perhaps you should create an Astoria service operation to expose the sproc and then expose that operation through RIA services??

    just a thought but not tested (yet)

    Reply

  9. this is ok for one or two SP but what if I have 100- of it and especialy if I am work only with silverlight but someone other DB and SP?????

    Reply

  10. @marko

    Agreed that this is a big problem. So far the Beta1 of EF4 (in VS2010) doesn’t show much improvement. But I know there is more help coming for Stored Procs in Beta 2 so I’m very hopeful that it will be along these lines.

    julie

    Reply

  11. Hi,

    First of all thank you so much for providing this rare information.

    Can you guide me how to implement this in Silverlight?

    When I add the web reference of the ADO.Net DataServices in my Silverlight application and create the object of AdventureWorksLTEntities I do not see the method annualCustomerSales

    I see this method when I access the .edmx directly in WPF or ASP.Net, i.e. without ADO.Net Data Services, but this is not available in Silverlight though ADP.Net Data Service.

    Any help on this will be helpful.

    Reply

  13. This was very helpful!

    Unfortunately, it seems like a huge pain to do for a database with 100 or so read-only SPs. Any idea if MS will support custom entities like this automagically in VS2010?

    Reply

  14. @clduvall

    the VS2010 Beta 1 looks promising for making it easier to use the sprocs. We can manually create a complex type and then map a stored proc to it. I’m hoping it will improve even more with the Beta 2.

    Reply

  18. I have implemented the above the same way as you have described.

    My custom entity has only data field called DenseRank. And I mantained the same field as property too in the entity.

    But I get the error:

    {"The data reader is incompatible with the specified ‘DatamartModel.AreaResulSet’. A member of the type, ‘DenseRank’, does not have a corresponding column in the data reader with the same name."}

    Let me know what to in this regard.

    Thanks

    Irene

    Reply

  19. Hi Irene

    The funciton will expect an EXACT match between the schema of hte results and teh schema of the entity you are mapping them to.

    So the fields have to be the same names, in the same order and of the same type. I’m not sure how much more detail it requires.

    let me know if this helps or not.

    julie

    Reply

  21. @slyi: so, are you saying to create a dummy view in SQL Server and map the stored procedure to that entity?

    I did ask a question on this at thedatafarm.com/…/entity-framewor

    Is that what you are referring to as well Julie?

    I guess this alternative would be better than updating the .edmx manually after each update.

    Again, thank you Julie and everybody for your input and patience.

    I am having a blast learning about the Entity Framework.

    Reply

  23. Hi Julie,

    Thanks a lot for great articles and web cast on EF.

    Just continuing on above question. Is there a way we can return less columns form function for entity. In some scenario i don’t want to return full entity columns.

    Reply

  24. Julie,

    Every time I try the method of adding a ‘dummy’ table or view to accommodate reading from an Sp, I get the following:

    "Error 1 An error occurred while executing the command definition. See the inner exception for details.

    The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Too many parameters were provided in this RPC request. The maximum is 2100."

    Thanks,

    Shahryar

    Reply

  25. Hello Julia

    Great post!!!

    But I am working on 1st sproc in EF and got stuck in step 3 and 4 as you described above. I am not sure what should I copy from conceptual layer and paste at store layer ? I mean which files are these ?

    I can see this in edmx:conceptualmodels:

    <EntityType Name="result_IpakCompliance">

    <Key>

    <PropertyRef Name="Name" /></Key>

    <Property Name="Name" Type="String" Nullable="false" />

    <Property Name="Value" Type="Int32" Nullable="false" /></EntityType></Schema>

    But where should I paste it ? Please let me know …

    Thanks ….

    Reply

  26. Okay.. after a much closer look , I got it . Just need to change that in the xml file and my sproc started showing up in mapping.

    Thanks for the great post 🙂

    Reply

  27. Hi Julie, I follow every step but I’m doing it on c#, so the last part to call the proc it doesn’t brings the entity I can see it on the model and don’t have any errors but when I search in the next lines I can’t find the way to call it:

    private RedUsuarioContext ruc = new RedUsuarioContext();

    ruc."storeprocedure()"..

    please help!

    Reply

  28. Does this work with silverlight 4 and EF 4. I had a working code in asp.net 3.5 and ria. But when I tried to achieve the same in visual studio 2o10 with silverlight it doesn’t work. But is does work with normal asp.net web application. What do i have to use the technique with wcf ria and visual sutudio 2010

    Reply

  29. Hi Julie,

    2 1/2 years after this post originated it has helped me to build a dynamic data set using your method of function imports combined with a dummy table. The only thing i did different was actually add the table into the database so that the mapping would automatically occur and would not be erased when updating from data model. I have a unique reason for needing this otherwise views work just fine, but the performance gains i got by being able to pass parameters into my stored procedures and have it return a dynamic dataset that i can join w/ other tables in linq to entities is fantastic. Thank you.

    Reply

  30. Hi Julie,

    Is there that we could avoid the model’s xml being reset every time I make an update on my model. I tried this tutorial however every time I add a new function, it will reset all the changes i made in the xml. Thanks

    Reply

  31. @marvs…if you are modifying the SSDL manually, your changes will get overwritten. Also you should take into consideration the fact that this blog post is almost 3 years old. However the sad fact about the SSDL overwrites has not changed even in Visual STudio 2010.

    Reply
  32. Pingback: EF Mapping to Read Stored Procs, another twist | The Data Farm

Leave a Reply

Your email address will not be published. Required fields are marked *


The reCAPTCHA verification period has expired. Please reload the page.

This site uses Akismet to reduce spam. Learn how your comment data is processed.