Stored Procedure Mapping

EF : deux nouveaux posts de l'ADO .Net Team

Matthieu MEZIL — Fri, 28 Mar 2008 11:53:41 GMT

L'ADO .Net Team vient de poster deux nouveaux posts : le premier concerne l'utilisation des procédures

Stored Procedure Mapping Overview

Hot Topics — Sat, 29 Mar 2008 18:43:27 GMT

Noam Ben-Ami, from the EF Tools team shows how to use the Entity Framework Designer tools to map Store

re: Stored Procedure Mapping

Tony — Tue, 01 Apr 2008 03:54:29 GMT

After attempting a similar example on my own and I get the following exception:

Error 2027: If an entity set or association set includes a function mapping, all related entity and association sets in the entity container must also define function mappings. The following sets require function mappings: Report2Queries.

THe Report2Queries is an 1 to many association between a Report and its queries.

re: Stored Procedure Mapping

Noam Ben-Ami — Tue, 01 Apr 2008 20:45:48 GMT

Tony,

I assume that you have a report entity and a query entity, and that a report can contain many queries. I also assume that the query table has a foreign key to the report entity, something like reportId. Finally, I assume that your query entity has a navigation property, probably called report, which means that the insert, update, and delete stored procedures must have a parameter called reportId, as detailed in the blog entry.

Given all of that, you need to map report.reportId on the query type to the reportId parameter on the insert, update, and delete stored procedures.

re: Stored Procedure Mapping

Roman Dynnik — Wed, 16 Apr 2008 15:18:07 GMT

>>>The value of the id column from the stored procedure’s return table will now be mapped back to the entity...

Good article, thanks!

I have some quetions:

1. Is it possible map back to the entity a out(inout)-parameter from stored procedure?

2. Is it possible map to a parameter of stored procedure any variable or property from other object (for example, I need to pass a ticketID)

re: Stored Procedure Mapping

Andrey Balykov — Wed, 23 Apr 2008 10:24:04 GMT

I've a problem with using Stored proc in Entity Framework Model.

For simple Example, I've 2 tables (and Entities), Prices (PriceID primary key, CategoryID, price) and Category (CategoryID primary key, CategoryName). I've relation FK_Prices_Category

I was made EDM with stored proc - all OK. But I can't make Deleting prom Price entity.

My code:

01 VmzkoPricesEntities ctx = new VmzkoPricesEntities();

02 VmzkoPricesModel.Prices pr = ctx.GetPriceByID(newsID).First<VmzkoPricesModel.Prices>();

03 ctx.DeleteObject(pr);

04 ctx.SaveChanges(true);

GetPriceByID - is an imported stored proc with output of all fields of Prices (PriceID, CategoryID, price) BUT if I expand 'pr' object (row 02) then property Category is NULL.

After that deleting is not available: Entities in 'VmzkoPricesEntities.Prices' participate in the 'FK_Prices_Category' relationship. 0 related 'Category' were found. Between 1 and 1 'Category' are expected.

How can I fill Category property inside Prices object by using stored proc???

How can I fill that by alternative method?

re: Stored Procedure Mapping

Arye Friedman — Mon, 19 May 2008 17:12:47 GMT

Hi Noam,

Do you plan to implement a wizard that will be able to port (not just map) stored procedures with a non-trivial logic to LINQ code? The purpose is to run these converted stored procedures using EF approach against another data source which does not contain any logic but just data.

[Entity Framework] Error “AssociationSets must be consistently mapped for all operations”. Mapear asociaciones en Store Procedure de Delete

Sun, 24 Aug 2008 00:47:28 GMT

Con un proyecto donde estoy probando Entity Framework , para ir mas alla de lo simple a lo que estamos

re: Stored Procedure Mapping

Skeptic — Wed, 15 Oct 2008 23:14:25 GMT

I stopped reading after "We hope to provide a solution for this issue in future versions of the Entity Framework..." because this told me everything I need to know: EF is "not ready for prime time".

I look forward to revisiting EF once it becomes realistic enough to use out of the box without a lot of "magic work-around coding" that requires a PHD in Computer Sciene or worse, breaks when the next edition of EF comes out.

re: Stored Procedure Mapping

richardcollette — Thu, 30 Oct 2008 21:36:09 GMT

Definitely frustrated by limitations myself as well. I simply want to get a collection of read only entities from a table (a list of US States) using a stored procedure. Issues are:

I must map the entity to a store table or view, even when updates are not going to be performed.

The entity must map all primary keys to the store table, even though I only require a portion of the table values returned.

Clearly the read only use case was not focused on in the design much. As a result, EF seems to tie my data access to the physical store more so than ever.

