Entity Framework 6 uses an UPDATE when it should be using a DELETE when deleting from a bound DataGridView

Question

I have a DataGridView bound to a BindingSource that is bound to DataMember tbl_Distribution_Orders_Restriction of DataSource bs_tbl_Series_Manufacturer, which is itself a BindingSource that has a DataSource linked to an entity, ForeNET.tbl_Series_Manufacturer. The DataGridView in question displays only records related to the current record of bs_tbl_Series_Manufacturer.

tbl_Distribution_Orders_Restriction has the following definition:

CREATE TABLE [Fore].[tbl_Distribution_Orders_Restriction](
    [GM_ORDER_NBR] [varchar](50) NOT NULL,
    [Include] [bit] NOT NULL,
    [AUS_SRS_CD] [varchar](2) NULL,
    [ManufacturerID] [tinyint] NULL,
    [CNTLG_DLR_CD] [varchar](6) NULL,
    [FAWCode] [varchar](15) NULL,
 CONSTRAINT [PK_tbl_Distribution_Orders_Restriction_1] PRIMARY KEY CLUSTERED 
([GM_ORDER_NBR] ASC) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

ALTER TABLE [Fore].[tbl_Distribution_Orders_Restriction]  WITH CHECK ADD  CONSTRAINT [FK_tbl_Distribution_Orders_Restriction_tbl_Dealer] FOREIGN KEY([CNTLG_DLR_CD])
REFERENCES [Fore].[tbl_Dealer] ([cntlg_dlr_cde])

ALTER TABLE [Fore].[tbl_Distribution_Orders_Restriction] CHECK CONSTRAINT [FK_tbl_Distribution_Orders_Restriction_tbl_Dealer]

ALTER TABLE [Fore].[tbl_Distribution_Orders_Restriction]  WITH CHECK ADD  CONSTRAINT [FK_tbl_Distribution_Orders_Restriction_tbl_ModelCodes] FOREIGN KEY([FAWCode])
REFERENCES [Fore].[tbl_ModelCodes] ([FAWCode])

ALTER TABLE [Fore].[tbl_Distribution_Orders_Restriction] CHECK CONSTRAINT [FK_tbl_Distribution_Orders_Restriction_tbl_ModelCodes]

ALTER TABLE [Fore].[tbl_Distribution_Orders_Restriction]  WITH CHECK ADD  CONSTRAINT [FK_tbl_Distribution_Orders_Restriction_tbl_Series_Manufacturer] FOREIGN KEY([AUS_SRS_CD], [ManufacturerID])
REFERENCES [Fore].[tbl_Series_Manufacturer] ([aus_series_cde], [ManufacturerID])

ALTER TABLE [Fore].[tbl_Distribution_Orders_Restriction] CHECK CONSTRAINT [FK_tbl_Distribution_Orders_Restriction_tbl_Series_Manufacturer]

ALTER TABLE [Fore].[tbl_Distribution_Orders_Restriction] ADD  CONSTRAINT [DF_tbl_Distribution_Orders_Restriction_Include]  DEFAULT ((0)) FOR [Include]

CREATE TRIGGER [Fore].[trg_I_tbl_Distribution_Orders_Restriction] 
   ON  [Fore].[tbl_Distribution_Orders_Restriction]
   INSTEAD OF INSERT
AS 
BEGIN
    SET NOCOUNT ON
    INSERT INTO Fore.tbl_Distribution_Orders_Restriction (GM_ORDER_NBR, [Include], AUS_SRS_CD, ManufacturerID, CNTLG_DLR_CD, FAWCode)
    SELECT      inserted.GM_ORDER_NBR, inserted.Include, Fore.qry_SOM_OrderInfo.AUS_SRS_CD, Fore.tbl_ModelCodes.ManufacturerID, 
                      Fore.qry_SOM_OrderInfo.CNTLG_DLR_CD, Fore.qry_SOM_OrderInfo.FAWCode
    FROM        inserted INNER JOIN
                      Fore.qry_SOM_OrderInfo WITH (NOEXPAND) ON inserted.GM_ORDER_NBR = Fore.qry_SOM_OrderInfo.GM_ORDER_NBR INNER JOIN
                      Fore.tbl_ModelCodes ON Fore.qry_SOM_OrderInfo.FAWCode = Fore.tbl_ModelCodes.FAWCode
END

CREATE TRIGGER [Fore].[trg_U_tbl_Distribution_Orders_Restriction] 
   ON  [Fore].[tbl_Distribution_Orders_Restriction]
   INSTEAD OF UPDATE
AS 
BEGIN
    SET NOCOUNT ON
        DELETE Fore.tbl_Distribution_Orders_Restriction
    FROM deleted INNER JOIN Fore.tbl_Distribution_Orders_Restriction ON Fore.tbl_Distribution_Orders_Restriction.GM_ORDER_NBR=deleted.GM_ORDER_NBR AND Fore.tbl_Distribution_Orders_Restriction.[Include]=deleted.[Include]
INSERT INTO Fore.tbl_Distribution_Orders_Restriction (GM_ORDER_NBR, [Include], AUS_SRS_CD, ManufacturerID, CNTLG_DLR_CD, FAWCode)
    SELECT  inserted.GM_ORDER_NBR, inserted.Include, qry_SOM_OrderInfo.AUS_SRS_CD, Fore.tbl_ModelCodes.ManufacturerID, Fore.qry_SOM_OrderInfo.CNTLG_DLR_CD, Fore.qry_SOM_OrderInfo.FAWCode
    FROM    inserted INNER JOIN Fore.qry_SOM_OrderInfo WITH (NOEXPAND) 
                ON inserted.GM_ORDER_NBR = Fore.qry_SOM_OrderInfo.GM_ORDER_NBR INNER JOIN
                      Fore.tbl_ModelCodes ON Fore.qry_SOM_OrderInfo.FAWCode = Fore.tbl_ModelCodes.FAWCode
END

My problem is this:

When I delete a row from the DataGridView, the row disappears as expected. At that point, my event code calls Context.SaveChanges() However, the row is not deleted from the database, and the next time I open the form, all the records that I thought I had deleted are still there. No error messages are displayed at any point.

I bound Context.Database.Log to the debug window, and found that when I perform a delete through the DataGridView, results similar to the following log output are being generated on executing Context.SaveChanges():

UPDATE [Fore].[tbl_Distribution_Orders_Restriction]
SET [AUS_SRS_CD] = NULL, [ManufacturerID] = NULL
WHERE ([GM_ORDER_NBR] = @0)

-- @0: '6W2CAA' (Type = AnsiString, Size = 50)

-- Executing at 12/02/2014 8:15:51 AM +11:00

-- Completed in 142 ms with result: 1

The end result of this SQL statement is that no changes at all are made to the '6W2CAA' record in [Fore].[tbl_Distribution_Orders_Restriction], since that table has triggers that populate fields [AUS_SRS_CD] and [ManufacturerID] (amongst others) based on the [GM_ORDER_NBR] value, the former fields existing only to obviate the need for an unacceptably slow stored procedure that must look up these values from a number of other tables - it is more acceptable for the lookup load to be shifted to the SQL Server on insert/update.

However, even if these triggers did not exist, in the event of a user deleting then inserting records with the same [GM_ORDER_NBR] value, an error would occur since a record with the same (primary key) [GM_ORDER_NBR] value would still exist, albeit with NULL [AUS_SRS_CD] and [ManufacturerID] values.

I would have expected that the SQL statement executed by Entity Framework after a delete from the DataGridView would be more like: DELETE FROM [Fore].[tbl_Distribution_Orders_Restriction] WHERE ([GM_ORDER_NBR] = @0)

When I .Remove an entity in event code, followed by .SaveChanges, the invalid UPDATE still occurs first, followed by a DELETE.

How can I get Entity Framework to execute the correct DELETE SQL statement in response to a DataGridView deletion, instead of (rather than in addition to) a wholly inappropriate UPDATE?

Answer 1

Entity Framework does not issue "Delete" for relations, your DataGridView is bound to an Entity Collection not an Entity Set.

When an entity is removed from EntityCollection, Entity Framework will only delete its association or relation which is identified by a foreign Key. Entity Framework does not know whether to delete it from database or not.

Consider an entity with multiple foreign keys.

 Table Products
    PK ProductID
    FK CategoryID (nullable)
    FK VendorID (nullable)

Assuming Product is related to Category table and Vendors table. Now when you are removing a product from one category, it is not necessary that it should be deleted from table itself. Because some products may have category and some may not have, similarly some may have vendors defined and some may not.

This is the reason EF does not delete actual record, instead it just sets its foreign key column to null. Which removes an entity from its associated related table, but entity is still present.

If foreign keys are not nullable, then old version of EF used to throw an error. I don't know about the new one yet.

Theoretically, in case of nullable foreign keys, Child Entity can exist without its parent, and in case of non nullable foreign keys, Child Entity can not exist without its parent, in this case relation should be defined correctly. And I had overriden a SaveChanges method to identify non nullable foreign keys and delete the record accordingly if it's associated parent is set to null.

Answer

You can bind DGV to Entity Set of tbl_Distribution_Orders_Restriction and set Condition/Predicate for ForeginKey = Manufacturer DGV's SelectedItem.PrimaryKey