Stored Procedure Mapping with no EntitySet

Question

 

Is it possible to map stored procedures without an EntitySet?

I just want to insert some data into the database, returning a scalar would also be possible but not the whole entity.

If I specify no entityset, the C# class does not contain the definition for the sp.

If I specify an entityset with an ReturnType eg. Int32, the generator of course complains that we have to return an entity.

 

thx, Michael

 

EntityClassGenerator genClass = new EntityClassGenerator(LanguageOption.GenerateCSharpCode);

genClass.GenerateCode(strCsdlOutputPath, strOutputPathClass);

 

 

 

SSDL:

  <Function Name="spWarehouseLocationStatusInsert" Aggregate="false" BuiltIn="false" NiladicFunction="false" IsComposable="false" ParameterTypeSemantics="AllowImplicitConversion" Schema="dbo">
    <Parameter Name="intClientID" Type="int" Mode="in" />
    <Parameter Name="intOUID" Type="int" Mode="in" />
    <Parameter Name="guidUserID" Type="uniqueidentifier" Mode="in" />
    <Parameter Name="intWarehouseLocationID" Type="int" Mode="in" />
    <Parameter Name="strStatusID" Type="char" Mode="in" />
    <Parameter Name="datStatusDate" Type="datetime" Mode="in" />
    <Parameter Name="strDescription" Type="nvarchar" Mode="in" />
    <Parameter Name="guidProcessID" Type="uniqueidentifier" Mode="in" />
    <Parameter Name="datSysDateCreated" Type="datetime" Mode="in" />
  </Function>

 

 

CSDL:

  <FunctionImport Name="spWarehouseLocationStatusInsert">
       <Parameter Name="intClientID" Type="Int32" Mode="in" />
       <Parameter Name="intOUID" Type="Int32" Mode="in" />
       <Parameter Name="guidUserID" Type="Guid" Mode="in" />
       <Parameter Name="intWarehouseLocationID" Type="Int32" Mode="in" />
       <Parameter Name="strStatusID" Type="String" MaxLength ="3" Mode="in" />
     <Parameter Name="datStatusDate" Type="DateTime" Mode="in" />
     <Parameter Name="strDescription" Type="String" MaxLength="255" Mode="in" />
    <Parameter Name="guidProcessID" Type="Guid" Mode="in" />
    <Parameter Name="datSysDateCreated" Type="DateTime" Mode="in" />
      </FunctionImport>

 

MSL:

    <FunctionImportMapping FunctionImportName="spWarehouseLocationStatusInsert" FunctionName="shippingNETModel.Store.spWarehouseLocationStatusInsert">

    </FunctionImportMapping>

Answer 1

Unfortunately, due to time constraints, codegen does not generate code for functions that return scalar types.

 

For now, you can call them yourself using the ObjectContext.Connection.CreateCommand() method. You can wrap that call in a function and put it in a partial class extension of the codegen'd object context, for ease of use.

 

 

 

Answer 2

If I do so I get the following error:

The value of CommandText is not valid for a StoredProcedure command. Must be of the form 'ContainerName.FunctionImportName'.

 

What am I doing wrong? Should I define another ConnectionString or again import the StoredProcedures in the ssdl?

 

thx, Michael

 

public void SetStatus(int ClientID, int OUID, Guid UserID, string StatusID, DateTime StatusDate, string Description)

{

using (MyEntities db = new MyEntities())

{

db.Connection.Open();

DbCommand cmd = db.Connection.CreateCommand();

cmd.CommandType = CommandType.StoredProcedure;

cmd.CommandText = "spTest";

DbParam.AddParameter(cmd, "ClientID", DbType.Int32, ClientID);

cmd.ExecuteNonQuery();

}

}

Answer 3

If the stored procedure makes sense at entity-level, you should map it through SSDL-MSL-CSDL and use it like “MyEntities.MyEntityProc” - just like you use the entity container name to get to the entity sets for querying. This is the recommended practice.

 

On other hand, if the procedure doesn’t make sense at entity level, and you are using to hack something straight to database, you can get hold of the store connection and send native SQL or execute store procedures. That’s not recommended although it’s supported. You get a store DbConnection through:

 

Code Block

ObjectContext.Connection.StoreConnection

 

 

You can also execute procedures that return entity collections straight from ObjectContext using the protected ExecuteFunction() method, but that doesn’t seem to be applicable to your case.

 

Zlatko Michailov

Program Manager, Data Programmability Runtime

Microsoft Corp.

Answer 4

First many thx to your efforts answering my questions.

