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.
