Adding Master / Detail Records using DALINQ

Data Abstract
1

I’m working on a sample project where I have three tables in play: user, company, and a “link” table that links users to companies. All three tables have a unique identifier (Oid) as the primary key. The “link” table has a column for the user’s Oid and a column for the company’s Oid. There’s a constraint that requires the user id to be in the user table and the company id to be in the company table when inserting a “link” record.

I log in my user account and fetch the Oid for the currently logged in user.
Then I create a new company record.
Then I create a new “link” record to connect that user to the newly created company.

When I call ApplyUpdates() an error is generated stating that Oid is not in the result set. However, if I call ApplyUpdates() after each insert everything works without a hitch.

Should I be able to insert both records then call ApplyUpdates() once?

This is not the only instance where I have such master/detail tables with constraints, foreign keys, etc.

I’m wanting to use the Briefcase mode for a mobile application and my concern is that records will be added to the briefcase then when I try to push the changes back up to the server then I’ll run into issues.

    private RemoteTableQuery<dbo_Company> companies = null;
    private RemoteTableQuery<dbo_BrechbuhlerUser> users = null;

    public ObservableCollection<Item> Items { get; set; }
    public Command LoadItemsCommand { get; set; }

    async Task<bool> AddToDatabase(Item item)
    {
        if (companies == null)
            return await Task.FromResult(false);

        dbo_Company company = new dbo_Company();

        company.id = Guid.NewGuid();
        company.Name = item.Text;
        company.DisplayName = item.Description;
        company.IsActive = false;

        module.DataAdapter.InsertRow<dbo_Company>(company);

        module.DataAdapter.ApplyChanges(); // Error occurs if I remove this line...

        dbo_CompanyCompanies_BrechbuhlerUserUsers link = new dbo_CompanyCompanies_BrechbuhlerUserUsers();

        link.OID = Guid.NewGuid();
        link.id = link.OID.ToString();
        link.OptimisticLockField = 0;
        link.Users = user.Oid;
        
        link.Companies = company.id;

        module.DataAdapter.InsertRow<dbo_CompanyCompanies_BrechbuhlerUserUsers>(link);

        module.DataAdapter.ApplyChanges();

        Trace.WriteLine(string.Format("{0} {1}", item.Text, item.Description));

        Items.Add(item);

        return await Task.FromResult(true);
    }
2

Hello

This issue happens because Data Abstract tries to insert data into the database in a wrong order. It tries to insert data into the dbo_CompanyCompanies_BrechbuhlerUserUsers table and then into the dbo_Company table. This violates the server’s consistency check and results in an data insertion error.

The solution of this issue is to somehow tell the Data Abstract engine that dbo_Company should be updated before other tables.

To do this open the server’s Schema. Go to the Update Rules group. Add a new Update Rule. Leave all fields as is. You need just set the Data Table name to dbo_Company and set proper update rule name.

Then start the server and you testcase should not fail anymore.

What happens here:

When the update call (.ApplyChanges) contains data for more than one table then server needs to process the received array of Delta instances . Unfortunately it might be not possible to process received Deltas in the order they are present in the incoming data due to the database constraints.
So server first check the Update Rules set in its Schema. If there are any rules then server first goes over these rules and checks if there were a Delta received for the table mentioned in the Update Rule. If there is such Delta then it is processed. Once all Update Rules were checked the rest of the still not processed Deltas is applied to the database.

Regards