entity mappings and complex types

[vatirim]

I manually created an entity to be used for the result of a stored procedure.
I also created a complex type which is used in that entity to group some fields.
When I map the entity to the columns of the stored procedure result I can also map columns to the fields inside the complex type using the class editor dialog.
However, this does not seem to be taken into account. Only when I do the mapping directly in the complex type definition the call works.

Is this a bug?

BTW: It would be nice if one could overload the entity mapping for each stored procedure call. Some stored procedures may return the same "entity" but using different column names. Currently it seems one has to define one entity per stored procedure with different result column names.

PS: I use Entity Developer v4.7, target: NHibernate.

[Shalex]

When I map the entity to the columns of the stored procedure result I can also map columns to the fields inside the complex type using the class editor dialog.
However, this does not seem to be taken into account. Only when I do the mapping directly in the complex type definition the call works.

We have reproduced the problem assuming there is a following shema in the database:

Code: Select all

CREATE TABLE dbo.Dept (
  DEPTNO int NOT NULL,
  DNAME varchar(14) NULL,
  LOC varchar(13) NULL,
  PRIMARY KEY (DEPTNO)
)
GO
CREATE PROCEDURE dbo.SelectFromDept
    @deptno int
AS 
    SET NOCOUNT ON;
    SELECT * FROM dept
    WHERE deptno = @deptno;
GO

1. Drag&drop the table and procedure on the diagram surface of the NHibernate model and assign the return type of the SelectFromDept method to the Dept class.
And now the method works. The corresponding XML:

Code: Select all

  <sql-query name="SelectFromDept" callable="true">
    <return class="Dept">
      <return-property name="DEPTNO" column="DEPTNO" />
      <return-property name="DNAME" column="DNAME" />
      <return-property name="LOC" column="LOC" />
    </return>
    <query-param name="deptno" type="Int32" />exec dbo.SelectFromDept :deptno </sql-query>

2. Drag&drop the DNAME and LOC properties from the Dept class to the diagram surface to create a new complex type. And procedure doesn't work ("could not execute query"), its XML:

Code: Select all

  <sql-query name="SelectFromDept" callable="true">
    <return class="Dept">
      <return-property name="DEPTNO" column="DEPTNO" />
    </return>
    <query-param name="deptno" type="Int32" />exec dbo.SelectFromDept :deptno</sql-query>

3. To fix the problem, reassign the return type of our method (set to None, save, set back to Dept, save) and it works again:

Code: Select all

  <sql-query name="SelectFromDept" callable="true">
    <return class="Dept">
      <return-property name="DEPTNO" column="DEPTNO" />
      <return-property name="DNAMEType">
        <return-column name="DNAME" />
        <return-column name="LOC" />
      </return-property>
    </return>
    <query-param name="deptno" type="Int32" />exec dbo.SelectFromDept :deptno</sql-query>

We will investigate this issue. As a temporary workaround, please do the reassignment as shown above.

BTW: It would be nice if one could overload the entity mapping for each stored procedure call. Some stored procedures may return the same "entity" but using different column names. Currently it seems one has to define one entity per stored procedure with different result column names.

You can change result mapping of your method in the following way: right click on the method in Model Explorer > Result Mapping Details.