Feb
7
Written by:
kchristo
Tuesday, February 7, 2012
In previous posts I made reference to views (DB or RIA) as an alternative (amongst other well-known potentials of views) to search and sort against fields that would normally be relations (lookups or however you want to call them).
From the very beginning I had an issue with SQL Server views imported to LS. LightSwitch had a very strange way to define the primary keys of the views. These “inferred” primary keys are most of the time very complex combined keys. And this is not an issue actually until you decide to add a relationship between this view (which 99% of the times has only one field as actual primary key) and another table. Then you realize this, otherwise benign, complex primary key is an issue (to say the least).
It was only recently, in this thread, after Yann Duran’s suggested reading (always to the rescue and always having something useful to contribute), that I managed to both understand how LS “infers” the Primary Key for a View and realize how one can “force” a primary key.
The bottom line is that LS decides that all not-nullable fields will be part of the inferred primary key. Not very inspired but I must admit I cannot think of anything better. So, how one can force the primary key one wants? The way is re-introducing all you not-nullable, non-key fields by cast/convert to the same data-type (or another one if needed). This way the definition of the “table”/”view” imported by LS contains all, originally not nullable fields, as nullable.
Very short example. Imagine Table Person as
Id int not null, PK
LastName nvarchar(50) not null,
FirstName nvarchar(50) null,
.
.
.
Your view’s SQL would be
select Id, CONVERT(nvarchar(50), LastName) as LastName, FirstName
from Person
Simple, dummy example just to display what I mean.
I feel I am re-inventing the wheel as this information was already out there, but in Greece we say “Επανάληψη μήτηρ μαθήσεως” or “Repetition is the mother of Learning” or something like that. Looks like we never learned much from this anyways…
P.S. After Yann's suggestion I have to note that actually this inferred primary key mechanism is Entity Framwork's native and not LightSwitch own behaviour. Although I had that in my mind I didn't make it clear here. I apologize for any missunderstanding and I thank Yann once more for the comment.