Saving References with Foreign Key Constraint

ServiceStack.OrmLite MySql
1

We are currently using ServiceStack 5.1.1

We are having difficulty saving references while also populating the parent id. Take the following class:

    [Alias("Branches")]
    public class Branch
    {
        [AutoId]
        public Guid Id { get; set; }

        [Reference]
        public Address Address { get; set; }

        [ForeignKey(typeof(Address), OnDelete = "CASCADE")]
        public Guid AddressId { get; set; }
    }

If we call db.SaveAsync(branch, references: true) a constraint exception is thrown because the AddressId is not populated. However, if we call db.SaveAllReferencesAsync(branch) the Address reference is added as expected and the AddressId is populated accordingly. But, the Branch.Id is not auto populated. If we simply add the Branch.Id in code prior to calling SaveAllReferencesAsync everything works as expected.

How can we maintain our foreign key constraint, save references AND have the parent id auto populated?

We appreciate your help

2

Can you please provide a small stand-alone repro of the issue.

3

References_ForeignKey_Constraint repro

Please NOTE: The connection string will need to be updated accordingly.

Let me know if you need more.

4

Just looking at your source code, you need to call await after each async API:

await db.SaveAllReferencesAsync(branch);

Let me know if that resolves the issue.

5

I apologize for the discrepancy. Our production code is using the asynchronous operations. The test code I provided is not. It was merely a typo. Nevertheless, both sync and async operations behave the same.

If you change

SaveAllReferencesAsync

to

SaveAllReferences

The behavior endures.

6

SaveAllReferences() is only for saving the references of the parent entity (Branch) that already exists and because you’re using an [AutoId] it needs to be able to be populated from the database so the parent entity needs to be saved first but you can’t do that with the non-null Foreign Key constraint using a self-reference which requires the reference to be saved first.

So basically you need to make the Guid? AddressId non nullable so the parent entity can be saved first, then you can use:

db.Save(branch, references: true)

To save the parent entity and references.

7

That resolved our issue.

Thank you sir.

1 Like
8

I may have spoken too soon.

How can we achieve this same behavior but maintain the NOT NULL constraint on the AddressId column?

It appears that our only course of action is to simply save all of the references ‘first’ and then assign the foreign keys accordingly prior to saving the parent record. This is what I was hoping

db.Save(branch, references: true)

would do.

Any clarity you could provide would be appreciated.

Thanks again

9

A Self Reference Key can’t be null for the reasons mentioned above. You would need to move the Reference Id to the Address table, i.e:

[Alias("Branches")]
public class Branch
{
    [AutoId]
    public Guid Id { get; set; }

    [Reference]
    public Address Address { get; set; }
}

[Alias("Addresses")]
public class Address
{
    [AutoId]
    public Guid Id { get; set; }

    [Required]
    public Guid BranchId { get; set; }

    [Required]
    public string StreetAddress { get; set; }

    [Required]
    public string City { get; set; }

    [Required]
    public string State { get; set; }

    [Required]
    public string ZipCode { get; set; }
}
10

Understood

Thank you