Tuesday, April 03, 2012

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.


image


The Entity designer warns about the inferred key.


image


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


image


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).


image

8 comments:

M. Hammad said...

this approach will prevent the optimizer from using the index on the name field and may lead to poor performance in case of filtration on this field.

Jan D'Hondt said...

Yes, it will lead to poor performance. But that is not the point of this article. Unless the LightSwitch team develop a better approach for manually defining primary keys on views this is what I have found that works.

Anonymous said...

Nice solution Jan, One other thing this will also resolve is that you make changes to Views and sometimes fields part of Primary Keys get removed and this causes your screens to remove Data Sources added without any warning which caused me a lot of pain.

Thanks,
Ray

Leslie Lim said...

Wow. Awesome article. Please do more articles like this in the future. Very informational and knowledgeable. I will expect more from you in the future. For now i will just bookmark your page and surely I'm gonna come back later to read more. Thank you to the writer!


Rica
www.imarksweb.org

ninest123 said...

louis vuitton outlet, sac longchamp, ralph lauren pas cher, replica watches, louboutin outlet, louboutin shoes, christian louboutin outlet, prada outlet, ugg boots, longchamp pas cher, tiffany and co, louis vuitton, louboutin, air jordan pas cher, tory burch outlet, cheap oakley sunglasses, nike outlet, polo ralph lauren outlet, ray ban sunglasses, polo ralph lauren outlet, louis vuitton outlet, nike air max, replica watches, louis vuitton, air max, longchamp outlet, michael kors, oakley sunglasses, chanel handbags, nike free, nike roshe run, oakley sunglasses, burberry, tiffany jewelry, ray ban sunglasses, kate spade outlet, prada handbags, oakley sunglasses, nike air max, louis vuitton, louboutin pas cher, uggs on sale, ray ban sunglasses, oakley sunglasses, longchamp, jordan shoes, gucci outlet, ugg boots, nike free, longchamp outlet

ninest123 said...

vanessa bruno, coach outlet, nike free run uk, michael kors, michael kors outlet, coach outlet, replica handbags, new balance pas cher, michael kors outlet, hollister pas cher, michael kors, converse pas cher, nike air max, tn pas cher, ray ban pas cher, michael kors, abercrombie and fitch, lululemon, ralph lauren uk, vans pas cher, michael kors, michael kors outlet, mulberry, nike air max, kate spade handbags, nike air max, hermes, ugg boots, hollister, nike roshe, lacoste pas cher, oakley pas cher, nike blazer, ugg boots, true religion jeans, michael kors outlet, sac guess, air force, true religion outlet, hogan, ray ban uk, coach purses, timberland, north face, burberry, michael kors outlet, true religion jeans, burberry outlet online, north face, true religion jeans

ninest123 said...

lancel, celine handbags, jimmy choo shoes, bottega veneta, nike roshe, asics running shoes, gucci, giuseppe zanotti, nike huarache, mcm handbags, herve leger, hollister, hollister, new balance, ray ban, p90x workout, soccer shoes, babyliss, longchamp, mac cosmetics, nike trainers, chi flat iron, mont blanc, vans, vans shoes, ghd, iphone cases, nike air max, hollister, ferragamo shoes, ralph lauren, louboutin, nike air max, beats by dre, valentino shoes, converse outlet, lululemon, north face outlet, instyler, soccer jerseys, birkin bag, insanity workout, baseball bats, north face outlet, abercrombie and fitch, timberland boots, reebok shoes, nfl jerseys, oakley, wedding dresses

ninest123 said...

hollister, louis vuitton, canada goose outlet, moncler, pandora jewelry, swarovski, louis vuitton, bottes ugg, coach outlet, moncler, supra shoes, montre pas cher, moncler, ugg,uggs,uggs canada, ugg,ugg australia,ugg italia, canada goose, links of london, pandora jewelry, karen millen, doudoune canada goose, juicy couture outlet, moncler, pandora charms, marc jacobs, swarovski crystal, moncler, ugg pas cher, thomas sabo, louis vuitton, moncler outlet, moncler, canada goose, canada goose uk, canada goose outlet, ugg boots uk, juicy couture outlet, wedding dresses, moncler, canada goose, toms shoes, louis vuitton, replica watches, sac louis vuitton pas cher, pandora charms, canada goose