I have local database where I insert some data and I want this data to be available also in cloud. As a lazy guy I don’t want to synchronize data manually. After setting up local and cloud databases I activated Azure Data Sync to just find out that it conflicts with EF7 insert and update queries. As I don’t have many tables and my database is pretty small I invented simple workaround.
Problem
First let’s see the SQL that EF7 generates when I update data and in same transaction new row is inserted to another table:
UPDATE [Product] SET [ShortDescription] = @p1
WHERE [Id] = @p0;
SELECT @@ROWCOUNT;
INSERT INTO [ProductField] ([BoolValue], [DoubleValue], [IntValue],
[ProductId], [ProductTypeFieldId], [StringValue], [Unit])
OUTPUT INSERTED.[Id]
VALUES (@p2, @p3, @p4, @p5, @p6, @p7, @p8);
If I introduce the database to Azure Data Sync client then it creates some triggers to every table that is synchronized. There are three triggers per synchronized table. One for insert, one for update and one for delete. Update trigger looks like this.
ALTER TRIGGER [dbo].[Product_dss_insert_trigger] ON [dbo].[Product] FOR INSERT AS
SET NOCOUNT ON
DECLARE @marker_create_scope_local_id INT
DECLARE @marker_scope_create_peer_timestamp BIGINT
DECLARE @marker_scope_create_peer_key INT
DECLARE @marker_local_create_peer_timestamp BIGINT
DECLARE @marker_local_create_peer_key INT
DECLARE @marker_state INT
SELECT TOP 1
@marker_create_scope_local_id = [provision_scope_local_id],
@marker_local_create_peer_timestamp = [provision_timestamp],
@marker_local_create_peer_key = [provision_local_peer_key],
@marker_scope_create_peer_timestamp = [provision_scope_peer_timestamp],
@marker_scope_create_peer_key = [provision_scope_peer_key],
@marker_state = [state]
FROM [DataSync].[provision_marker_dss]
WHERE [object_id] = 750625717
AND [owner_scope_local_id] = 0
MERGE [DataSync].[Product_dss_tracking] AS [target]
USING (SELECT [i].[Id] FROM INSERTED AS [i]) AS source([Id])
ON ([target].[Id] = [source].[Id])
WHEN MATCHED THEN
UPDATE SET [sync_row_is_tombstone] = 0,
[local_update_peer_key] = 0,
[restore_timestamp] = NULL,
[update_scope_local_id] = NULL, [last_change_datetime] = GETDATE()
WHEN NOT MATCHED THEN
INSERT (
[Id] , [create_scope_local_id], [scope_create_peer_key], [scope_create_peer_timestamp],
[local_create_peer_key], [local_create_peer_timestamp], [update_scope_local_id],
[local_update_peer_key], [sync_row_is_tombstone], [last_change_datetime],
[restore_timestamp])
VALUES (
[source].[Id],NULL, NULL, NULL, 0, CAST(@@DBTS AS BIGINT) + 1, NULL, 0, 0,
GETDATE() , NULL);
When insert occures in table with trigger we get the following exception:
SqlException: The target table ‘ProductField’ of the DML statement cannot have any enabled triggers if the statement contains an OUTPUT clause without INTO clause.
The problem is that EF7 uses OUTPUT clause and it conflicts with triggers.
Temporary workaround
As a temporary workaround I made a copy of my database and wrote some T-SQL that runs on SQL Server Agent. The point this script is simple: in transaction clear data from all tables in database copy and insert data from database that is used by EF7. This is not good solution for long run or for large databases as it may take time to copy rows and it also grows transaction logs big. But in smaller scales it works.
begin transaction
delete from ProductField
delete from Product
delete from Manufacturer
delete from Country
delete from ProductCategory
delete from ProductTypeField
delete from ProductType
set identity_insert ProductType on
insert into ProductType(Id, Name) select * from beerstore.dbo.ProductType
set identity_insert ProductType off
set identity_insert ProductTypeField on
insert into ProductTypeField(Id, Name, ProductTypeId, DataType, HasUnit,
DefaultUnit) select * from beerstore.dbo.ProductTypeField
set identity_insert ProductTypeField off
set identity_insert ProductCategory on
insert into ProductCategory(Id, Name, ParentId)
select * from beerstore.dbo.ProductCategory
set identity_insert ProductCategory off
set identity_insert Country on
insert into Country (Id, IsoCode, Name)
select * from beerstore.dbo.Country
set identity_insert Country off
set identity_insert Manufacturer on
insert into Manufacturer (Id, Name, CountryId)
select * from beerstore.dbo.Manufacturer
set identity_insert Manufacturer off
set identity_insert Product on
insert into Product (Id, CategoryId, Name, Unit, UnitPrice,
ProductTypeId, Description,
ShortDescription, ManufacturerId, HasImage)
select * from beerstore.dbo.Product
set identity_insert Product off
set identity_insert ProductField on
insert into ProductField (Id,BoolValue, DoubleValue,
IntValue, ProductId, StringValue,
ProductTypeFieldId, Unit)
select * from beerstore.dbo.ProductField
set identity_insert ProductField off
commit I call the script above also as poor man’s data sync. To get better idea about my solution take a look at the following image that visualizes the whole solution.
One my ask now why I don’t synchronize data with some script directly to SQL Azure database. The answer is simple – I want to stay with Azure Data Sync and when EF7 team will solve the problem (I think it’s EF7 because there are also other databases with triggers in this world) I can switch over to original database again with Azure Data Sync.
View Comments (2)
Did you try to set the max batch size to 1?
I later found out that I can also go with this but still I prefer to run EF7 without minimal involvement and I don't see any reason why mapper should conflict with something in database that is used by many systems. Anyway solution to triggers problem is coming with rc2 soon.