How to sync part of the table

[ipavlovi]

We have already purchased license for Entity Framework Extensions. Now we are evaluating your other library, z.dapperplus bulk operations library and we have some questions regarding that library.

We have a list which we want to synchronize with the part of the table which satisfy condition, and do not want to change the other part of the table which does not satisfy the same condition.

How to recognize which rows are inserted/updated/deleted after each bulk operation.

[JonathanMagnan]

Hello @ipavlovi ,

To synchronize only a part of the table, you need the option: ColumnSynchronizeDeleteKeySubsetExpression

DapperPlusManager.Entity<Order>("Order_Synchronize").Table("Orders").Key(x => new { x.Number}).UseBulkOptions(x => x.ColumnSynchronizeDeleteKeySubsetExpression = c => new { c.CustomerID });

Fiddle: https://dotnetfiddle.net/fbFJLW

To know which value has been inserted/updated/deleted after each bulk operation, you can get this information using the Audit feature: https://dapper-plus.net/audit

Let me know if you need more help with your scenario.

Best Regards,

Jon

[DTIBeograd]

Hello Jon,

Seems that we have managed to get what is required, by using ColumnSynchronizeDeleteKeySubsetExpression.

However, how can we ignore null value error on insert in case we have default value set in DB? Is this the right way? options.IgnoreOnSynchronizeInsertExpression = x=> x.DateTimeColumn;

Thanks

[DTIBeograd]

Also, is there a way to cast AuditEntries to the original object?

[JonathanMagnan]

Hello @DTIBeograd ,

Using the option IgnoreOnSynchronizeInsertExpression is perfect for columns that you want to have updated but not inserted. So if I understand your scenario correctly, this is the option you need to use.

There is no way to cast the AuditEntries to the original object at this moment. I added this suggestion to our backlog as we have too much ongoing development.

Best Regards,

Jon

[DTIBeograd]

Hello,

Actually we are doing only delete/insert with synchronize, but since I'm passing object with DateTimeColumn with null value I'm getting an error on insert, unless I'm using IgnoreOnSynchronizeInsertExpression to ignore that column. Seems to work fine.

Got another question, having trouble with child object synchronize. Please take a look at the sample:

context.Entity<Parent>("parent").Table("parent").Key(p => new { p.keyPart1, p.keyPart2, p.keyPart3, p.keyPart4 }).UseBulkOptions(options =>
            {
                options.ColumnSynchronizeDeleteKeySubsetExpression = p => new { p.keyPart1, p.keyPart2 };
                options.IgnoreOnSynchronizeInsertExpression = p => p.CreatedOn;
                options.SynchronizeIgnoreUpdate = true;
                options.UseAudit = true;
                options.AuditEntries = auditEntries;
            }).AfterAction((kind, x) =>
            {
                if (kind == DapperPlusActionKind.Synchronize)
                {
                    x.Child.ParentIdentityKey = x.IdentityKey;
                    x.Child.keyPart1 = x.keyPart1;
                    x.Child.keyPart2 = x.keyPart2;
                }
            });

context.Entity<Child>("child").Table("child").Key(i => new { i.ParentIdentityKey, i.keyPart1, i.keyPart2 });

var result = context.BulkSynchronize("parent", parent).AlsoBulkSynchronize("child", p => p.child);

Getting an error: The duplicate key value is ..... on child table synchronize.

Also, Is there a way on synchronizing child table without requirements of defining ParentIdentityKey property into child class?

Just to add its not a list of child objects it is just single subobject.

public class Parent
{
public prop 1...
public prop 2...
public Child {get;set;}
}

public class Child
{
public prop 1...
public prop 2...
}

Thanks

[DTIBeograd]

Dear Jon,

We have managed to find a solution for problem above, there is only one left. When calling AlsoBulkSynchronize to sync child object, AlsoBulkSynchronize is not aware that parent is not synchronized(skipped since its already in database) and it is trying to sync child regardless. The reason we are getting an error is that child object is not getting and id of parent in that case. Can we somehow skip child object synchronizing if there is no action happening on parent? Or can we pass parent ID from database to the child if there is no action on parent object?

Thanks

[JonathanMagnan]

