Entity Framework + Sql Anywhere 11 + Stored procedures

Question

I have been playing with the EF in the last couple of days. Our applications are based on SQL Anywhere 10 databases, and all our data access are done through stored procedures. Since EF is not supported by SA 10, I am testing EF with SA 11. For this purpose i have create a small database with 2 tables and a couple of stored procedures (based on the nerddinner database from the asp.net mvc samples, see here ) I have created a model from the database, tables and stored procedures and did the necessary function imports. I have a stored procedure with the following signature:

ALTER PROCEDURE "DBA"."get_dinner"( @dinner_id integer)
BEGIN
select dinner_id, title, event_date, description, hosted_by , contact_phone, address, country, latitude, longitude
from dba.dinners d
where d.dinner_id = @dinner_id
END

And the resulting function import code looks like this:

public global::System.Data.Objects.ObjectResult<dinner> get_dinner(global::System.Data.Objects.ObjectParameter dinner_id)
{
    return base.ExecuteFunction<dinner>("get_dinner", dinner_id);
}

And that's the problem. Ideally, the code generated should accept an int parameter, instead of global::System.Data.Objects.ObjectParameter dinner_id As far as I see, the edmx file has all the data it needs to interpret the parameter types correctly:

<Function Name="get_dinner" Aggregate="false" BuiltIn="false" NiladicFunction="false" IsComposable="false" ParameterTypeSemantics="AllowImplicitConversion" Schema="DBA">
  <Parameter Name="dinner_id" Type="int" Mode="InOut" />
</Function>

Am I missing something here? What else is needed in order to have a function import with the proper parameter type? Is this something you can correct by tweaking the edmx file, or is it a problem of the SA11 EF support implementation.

Hope someone can give me some further clues.

Answer 1

This is a known problem with InOut parameters and code gen for Function Imports.

We have been talking about making InOut parameters produce code like this:

public ObjectResults<dinner> get_dinner(ref int dinner_id);

Rather than what you have.

One thing to try is converting from an 'InOut' to an 'In' parameter. Code gen should then produce something like this:

public ObjectResults<dinner> get_dinner(int dinner_id);

The real question though is does it work if you call it?

Hope this background helps

Cheers

Alex