EF: SELECT with a SP

Imagine the following scenario. We have a SP which returns a SELECT and we want to integrate it on an EDM. For this, we will create an entity type. But we want to have to use the SP to have some instances of our entity type. This scenario isn’t really covered by EF v1 and we must do some “strange things” to realize it.

For this post, we will use this SP:

CREATE PROCEDURE [dbo].[TOTO]
AS
BEGIN
 SELECT Id, 1 AS VALUE FROM Test
END

When we will get the SP on our EDM we will have this on the SSDL:

<Function Name=TOTO Aggregate=false BuiltIn=false NiladicFunction=false IsComposable=false ParameterTypeSemantics=AllowImplicitConversion Schema=dbo />


Then we will create an entity type in the CSDL (which we can do with the designer):

<EntityType Name=Toto>

    <Key>

        <PropertyRef Name=Id />

    </Key>

    <Property Name=Id Type=Int32 Nullable=false />

    <Property Name=Value Type=Int32 Nullable=true />

</EntityType>


With the creation of the entity type, we will also have a new EntitySet:

<EntitySet Name=Totos EntityType=TestModel.Toto />


Then we can add the Function Import in the CSDL that will have a method on our ObjectContext. You just need to set the return type to Toto type.

Note that the entity type must have the same properties than the columns returned by the SP.

Then, as we don’t want to have the EntitySet on our ObjectContext and as we can’t delete it, we will set the property to private on our ObjectContext. Note that we can do this with the designer (go to Model Browser window, EntityContainer, EntitySets, Totos, go to the Properties window and change the Getter value).

But this isn’t all. Indeed, we will have an error with this EDM because Toto entity type isn’t mapped (even if we don’t want to use it directly).

So, how to do?

I use a SSDL View. As the SQL command will never be excecuted, we can write anything (even if it isn’t a correct query):

<EntityContainer Name=TestModelStoreContainer>

    <EntitySet Name=Totos EntityType=TestModel.Store.Toto >

        <DefiningQuery>

            I can do anything here! :-)

        </DefiningQuery>

    </EntitySet>

</EntityContainer>

<EntityType Name=Toto>

    <Key>

        <PropertyRef Name=Id/>

    </Key>

    <Property Name=Id Type=int Nullable=false/>

    <Property Name=Value Type=int />

</EntityType>


Then we will map the entity type on the SSDL View and here we are.


Note that EF will cover better this scenario with the V2 as Daniel Simmons told me: “In v2 we’re allowing sprocs to return complex types which will be the easy way to return objects which don’t need to be mapped, etc.”

This entry was posted in 7671, 7674, 7675. Bookmark the permalink.

4 Responses to EF: SELECT with a SP

  1. Grant says:

    This is good, but every time you do an Update from database, all the changes get reset.

  2. Matthieu MEZIL says:

    That’s right. As told Noam:

    “The issue is that the update model wizard regenerates the SSDL. We could not fit SSDL merging into the current release, and are currently scoping how much we can do for the next one. At the very least, we would like to preserve additions, such as the one you have made.

    For now, you have a few options. I think the one I would choose is the projection route. That is, create methods methods that do something like:

    from document in context.documents select new {document.description, document.timestamp}

    The EF will then scope the SQL down to only select the projected columns.”

  3. Grant says:

    Thanks for the reply.

    But what would you suggest I do if, say, I already had a complex query in a stored procedure, where the result set doesn’t resemble any entity in the EF model. It wouldn’t require any change tracking as I would not be making any updates via this result set, and doesn’t need to be mapped.

    Would you suggest ignoring the EF in these instances and simply call the stored proc via an ADO command? It seems a bit of a backward step.

    Complex types seems to be the best solution, for what I think are these very common situations.
    It’s a shame that although the framework supports it via your workaround, the modeler messes it up.

    Any idea when version 2 will be coming out? Although it doesn’t really help me now :(

    Thanks.

  4. urpcor says:

    I have got the same issue.
    but I disabled “Validate on Build”.
    It still complains with an error while building.
    But the overall build succeeds.
    Cool , but there I have still to create the entity by hand!
    Version 2 of EF would be required. Hope it comes soon.

Leave a Reply

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


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>