BulkMerge using ColumnPrimaryKeyExpression and related entities

[scottiemc7]

Description

If I have a parent/child relationship:

class Parent
{
    [Key]
    public Guid Id { get; set; }

    public string ExternalId { get; set; }
    public virtual ICollection<Child> Children { get; set; }
}

class Child
{
    [Key]
    public Guid Id { get; set; }
    public string Value { get; set; }

    public Guid TheParentId { get; set; }
    [ForeignKey("TheParentId")]
    public virtual Parent TheParent { get; set; }
}

where I don't know the parent key and I try to merge into an empty database, BulkMerge works great.

await dbContext.BulkMergeAsync(parentRecords, options =>
{
    options.IncludeGraph = true;
    options.IncludeGraphOperationBuilder = operation =>
    {
        if (operation is BulkOperation<Child> childOperation)
        {
            childOperation.IgnoreOnMergeUpdateExpression = c => new { c.Id, c.TheParentId };
        }
        else if (operation is BulkOperation<Parent> parentOperation)
        {
            parentOperation.ColumnPrimaryKeyExpression = p => new { p.ExternalId };
            parentOperation.IgnoreOnMergeUpdateExpression = p => new { p.Id };
        }
    };
});

However, when I try to add more children to a parent that already exists in the database I get a foreign key error. I'm assuming this is because the child is just using the parent key I assigned it, which obviously isn't correct. In this scenario, I don't know what it is and depend on the ColumnPrimaryKeyExpression lookup.

The workaround is to look up every parent record and assign the proper id. I'd like to avoid this if possible. Is there another way?

I thought that the ForceOutputFromUnmodifiedRow property (found here) sounded promising, but it's not documented and the tooltip says it's only for MSSQL at the moment. Thank you!

Further technical details

• EF version: 6.4
• EF Extensions version: 6.14.3
• Database Provider: MySQL

[JonathanMagnan]

Hello @scottiemc7 ,

Unfortunately, we currently don't have any solution for this issue for MySql.

The problem is there is no column to "Input", so the "UPDATE" part is skipped which at the same time cause no "OUTPUT". So trying for output like this will not work either: parentOperation.ColumnOutputExpression = p => new { p.Id };

One easy solution would be to add a dummy column that will be updated on when the UPDATE will be executed, but that's sure something not everybody wants to have in their production environment.

The other solution I think of is the one you proposed by retrieving and assigning the Id

Best Regards,

Jon

[scottiemc7]

@JonathanMagnan - thank you. After some more investigation based on your input, here's what I found:

• If I set parentOperation.ColumnOutputExpression = p => new { p.Id };, the above scenario actually does work and the children are inserted into the table as expected. However, for some reason this seems to break updates for both the parent and child.
• I do have an UpdatedDT column on the parent that I can set for every BulkMerge, so that sounds like an acceptable solution. Unfortunately, when I tried this I kept getting the same foreign key exception.

I can provide a test project if that would help, just let me know. Thanks.

[JonathanMagnan]

Hello @scottiemc7 ,

Sure, a test project will surely help as I had a different behavior than you, so perhaps something is missing in my lab.

You can send it in private here if needed: [email protected]

[scottiemc7]

No problem. I updated the public project that I was sharing before. Let me know if you need anything else. Thanks

https://bitbucket.org/visiondatabasesystems/efe-testing/src/main/

[JonathanMagnan]

Hello @scottiemc7 ,

Sorry for the delay,

From what my developer told me is that there is currently no problem, the UPDATE will be executed.

However, the issue is in the WHERE method.

In the database, a DateTime column is probably used. However, the precision of a DateTime column is really bad, people often prefer using a DateTime2 however they can for better precision.

So even if you insert a specific value in the database, this value might be somewhat rounded, and you cannot retrieve your row using the original datetime.

Here is some additional information about both type: https://stackoverflow.com/a/1334193/5619143

Let me know if that could explain the current behavior or my developer missed the point.