Intercepting save to set FK

Question

I have a DB with tables Sku and Account. An account can have multiple skus and in the Sku table I have the AccountId which is the FK on the Account table. I've set-up the relationships properly for the DB and when I view the Sku entity I see the Account field has been added, and in the Account entity I see the SkusCollection.

When I create a new Sku I get a Modal Window Picker for Account and this all works fine.

Now I want to create a login which limits things to a specific account. I know the AccountId and somehow later on I will link this to the login so that all queries, views edits will restrict Skus to the account which the login belongs to.

For experimentation I tried to intercept the "Inserting" event and override the Id using the following code:

partial void Skus_Inserting(Sku entity)
        {
            entity.Account.AccountId = 113;
        }

However this causes an error in that Account.AccountId id is read-only. I guess this is correct because this property is actually the PK of the account entity. What I actually want to do is set entity.AccountId but this is not available. In fact looking through all the entities the FKs have all been removed. So how do I set the FK for a new entity?

Also, when you click add and you get a popup for a new record, how do you configure the fields here? I've tried configuring the Account property of the Sku entity to not IsVisible but this still shows the account selector.

Basically i'm looking for guidance on how to set FKs in code and disable certain fields from view for given logins.

Thank you.

Neil

 

Answer 1

partial void Skus_Inserting(Sku entity)
        {
            entity.Account.AccountId = 113;
        }

However this causes an error in that Account.AccountId id is read-only. I guess this is correct because this property is actually the PK of the account entity. What I actually want to do is set entity.AccountId but this is not available. In fact looking through all the entities the FKs have all been removed. So how do I set the FK for a new entity?

Try this:

partial void Skus_Inserting(Sku entity)
{
  // use the _Single or _SingleOrDefault query 
  // for the Account entity type to return the Account 
  // with id 113
  Account newAccount = this.Accounts_Single(113);
  // now, associate the sku with account 113
  entity.Account = newAccount;
}

The syntax I used for the single query is wrong, but this should be close to the real code.  For every entity type in the system, we generate a query called EntityName_Single (and _SingleOrDefault) which takes whatever arguments are needed to compose the primary key of that entity.  If Account has an integer primary key, the appropriate method signature will be (int accountId). 

The Account_Single query should exist on the default application data service, and so i think there will be a way to grab it off the "this" reference when inside the save pipeline for that dataservice.

Basically, in the LS programming model, you associate the objects (Sku and Account) with each other, and LS/EF will do the FK updates  appropriately.

 

Answer 2

Thanks Matt. This worked fine. However, although I haven't proved it yet, it looks like this method will do an extra fetch for the account entity? So in my application if I assign an account to each login and then intercept all calls for saves this will always go and fetch the account entity before assigning the FK of the new/edited entity?

Perhaps this is a little wasteful? I assume this is something similar to how EF works? I have little/no experience with EF, but I have used alternative ORMs such as LLBLGen. How about if I keep a copy of the account entity in-memory and use this for subsequent add/save operations? I can see how this might be an issue if something in the account was to change, but the PK/FK is permanent so would this really matter anyway? I know that LLBLGen throws exceptions if the entity is out-of-date but I would like to know if something similar happens in LS?

Thanks.