There are two issue resulting from SQL Server Views in VS LightSwitch 2011 inferred keys;
- Columns appearing as key that you do not want to be inferred as keys.
- 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:
- 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.
- Single item per page.
- 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.
