0

We need to keep a specific table in sync between two databases. For this there is a background job in-place. The process is that EF core

  • queries all the table data in db1
  • maps the data into EF Core entities for db2
  • clears the existing rows in the table in db2 and finally
  • inserts the mapped entities into the table in db2

However, the table has relationships to other tables in db2. These reference to it via foreign keys. The FK's DeleteBehavior enum is set to Restrict.

Therefore, when in step #3 the existing records are to be deleted, there is an exception, since there are depending entities in other tables.

To work around this problem, the following procedure is invoked, temporarily disabling all the relationships constraints. Afterwards, meaning after deletion and re-insertion of the records, this is reversed.

This works fine, however I find this incredibly ugly. What are your takes on this? Can somebody make a better proposal?

Any advice or suggestions are greatly appreciated!

Here's the stored procedure to disable the FK constraints (source here)

DROP PROCEDURE [schema].[disableFks]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [schema].[disableFks]
AS
DECLARE @sql NVARCHAR(MAX) = N'';
with SCH as
(
    select SCHEMA_ID from sys.schemas where name in ('sch1', 'sch2', 'sch3')
)
, FKS AS 
(
    SELECT DISTINCT obj = QUOTENAME(OBJECT_SCHEMA_NAME(parent_object_id)) + '.' + QUOTENAME(OBJECT_NAME(parent_object_id)) 
    FROM sys.foreign_keys
    WHERE sys.foreign_keys.schema_id in (select schema_id from SCH)
)

SELECT @sql += N'ALTER TABLE ' + obj + ' NOCHECK CONSTRAINT ALL;' FROM FKS;

EXEC sp_executesql @sql;
GO
baouss
  • 1,312
  • 1
  • 22
  • 52
  • Why EF Core? EF Core not for ETL tasks. Ideally it should be GetRecords from DB1 and continuously insert into temporary table in DB2. Then using MERGE SQL correct destination table. Then no disabling is needed. If you post class model, I'll show how to do that via third-party extension, if you don't mind. – Svyatoslav Danyliv Sep 21 '22 at 13:54
  • Hi, we try to use EF Core for everything DB-related, which includes ETL. The main reason for this is that we need to remain in the MS ecosystem as far as possible. I'm afraid third party extension are not allowed here. – baouss Sep 21 '22 at 16:54

0 Answers0