MySQL Bugs: #53166: Using a MySQL stored procedure that maps to an entity causes exception

Bug #53166	Using a MySQL stored procedure that maps to an entity causes exception
Submitted:	26 Apr 2010 15:22	Modified:	3 Feb 2011 11:16
Reporter:	Marcos Boyington	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:	6.3.1	OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	entity-framework, stored-procedure

Description:
When trying to put results of a stored procedure into an entity, an exception is thrown:

The type of the key field 'Id' is expected to be 'System.Int64', but the value provided is actually of type 'System.String'

(Where Id is a primary key).

How to repeat:
Have a stored procedure that does something like:

SELECT id, name FROM user;

And attempt to store the results into the User entity

Suggested fix:
The problem seems to be related to the creation of the EntityKey.  It seems that for EntityKey.EntityKeyValues, the value really is a string (instead of an Int64).

Tonci: If you could direct me to where the values come from that the EntityKey is created with, I'd be more than happy to debug!

I debugged datareader.cs, and resultSet.Values seems to have the correct values, so I'm not sure what's going wrong.

Hi Marcos, let me check. It's so buried I always forget...

Marcos, not sure what you mean, can you please attach test case with DML/DDL so I can check.

As for "where to look", I'd start with Schema-Definition-xx.ssdl and definitely check the Fragments/Generators/Statements part of source.

<Schema Namespace="MyModel.Store" ...>
<EntityType Name="captcha_generated">
          <Key>
            <PropertyRef Name="id" />
          </Key>
          <Property Name="file" Type="varchar" Nullable="false" MaxLength="45" />
          <Property Name="id" Type="bigint" Nullable="false" StoreGeneratedPattern="Identity" />
          <Property Name="objs" Type="varchar" Nullable="false" MaxLength="45" />
          <Property Name="used" Type="utinyint" Nullable="false" />
        </EntityType>
...
<Function Name="get_generated_captcha" Aggregate="false" BuiltIn="false" NiladicFunction="false" IsComposable="false" ParameterTypeSemantics="AllowImplicitConversion" Schema="my_schema" />
...
</Schema>

<edmx:ConceptualModels>
          <EntitySet Name="CaptchaGenerated" EntityType="MyModel.CaptchaGenerated" />
          <FunctionImport Name="GetGeneratedCaptcha" EntitySet="CaptchaGenerated" ReturnType="Collection(MyModel.CaptchaGenerated)" />
...
</edmx:ConceptualModels>

<edmx:Mappings>
          <EntitySetMapping Name="CaptchaGenerated">
            <EntityTypeMapping TypeName="MyModel.CaptchaGenerated">
              <MappingFragment StoreEntitySet="captcha_generated">
                <ScalarProperty Name="Used" ColumnName="used" />
                <ScalarProperty Name="Objects" ColumnName="objs" />
                <ScalarProperty Name="Id" ColumnName="id" />
                <ScalarProperty Name="File" ColumnName="file" />
              </MappingFragment>
            </EntityTypeMapping>
          </EntitySetMapping>
          <FunctionImportMapping FunctionImportName="GetGeneratedCaptcha" FunctionName="MyModel.Store.get_generated_captcha" >
            <ResultMapping>
              <EntityTypeMapping TypeName="MyModel.CaptchaGenerated">
                <ScalarProperty Name="File" ColumnName="file" />
                <ScalarProperty Name="Id" ColumnName="id" />
                <ScalarProperty Name="Objects" ColumnName="objs" />
                <ScalarProperty Name="Used" ColumnName="used" />
              </EntityTypeMapping>
            </ResultMapping>
          </FunctionImportMapping>
...
</edmx:Mappings>

In MySQL:

DELIMITER $$

DROP PROCEDURE IF EXISTS `get_generated_captcha` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `get_generated_captcha`()
BEGIN
UPDATE `my_schema`.`captcha_generated` SET used=1, id=(@captcha_id:=id) WHERE used=0 ORDER BY id ASC LIMIT 1;
SELECT `id`, `file`, `used`, `objs` FROM `my_schema`.`captcha_generated` WHERE id=@captcha_id;
END $$

DELIMITER ;

Marcos, maybe a better way would be to attach mysqldump script for said database and an entire C# project? This way I'm always missing something.

Found the bug, and a workaround.

The problem seems to stem from the way the columns are mapped between SQL and EF (and thus, if there is a mismatch, a type error occurs).

The columns for an SQL method are built in the MySqlProviderServices.SetFunctionExpectedTypes.

The ordering used here are that of the members as defined in the EntityType in the EDMX.

This ordering of members is then usedin EFMySqlDataReader.GetValue to do a type conversion.

However, if the select statement in the SQL function does not return fields in the same order as that of the EntityType, problems happen.  It would seem the root of the problem is that the FunctionImportMapping is not being used.

As an example, I will use my previously posted data.  We have:

<Property Name="file" ... />
<Property Name="id" ... />

But the select statement in the sql function does not use this ordering:

SELECT `id`, `file`,

The workaround is simply to make sure that the SQL function returns fields in the same order as the EntityType defines the fields.

I can confirm I'm getting the same error on 6.3.3 and 6.3.5.

I haven't been able to apply the workaround since I use anonymous types, but problem persists even for a simple call as:

var myvar= (from r in myEntities.randomitems(4)
                                select new {r.id}).AsQueryable();

I have tried this but it doesn't seem to change anything:

var myvar= (from r in myEntities.randomitems(4)
                                select new {newid = int.Parse(r.id.ToString())}).AsQueryable();

No matter what I try I keep getting an error trying to use ResultMapping.  The error I keep getting is this:

Schema specified is not valid. Errors: 
<File Unknown>(144,12) : error 2025: XML Schema validation failed for mapping schema. Schema Error Information : The element 'EntityTypeMapping' in namespace 'urn:schemas-microsoft-com:windows:storage:mapping:CS' has invalid child element 'ScalarProperty' in namespace 'urn:schemas-microsoft-com:windows:storage:mapping:CS'.

Can you send me a project zip or more information to  allow me to repeat this?

Anyone?

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".