Entity Framework with Instead Of triggers

Question

I am using EF with a SQL Server database. I created a view and an Instead Of Insert trigger for that view which looks like this:

insert into Target (value, someFk) 
select value, 4 from inserted
select id from Target where @@ROWCOUNT > 0 and id = scope_identity()

I mapped the view into an EF edmx. When I try to add an entity I get the following exception when I call SaveChanges():

Unable to update the EntitySet 'TargetView' because it has a DefiningQuery and no element exists in the element to support the current operation.

The view has an identity column marked in the mapping.

Any suggestions?

score 3 · Accepted Answer · answered Aug 31 '12 at 14:24

3

If you open EDMX file with an xml editor, in the section where TargetView is defined you will have some xml similar to the following;

<EntitySet Name=".."  
           EntityType=".." 
           store:Type="Views" 
           store:Schema=".." 
           store:Name="..">
<DefiningQuery>SELECT ....</DefiningQuery>

You need to change this xml section in order to have CRUD operations;

<EntitySet Name=".."  
           EntityType=".."  
           store:Type="Tables" 
           Schema=".." />

answered Aug 31 '12 at 14:24

daryal

14,643
4
38
54

1

+1 see also this link http://smehrozalam.wordpress.com/2009/08/12/entity-framework-creating-a-model-using-views-instead-of-tables/ – StuartLC Aug 31 '12 at 14:25
@nonnb great article which describes alternative methods; thanks for sharing. – daryal Aug 31 '12 at 14:28
This works, but this is awfully wrong. Hope EF 5.0 will add some improvement here. – Jaster Sep 03 '12 at 09:26
@Jaster I do agree with you; EF is a headache when compared with other ORM tools like nhibernate. – daryal Sep 03 '12 at 13:03
nhibernate is not any better. For this issue it seems to do a better job. Compared to EF it has other limitations (auto generation/mapping, performance, etc). To be honest I do not like any OR mapper when it comes to serious database operations. – Jaster Sep 04 '12 at 12:01

Entity Framework with Instead Of triggers

1 Answers1

Linked