Using a view in LightSwitch and a way to set your primary key

Problem

LightSwitch infers all NON-Nullable fields from a SQL server view as part of the Primary Key in the entity.

In a business case where a lookup list of a limited set of records with Id and Name has to be used, the Entity Framework creates a read-only entity with an inferred Primary key that was defined on both Id and Name. However, only the Id is needed as Primary Key because the lookup view is to be used in combo boxes to filter screen data.

Solution

After reading some articles about this inferred primary key on all views, the solution seems to be to make the fields look like Nullable fields to the entity Framework. To do so you can modify your view and use the CONVERT function on the fields that do not need to be part of the Primary key.

Example

With AdventurWorks Database I create a view to be used in my LS app.

CREATE VIEW Production.LookupProductAssembly

AS

SELECT DISTINCT a.ProductID, a.Name

FROM Production.Product AS a

INNER JOIN Production.BillOfMaterials AS b

    ON a.ProductID = b.ProductAssemblyID

GO

Then use this view in LS by updating the data source and select the new view.

The Entity designer warns about the inferred key.

Click continue and a new entity is created, with both Id and name inferred as primary key.

This stands in the way of creating a relationship with some of the other entities.

As suggested in the solution, the view needs to be modified so that the Name filed gets interpreted as a nullable field and therefore not added to the inferred primary key.

ALTER VIEW Production.vLookupProductAssembly

AS

SELECT DISTINCT a.ProductID, CONVERT(nvarchar(50), a.Name) AS Name

FROM Production.Product AS a

INNER JOIN Production.BillOfMaterials AS b

    ON a.ProductID = b.ProductAssemblyID

GO

Update the datasource in Visual studio. Right click on the Data source name, select ‘Update Datasource’, click on finish in the dialog window.

The same warning pops up again, click continue. The entity will be redesigned, this time with the Name field as nullable (checkbox Required is unchecked).