Using LightSwitch Office Integration Pack with Complicated Table Relationships

Introduction

This tip provides some tricks for using the office integration extension (http://officeintegration.codeplex.com/) for LightSwitch 2011 with many-to-many relationship tables.

Background

When working on a client's project, the client wants a table of "products for sell". For each "products for sell" entry, there is a collection of "ingredients" used to make the product. So each "products" can have many "ingredients" and each "ingredient" can also have many "products." This require a Many-To-Many relationship to link both tables. However, LightSwitch cannot create a direct Many-To-Many relationship. To overcome this, I've followed the suggestions by Andy Kung on MSDN (here) and create a Many-To-Many relationship. But when I try to export a Word document of a "product" entry using office integration extension, the direct Export method seems not to work correctly.

Scenario Detail

As suggested above, I've followed the suggestion on MSDN and created a Many-To-Many relationship of two tables by using a third table act as a junction. The table diagram is shown below:

So when accessing "a product's ingredients," I am actually accessing the "combined" table rather than the "product" table. This would work fine in LightSwitch but not when using the office integration extension.

Using the Code

What I want is to export a product detail document which contains the product's information and the ingredient's used for making this product.

First, I generate a document object using GenerateDocument method, like this:

dynamic doc = OfficeIntegration.Word.GenerateDocument(OutputDocumentPath, this.Product, MapDetails);   

Where we specify which column we want to export in MapDetails, this will fill out the product information like product name or product ID. For more information on how to map columns, please visit http://officeintegration.codeplex.com/.

Then I have a table bookmarked "IngredientsTable" in a Word file and I want the program to fill out the table for me using the "ingredient" information in the "combined" table, I tried the following code which is in the sample application from the website.

Word.Export(doc, "IngredientsTable", 2, false, this.Combineds, MapTable);

Where I specify which information to map to which column in Word using MapTable.

However, everytime I run this, it generate the documents using information in "products" table and throw a stack overflow exception when trying to fill out the table.

So I approach in a different direction by first selecting the "combineds" collection in a "product" with the following code:

var ProductCombineds = from temp in this.Combineds select temp;

Then change the code for filling out the table to:

Word.Export(doc, "IngredientsTable", 2, false, ProductCombineds, MapTable);

This time, the program did not throw stack overflow exception, but the problem became "how to access the ingredients information in ProductCombineds?" So I created a delegate to return the ingredient name.

Func<int, string> formatIngredientName = (int CombinedItemID) =>
    {
        Combined[] cbnd = (from temp in this.DataWorkspace.ApplicationData.Combineds 
	where temp.Id == CombinedItemID select temp).Cast<Combined>().ToArray();
        return cbnd[0].Ingredent.Name;
    };

The reason I use delegate is because the format function in office integration pack uses delegates.

Basically this function selects the item from ApplicationData.Combineds, casts it as an array and since there is only one item, returns the ingredient name of the first element will do the trick. So in MapTable, all we need to do is add the following mapping:

MapTable.Add(new OfficeIntegration.ColumnMapping
("INGREDIENT NAME", "Id", FormatDelegate: formatIngredientName));

This mapping will map the column INGREDIENT NAME in the Word table with the value of a "combined" entry's Id and format it using the delegate function which returns the name. So the result is the program filling out the name of the ingredient in the table.

Conclusion

With the tricks above, we can use the powerful functions of the office integration extension to output any information we want. This can also work in many other cases involving complicated relationships between tables. For example, outputting a collection of OrderItems in an Order entry where the OrderItems' names are stored in a different table called Inventory. In this case, direct output of Inventory entry from Order will not be available due to the restrictions of the relationship. However, the above tricks will resolve this problem.

Points of Interest

An interesting point is that when the stack overflow exception is thrown, since it's thrown by mscorlib.dll, there is no information provided by Visual Studio which is pretty frustrating. Since there is no disassembly information, all I can do is assume that the relationship between the tables is simply too complicated for the method to output and try to simplify the information.