Using SQL Server View in Visual Studio LightSwitch 2011

There are two issue resulting from SQL Server Views in VS LightSwitch 2011 inferred keys;

1. Columns appearing as key that you do not want  to be inferred as keys.
2. Forcing columns to be keys that are not inferred as keys.

You will see the results of these issues with SQL Server Views in different ways for me there were two forms:

1. Strange Paging on child grids, you may see less row than are set on the pager i.e. page size was set to 10 but on pages you see 5, 6, 7 rows or more.
2. Single item per page.
3. Single item returned when there should be many.

Removing Inferred Columns

Figure 1 – View from Northwind

As you can see in Figure 1 view from the Northwind database as imported in to LightSwitch you can see there are two columns that may cause you problems.

To take these out of the inferred key on this view we can apply a CAST see below:

CAST(dbo.Products.Discontinued AS BIT)
CAST(dbo.Categories.CategoryName AS NVARCHAR(15))

Figure 2 – Adding CAST to Column

So when we “Update Datasource” we get what we see in Figure 3

Figure 3 – View after “Update Datasource”

Now we have remove the two offending columns from the inferred keys.

Forcing Columns to be Inferred

Here we need to do the opposite and the function we use for that is ISNULL (Transact-SQL) for this sample we will add the Supplier and Customer ID’s to the inferred keys, here the replacement value I am using is 0 because the two columns are INT but if a column was NVARCHAR(50) then the cast would be ISNULL(TextColumn, N‘’)

ISNULL(dbo.Products.SupplierID, 0)
ISNULL(dbo.Products.CategoryID, 0)

In Figure 4 we can see the result;

Figure 4 – After Including the Supplier and Customer IDs in the Inferred Keys

Conclusion

This way you can for the keys you want to used in your view, there will be a small hit in the queries for using either of these techniques in your queries but hopefully not too much of a hit.

Note: These view may or may not have issues, but I have had various problems in LightSwitch relating both issues and used one the two techniques to override the default behaviour.

Note: Also I did not come up with these methods they are scattered around the LightSwitch Forums but for my own use and easy reference I have added them here.