Synchronize with SynchronizeMatchedAndFormula

[ulfrudalv]

I'm trying to use BulkSynchronize to synchronize some tables. The problem is that I don't have the entire tables in memory, only those rows that relates to a specific aggregate root. I would expect that SynchronizeMatchedAndFormula would be used during the delete phase of BulkSynchronization but it does not seem to do that. Now all other rows that don't belong to the aggregate root are deleted but I want all rows that don't match the AggregateRootID to be kept.

This is how I use SynchronizeMatchedAndFormula SynchronizeMatchedAndFormula = $"AggregateRootID = '{id}'";

[JonathanMagnan]

Hello @ulfrudalv ,

I believe you are looking for the options: ColumnSynchronizeDeleteKeySubsetExpression

That option will make the library perform the delete statement only on a range of rows and not the whole table.

Example

In this example, if we synchronize customer of Type = 1, only customer from Type = 1 that doesn't exist in the source list will be deleted. Other customer types will not be touched

context.BulkSynchronize(customers, options => {
    options.ColumnPrimaryKeyExpression = customer => customer.Name;
    options.ColumnSynchronizeDeleteKeySubsetExpression = customer => customer.Type;
});

Online Example: https://dotnetfiddle.net/y5snLt

Let me know if that's what you are looking for.

Best Regards,

Jon

[ulfrudalv]

Hello Jonathan, I forgot to mention that I use DataTable and not EF. Is it possible to use ColumnSynchronizeDeleteKeySubsetExpression in that case, if so how do I write the equivalent code?

Best Regards, Ulf

[JonathanMagnan]

That's a very good question!

It looks that doing it with a DataTable is currently impossible or way harder.

We will look at it and provide a solution very soon. At least, all the logic to handle this scenario is already coded so we just need to provide an easy way to make it works also with a DataTable

You should get an update next Monday about it.

[ulfrudalv]

Great! Looking forward to it, I think this will make a big performance improvement for us.

Best Regards, Ulf

[JonathanMagnan]

Hello @ulfrudalv ,

Sorry for the delay, we are still reviewing the best way to add all mappings are there is some missing.

However, we choose to release at least the v3.0.4 today with the option ColumnSynchronizeDeleteKeySubsetNames.

You can use it with a list of string which should be your DataTable column name

context.ColumnSynchronizeDeleteKeySubsetNames = new List<string>() {"Type"};

Let me know if you need some help to get you started.

Best Regards,

Jon

[ulfrudalv]

Thanks, I managed to get it to work. However, I had to handle an empty DataTable, by deleting the rows for the particular aggregate root.

Best Regards, Ulf

[JonathanMagnan]

What do you mean by "I had to handle an empty DataTable, by deleting the rows for the particular aggregate root.". Is there something we need to fix? Just to make sure ;)

[ulfrudalv]

If there are two records in table A in the database, one with Type = 1 and another with Type = 2. I specify context.ColumnSynchronizeDeleteKeySubsetNames = new List<string>() {"Type"}; but I can't specify that Type should equal 1. When I execute BulkSynchronize and there is no data in DataTable for table A I would like that the record in table A with Type = 1 to be deleted. Can this be solved without doing a separate delete against table A with Type = 1?

Best Regards, Ulf

[JonathanMagnan]

Hello @ulfrudalv ,

Unfortunately at this moment, there is no way.

We checked some different way but we found none that we are happy yet.

However, it could be possible to let you create the "SQL" used that perform the check. A little bit like the FormulaUpdate: https://dotnetfiddle.net/4R14Nx

So you will need to write the raw SQL for the join clause. Is it a good solution for you?

[ulfrudalv]

I'm happy with the solution I've right now where I do manual delete on the tables with empty DataTables. The performance improvement of using BulkSynchronize rather than delete insert is really great. Thanks Ulf