Hello @DTIBeograd ,

Could you try with the option options.ForceOutputFromUnmodifiedRow= true;

Since you skip the Update part, the IdentityKey is not returning. The value should now be returned by forcing to output also UnmodifiedRow. You might need to specify you want to output the IdentityKey but I do not believe that's needed.

Let me know if that is working.

Best Regards,

Jon

[DTIBeograd]

It does work, but as I have noticed it acts as Update action according to AuditEntry. Is there a way to do just BulkDelete with similar option as "IgnoreOnSynchronizeInsertExpression"

Thanks, Alek

[JonathanMagnan]

Hello @DTIBeograd ,

It does work, but as I have noticed it acts as Update action according to AuditEntry.

If you look at the SQL generated, it indeed performs an Update but on a variable and doesn't change any column values. That's the easiest solution for us as we need to know which identity should go to which ZZZ_Index (the position of your entity in the list you provided).

Let me know if that's clear or you need a better explanation.

Is there a way to do just BulkDelete with similar option as "IgnoreOnSynchronizeInsertExpression"

I do not understand the question or what you want to try to do here. Let me know more about it

[DTIBeograd]

I want to delete part of the table based on sub key and not to delete objects provided in the list, I have made a mistake in the last question instead of "IgnoreOnSynchronizeInsertExpression" I wanted to ask "Is there a way to do just BulkDelete with similar option as ColumnSynchronizeDeleteKeySubsetExpression" so basically BulkDelete to act as BulkSynchronize delete part.

Thanks

[JonathanMagnan]

Hello @DTIBeograd ,

Thank you for the additional information, the question makes way more sense ;)

I will look at it with my developer.

[JonathanMagnan]

Hello @DTIBeograd ,

My developer just told me that we have the option SynchronizeOnlyDelete = true

So only the Delete part from the synchronize will be done (Insert and Update will be skipped)

Is this option could work for you?

[DTIBeograd]

Yeah that would be fine too.

Thanks

[DTIBeograd]

Hello Jon,

Now I'm having problem mapping enum property to to ms sql column

.Map(x=>x.property, "propertyId")

property is an enum type

Getting an error: Exception: Missing Column : property On entity : EntityName On Table : [TableName]

Have no problems with other property mapping.

Thanks

[JonathanMagnan]

Hello @DTIBeograd ,

Do you think you could create a runnable project with the issue? It doesn’t need to be your project, just a new solution with the minimum code to reproduce the issue.

[DTIBeograd]

At some point ill try to make test app.

Got another question since I cannot find a lot of documentation regarding AllowDuplicateKeys option. If I have an list of objects position 1 object key 1 position 2 object key 2 position 3 object key 3 position 4 object key 1 and if AllowDuplicateKeys option is enabled, will last object at position 4 overwrite object at position 1?

Thanks

[JonathanMagnan]

Hello @DTIBeograd ,

Let's start to say that for Dapper Plus, this option is enabled by default.

The answer is a little bit complex. It will depend if the key is on the same batch or not. I will assume in my answer that a BulkUpdate has been done to make it easier to explain:

If a duplicate key is in the same batch

For every batch, only the last row of every unique key will be updated. So in this case, only rows at position 2, 3, 4 will be updated.

In other words, we will not overwrite the value of the entity in position 1 since we never updated it

if a duplicate key is a different batch

For every batch, only the last row of every unique key will be updated.

Since there are in different batches, that means we updated the database with value at the position 1, then when executing the next batch, we updated again the database but now with value at the position 4. So in this scenario, we indeed overrated value of position 1 by the value at position 4.

Why this behavior?

We were using before (and still if you need to output value) the MERGE statement. However, this statement requires to match a database row only once. So we always take the last unique key of a batch to sastify this behavior.

Let me know if everything is clear or you need a better explanation.

[DTIBeograd]

For BulkMerge i had to set AllowDuplicateKeys = true, so it was not default behavior, however, explanation is completely clear, thanks.

[JonathanMagnan]

Hello @DTIBeograd ,

You are right. I just double-checked the code and we have to remove this option by default a few months ago (was causing some issues with provider outside of SQL Server)

So you indeed need to enable it.