Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

  1. Home
  2. LightSwitch: SQL Server View returns fewer records than expected.

LightSwitch: SQL Server View returns fewer records than expected.

Symptoms

In a LightSwitch project you have created a Screen that uses a SQL Server View as the data source. You find that, when you view the records at runtime, the number of records is fewer than the same view run from the SQL Server Management Studio.

↑ Back to the top

Cause

LightSwitch infers a Primary Key (PK) from the composite of the non-null columns of the View. At runtime the results do not include duplicate records, that is, the first of any records where the Composite Primary Key (CPK) match is displayed and the remainder are omitted.

↑ Back to the top

Resolution

When we use a view without a unique key value we return just the unique records. In order to return all records then we need to evaluate the data source and chose the columns that will provide the uniqueness.

Assuming that Field7 and Field8 need to be included to make each row unique, you would change the view to:

SELECT Field1, Field2, Field3, Field4,
ISNULL(Field5, ''), ISNULL(Field6, ''), Field7, Field8
FROM table(s)

↑ Back to the top

More Information

The functionality to override the automatically generated Composite Primary Key is not currently available and SQL Server does not provide a means to create a Primary Key in a View.

↑ Back to the top

Keywords: vkball, kb

↑ Back to the top

Article Info
Article ID : 2650500
Revision : 1
Created on : 1/8/2017
Published on : 12/16/2011
Exists online : False
Views : 55