2

I've created a View from a table in another database. I have dbo rights to the databases so viewing and updating is not a problem. This particular View did not have an "id" column. So I added one to the View by using ROW_NUMBER. Now I had a problem with a table, in the same database, not showing up in LightSwitch but that was solved by changing the id column to be NOT NULL. I haven't done any real manipulation in LightSwitch. I'm still in the Import Your Data Source stage (ie. very beginning).

This View, in LightSwitch, is going to be read-only. No updating or deleting. From what I've read, LightSwitch needs a way to determine the PK of a Table or View. It either reads it from the schema (column set as a PK) or finds a column set as NOT NULL and uses that as the PK. Well I can't seem to do either of those things in SQL Server or LightSwitch, so I am stuck as to how to get LightSwitch to "see" my View.

dotnetN00b
  • 5,021
  • 13
  • 62
  • 95
  • You could do with reading [Writing the perfect question](https://msmvps.com/blogs/jon_skeet/archive/2010/08/29/writing-the-perfect-question.aspx). – Oded Mar 27 '12 at 18:46
  • Well, [after seeing this link on fixing the problem](http://social.msdn.microsoft.com/Forums/en-US/lightswitch/thread/64534e95-ae07-4b8d-9064-f4deadae6438), I'll wait to see if there is a better solution. – dotnetN00b Mar 27 '12 at 19:53

2 Answers2

2

for lightswitch to see your view you must have a primary key on a column of the table your are selecting from. Example:

create table tbl_test
(
id int identity primary key not null,
value varchar(50)
)

create view vw_test
as
select *
from tbl_test

note:sometimes when you edit the primary key column in the view select statement it may cause lightswitch to not see it

Example:

create view vw_test
 select cast(id as varchar(50) id,...

lightswitch would not see the table

Hope this was helpful! :)

har07
  • 88,338
  • 12
  • 84
  • 137
stephan
  • 21
  • 1
  • 1
    Dumb Tool still adds definition as "The key has been inferred and the definition was created as a read-only table/view". REALLY ANNOYING - hasn't Microsoft heard of INSTEAD OF Triggers to handle updates through a View - it is 2013, not the dark ages! – SAinCA Apr 06 '13 at 00:45
0

What I do in this case is create a view with an ID column equal to the row number. Ensure the column you're basing the ID on is not null using the isnull() or coalesce() functions.

Example:

create view as
select distinct ID = row_number() over (order by isnull(Name,'')), 
Name = isnull(Name,'')
from My_Table
FistOfFury
  • 6,735
  • 7
  • 49
  • 57