It's interesting that this is so poor because it works fairly well in Linq to SQL except that I cannot specify a name for the return type or its properties. I have to live with the autogenerated return type.

Please take this use case into account in the next release.

Entity Framework:entity client 调用存储过程

davin — Sat, 20 Dec 2008 19:14:21 GMT

如果对于sp在EDM的映射有一些了解的话，可以很清楚的明白，在EDM中映射一个sp返回的类型包括：Entity Type，Scalars Type,None；这三中类型分别对应的sp返回内容对应到数据库，表的层面就是：（Entity）一张表的所有字段或若干字段(必须包括主键）;（Scalars）返回一个常量（int, varchar）可以是数据库的一个字段，可以是sum,count,left...等函数操作的结果;(none)自然是什么都不返回.这里自然就有一个严重的问题，对于返回（multiple

re: Stored Procedure Mapping

Margaret — Tue, 30 Dec 2008 16:08:01 GMT

I got a similar error 2027 to Tony, and provided mappings for the association set. However, in my case, the relationship is 1 to 0..1 and when I mapped the Key of the dependent table to the PK of the independent table in all three Function mappings (as instructed), I got a slightly different error:

Error 2027: If an EntitySet or AssociationSet includes a function mapping, all related entity and AssociationSets in the EntityContainer must also define function mappings.

It went on to say that the Independent table required function mappings, even though up to now I was able to save changes using the regular .SaveChanges Method. Is this because of the 1 to 0..1 relationship?

re: Stored Procedure Mapping

D.Asuncion — Tue, 20 Jan 2009 06:07:19 GMT

Olahhhh.. Amigo... I Cannot Run.. I got some error.. like this :

error 2037: A mapping function bindings specifies a function EDMXModel.Store.Order_Insert but does not map the following function parameters: productId.

5 Ado.Net Team Reference.App_Code.Model.msl(61,6) : error 2048: The EntitySet 'Order' includes function mappings for AssociationSet 'FK_Order_Product', but none exists in element 'InsertFunction' for type 'EDMXModel.Order'. AssociationSets must be consistently mapped for all operations.

re: Stored Procedure Mapping

D.Asuncion — Tue, 20 Jan 2009 08:29:13 GMT

I fixed it now... Thank you very much..for the article.

re: Stored Procedure Mapping

David J. — Sat, 24 Jan 2009 03:25:08 GMT

I've started today working on an ADO.NET entity model using stored procedures, and I have to say I'm underwhelmed. Some of the serious limitations:

1) Cannot map output parameters from the insert sproc back to entity properties - only the return value. If you have an existing sproc that returns the identity value as an output parameter, guess what - you'll have to rewrite it. If you're procedure passes back multiple output parameters, then you're screwed - you'll have to rethink your design.

2) If you choose sproc mapping you MUST provide an insert, update and delete procedure. Never mind if you don't intent to support all of those semantics. I found a bug reported on this. The fix was postponed, and to paraphrase the workaround provided: "If you're not doing full CRUD, then don't use EF." I guess a model where there are some entities that don't actually support full CRUD semantics is beyond the EF team's comprehension.

3) You can apparently only have one procedure per operation. So let's say you want multiple delete procedures - one to delete a specific order, one to delete all orders for a customer, etc. There's apparently no way to do that unless you cram it all into one sproc.

4) While futzing with these limitation I had to iterate on DB changes to workaround them and the entity designer got confused several times. After mucking with the raw XML and not getting anywhere, I regenerated the project from scratch and it worked fine. This doesn't give me much confidence in the ability of the designer to cope with an evolving data model.

re: Stored Procedure Mapping

jason chan — Wed, 29 Apr 2009 03:12:25 GMT

what is the point of mapping all the stored procedures with all the insert/update/delete?

what if i don't do it? what will happen?

re: Stored Procedure Mapping

jason chan — Wed, 29 Apr 2009 03:27:59 GMT

i just tried removing the stored procedure mapping for delete function with the delete stored procedure, so the code will stop at this line

foreach(Product p in context1.Product) {

context1.DeleteObject(p)

}

it will say:

Mapping Exception was unhandled

Schema specified is not valid. Errors: 2025

So, I think if you do all the stored procedure mapping with all your entities...then you can just use all the add, delete, update easier.

re: Stored Procedure Mapping

jason chan — Wed, 29 Apr 2009 03:33:46 GMT

i tried to remove the stored procedure mapping for insert function on product...

so i will get the same error (mapping exception was unhandled) from this line

foreach(Product p in context1.Product)

so, I must do all the mapping before I can use the above code?

re: Stored Procedure Mapping

Kreshiv — Sat, 12 Sep 2009 01:28:22 GMT

Thanks Man. I was searching for the meaning of this error for 2 days..

Hope I can try and resolve it now.