The reason why we are doing this is, that we have some very complex operations when modifying data in the db. Working with the objectmodel would result in several (more than 20) sql statements running against the database, which is from our perspective not very efficent. Please correct me if I am wrong on this point. Therefore the good old stored procedure came to our minds. So since the stored procedure makes absolutely sense at entity-level we will try to follow your recommendation and map it through SSDL-MSL-CSDL. Just to save some time: Is it than possible to call the sp without returning an entity. Maybe someone can give a sample for that.

 

br, Michael

 

Answer 5

Sorry for the late response.

 

What you describe is the right purpose for a stored procedure. Here’s a sample of a procedure that returns a scalar. A procedure that returns nothing is the same except you execute it with command.ExecuteNonQuery():

 

SQL

CREATE PROCEDURE RetrievalScalar(@x INT)

AS

BEGIN

    SELECT @x;

END;

GO

 

SSDL

<?xml version="1.0" encoding="utf-8"?>

<Schema Namespace="Microsoft.Samples.Data.QuickStart.Store" Alias="Self" ProviderManifestToken="09.00.3054" xmlns="http://schemas.microsoft.com/ado/2006/04/edm/ssdl">

  <EntityContainer Name="EntityFrameworkQuickStart" />

  <Function Name="RetrievalScalar" Aggregate="false" BuiltIn="false" NiladicFunction="false" IsComposable="false" ParameterTypeSemantics="AllowImplicitConversion" Schema="dbo">

    <Parameter Name="x" Type="int" Mode="In" />

  </Function>

</Schema>

   

MSL

<?xml version="1.0" encoding="utf-8"?>

<Mapping Space="C-S" xmlns="urnchemas-microsoft-com:windowstorage:mapping:CS">

  <EntityContainerMapping StorageEntityContainer="EntityFrameworkQuickStart" CdmEntityContainer="QuickStart" >

    <FunctionImportMapping FunctionImportName="RetrievalScalar" FunctionName="Microsoft.Samples.Data.QuickStart.Store.RetrievalScalar" />

  </EntityContainerMapping>

</Mapping>

 

CSDL

<?xml version="1.0" encoding="utf-8"?>

<Schema Namespace="Microsoft.Samples.Data.QuickStart" Alias="Self" xmlns="http://schemas.microsoft.com/ado/2006/04/edm">

  <EntityContainer Name="QuickStart" >

    <FunctionImport Name="RetrievalScalar" ReturnType="Int32">

      <Parameter Name="x" Type="Int32" Mode="In" />

    </FunctionImport>

  </EntityContainer>

</Schema>

 

C#

You may use the protected ObjectContext.ExecuteFunction() only for procedures that return collections of entity types. But you may use EntityClient for all kinds of procedures including ones that return nothing:

 

            using (EntityConnection conn = new EntityConnection(StoredProcRetrievalString))

            {

                conn.Open();

 

                const int x = 10;

                EntityCommand cmd = conn.CreateCommand();

                cmd.CommandText = "QuickStart.RetrievalScalar";

                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.AddWithValue("x", x);

 

                Console.WriteLine("\nQuickStart.RetrievalScalar({0})", x);

                int ret = (int)cmd.ExecuteScalar();

                Console.WriteLine("Retrun int={0}", ret);

            }

 

In your case you have to use cmd.ExecuteNonQuery() to invoke the procedure.

 

 

Zlatko Michailov

Program Manager, Entity Services

Microsoft Corp.

Answer 6

Hello, I ran into a similar problem and you have a great explanation of calling procs and function support.

However, can you comment on how to call a T-SQL function like GetDate() directly ? 

This used to work in EDM Beta - I can no longer rely on this code.

Can you comment on why it might not work anymore and what is the best practice with example to get the SQL Server date using good old fashion and commonly used GetDate()  or current_timestamp for that matter....

 public partial class Entities : global:ystem.Data.Objects.ObjectContext
    {


        public DateTime GetDate()
        {
            // You got to be kidding me!!
            var q = this.CreateQuery<DateTime>("GetDate");
            return q.ToArray<DateTime>()[0];
        }
.
.
.
}

returns Error: 'GetDate' cannot be resolved into a valid type constructor or function., near function, method or type constructor, line 1, column 8.

thanks
Marty

Answer 7

 

you can try using SqlServer.GEtDate()

 

SqlServer namespace allows you u to directly call functions defined on the sql server.

 

Zeeshan Hirani

Answer 8

I mapped my stored proc as mentioned above but it doesnt appear as object in my data context

although it appeared before ,can anyone help?

Answer 9

In EF3.5, scalar function imports do not get codegened. See the beginning of this thread for more info.

---

This posting is provided "AS IS" with no warranties, and confers no rights.