How to programmatically modify a foreign key value in a Lightswitch entity

Problem

Sometimes the business requirement of an application is such that a foreign key value in a database record or set of records needs to be changed into another value automatically. E.g. in the AdventureWorks database a selected set of products has to be given a new product subcategory.

In principle this means that the value ProductSubCategoryyId in the Products table needs to be changed from value A to B. In a Lightswitch entity based on the database table, the value of the Id is hidden, instead the entity propagates an instance of the ProductSubCategory entity.

Solution

To change the value of the foreign key programmatically you need to change the SubCategory not the Id.

Example

Suppose we have an editable grid screen base on the AdventureWorks Products and the product that is currently selected needs to have another default SubCategory. A button on the Datagrid command bar has execute code behind to do so.

The trick is to create a new instance of the subcategory with the Id you want and then assign this new instance to the product in question. In the example we want to assign a subcategoryId with value 1 to the selected product.

partial void ModifySubCategory_Execute()

{

    int NewSubCategoryId = 1;

    ProductSubcategory s = DataWorkspace.AdventureWorksData.ProductSubcategories_SingleOrDefault(NewSubCategoryId);

    Product p1 = this.Products.SelectedItem;

    p1.ProductSubcategory = s;

}

Once the data will be saved the SubcategoryId value will be written into the product table.