[Solved] Custom class and stored procedure

[Marko Gluhak]

Hi,

I use reverse model (database first). In designer I created dummy class called cSiteMenu. I created properties:
IdCategory (int32, identity)
CategoryName (string)
DisplayOrder (int32)
NumberOfArticles (int32)

I have SP:

SELECT c.IdCategory
, c.Name AS CategoryName
, DisplayOrder
, COUNT(ac.IdArticle) AS NumberOfArticles

FROM

Category c

LEFT JOIN ArticleCategory ac ON c.IdCategory = ac.IdCategory

GROUP BY c.IdCategory, Name, DisplayOrder

ORDER BY DisplayOrder

I have this line in my code:

var returnValue = db.ExecuteStoredProcedure<cSiteMenu>( "SiteMenu_Select", null, null ).ToList( );

When I call it I get this error:

Mapping the result to instances of the class 'EZine.BO.cSiteMenu' failed because the
 required primary key column 'id_category' is not present.

[Ady]

Hello Marko,

 When you try to map the results of a stored procedure to a persistent type, db.ExecuteStoredProcedure<cSiteMenu>, the columns in the resultset returned by the stored procedure need to match the columns that make up the persistent type i.e cSiteMenu.
When you create a persistent type, OpenAccess creates a table and columns for it and generates default names for them. In this case the default primary key column name that is generated is 'id_category'. The stored procedure returns the column 'IdCategory' and hence there is a mismatch and hence the exception.
You can explicitly specify the column name for the primary key field IdCategory, using the Table editor or specify an alias name in the select query in the stored procedure so that the column names match.

Do get back in case you need further assistance.

All the best,
Ady
the Telerik team