The member with identity 'component_type_name' does not exist in the metadata collection.\r\nParameter name: identity

Question

In the database I have a table called SERVICE. For this table I have triggers after INSER/UPDATE/DELETE. When I try to insert a record into the database using EF I get the error "The member with identity 'component_type_name' does not exist in the metadata collection.\r\nParameter name: identity". The column 'component_type_name' does not exist in SERVICE table but in table COMPONENT_TYPES_IN_SERVICE. This is the insert trigger for table SERVICE. When I remove the trigger from the table I dont have any problem with inserting.

CREATE TRIGGER [dbo].[UpdateComponentTypesInServiceOnInsert]
   ON [dbo].[SERVICE] 
   AFTER INSERT
AS 
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

DECLARE @componentID int;
SET @componentID = 0;

DECLARE @ComponentTypeID int;
SET @ComponentTypeID = 0;

DECLARE @ComponentTypeName nvarchar(255);
SET @ComponentTypeName = null;

SELECT @componentID = component_id from inserted;

SELECT @ComponentTypeID = c.component_type_id, @ComponentTypeName = ct.description from COMPONENTS c
inner join dbo.COMPONENT_TYPES ct on c.component_type_id = ct.id
WHERE c.id = @componentID; 

Select * from COMPONENT_TYPES_IN_SERVICE cts
where cts.id = @ComponentTypeID;

IF (@@ROWCOUNT > 0)
    BEGIN
        UPDATE COMPONENT_TYPES_IN_SERVICE
        SET number_of_components = number_of_components + 1
        WHERE id = @ComponentTypeID;
    END
ELSE
    BEGIN
        INSERT INTO COMPONENT_TYPES_IN_SERVICE(id, component_type_name, number_of_components)
        VALUES (@ComponentTypeID, @ComponentTypeName, 1); 
    END
END

Does any one knows any solution???

score 5 · Accepted Answer · answered Jun 04 '11 at 18:21

You cannot do this:

Select * from COMPONENT_TYPES_IN_SERVICE cts
where cts.id = @ComponentTypeID;

It returns COMPONENT_TYPES_IN_SERVICE record back to your application and EF tries to copy returned values into SERVICE because it thinks that you are passing back some database generated values.

score 3 · Answer 2 · answered Sep 01 '13 at 13:22

3

You cannot have any select statement that returns values in your trigger when using Entity Framework

answered Sep 01 '13 at 13:22

Serjik

10,543
8
61
70

score 1 · Answer 3 · answered Oct 15 '14 at 07:32

1

The exact cause of this unfamiliar error in most of the times is the existence of SELECT statements in triggers while using Entity Framework. Removing them would fix the error with a high probability.

answered Oct 15 '14 at 07:32

Mohsen Afshin

13,273
10
65
90

The member with identity 'component_type_name' does not exist in the metadata collection.\r\nParameter name: identity

3 Answers3