EF4: ObjectContext inconsistent when inserting into a view with triggers

Question

I get an Invalid Operation Exception when inserting records in a View that uses “Instead of” triggers in SQL Server with ADO.NET Entity Framework 4. The error message says:

{"The changes to the database were committed successfully, but an error occurred while updating the object context. The ObjectContext might be in an inconsistent state. Inner exception message: The key-value pairs that define an EntityKey cannot be null or empty. Parameter name: record"}

@ at System.Data.Objects.ObjectContext.SaveChanges(SaveOptions options)
  at System.Data.Objects.ObjectContext.SaveChanges()

In this simplified example I created two tables, Contacts and Employers, and one view Contacts_x_Employers which allows me to insert or retrieve rows into/from these two tables at once. The Tables only have a Name and an ID attributes and the view is based on a join of both:

CREATE VIEW [dbo].[Contacts_x_Employers]
AS
SELECT dbo.Contacts.ContactName, dbo.Employers.EmployerName
FROM dbo.Contacts INNER JOIN dbo.Employers 
ON dbo.Contacts.EmployerID = dbo.Employers.EmployerID

And has this trigger:

Create TRIGGER C_x_E_Inserts
   ON  Contacts_x_Employers
   INSTEAD of INSERT
AS 
BEGIN
    SET NOCOUNT ON;

    insert into Employers (EmployerName)
    select i.EmployerName 
        from inserted i
    where  not i.EmployerName in 
    (select EmployerName from Employers)

    insert into Contacts (ContactName, EmployerID)
    select i.ContactName, e.EmployerID 
    from inserted i inner join employers e
    on i.EmployerName = e.EmployerName;

END
GO

The .NET Code follows:

using (var Context = new TriggersTestEntities()) { Contacts_x_Employers CE1 = new Contacts_x_Employers(); CE1.ContactName = "J"; CE1.EmployerName = "T"; Contacts_x_Employers CE2 = new Contacts_x_Employers(); CE1.ContactName = "W"; CE1.EmployerName = "C"; Context.Contacts_x_Employers.AddObject(CE1); Context.Contacts_x_Employers.AddObject(CE2); Context.SaveChanges(); // line with error }

 

SSDL and CSDL (the view nodes):

<EntityType Name="Contacts_x_Employers">
   <Key>
    <PropertyRef Name="ContactName" />
<PropertyRef Name="EmployerName" />
   </Key>
<Property Name="ContactName" Type="varchar" Nullable="false" MaxLength="50" />
<Property Name="EmployerName" Type="varchar" Nullable="false" MaxLength="50" />
 </EntityType>

<EntityType Name="Contacts_x_Employers">
  <Key>
     <PropertyRef Name="ContactName" />
     <PropertyRef Name="EmployerName" />
   </Key>
   <Property Name="ContactName" Type="String" Nullable="false" MaxLength="50" Unicode="false" FixedLength="false" />
   <Property Name="EmployerName" Type="String" Nullable="false" MaxLength="50" Unicode="false" FixedLength="false" />
</EntityType>

The Visual Studio solution and the SQL Scripts to re-create the whole application can be found in the TestViewTrggers.zip at ftp://JulioSantos.com/files/TriggerBug/. I appreciate any assistance that can be provided. I already spent days working on this problem.

Answer 1

I stumbled on the same problem when I tried to insert a row in a view with "instead of insert" and "instead of update" triggers.

I think I found a solution: when visual studio's wizard drop your view in your model, it add a StoreGeneratedPattern="Identity" on some properties (probably the keys of your entity).

When generating requests on a regular table, this property tells entity framework to expect an ID in return, so it append a select scope_identity() at the end of the insert.

Now with updatable views the scope_identity is screwed because the insert happen in another scope and it returns null, so the insert fail.

If you remove this StoreGeneratedPattern="Identity" from the model, entity framework doesn't append select scope_identity() and the insert is working fine.

I hope this solve your problem and that it doesn't come too late.

Cheers

More details here : http://social.msdn.microsoft.com/Forums/en-US/adodotnetentityframework/thread/9fe80b08-0b67-4163-9cb0-41dee5115148/