The Anatomy of a LightSwitch Application Part 4 – Data Access and Storage

Ref: LightSwitch.

In prior posts I’ve covered the LightSwitch presentation tier and the logic tier. The presentation tier is primarily responsible for human interaction with the application using data from a LightSwitch logic tier. The logic tier comprises one or more LightSwitch data services whose primary job is to process client requests to read and write data from external data stores. This post explores how the logic tier access data and what the supported data storage services are.

The following diagram shows the relevant pieces of each tier that participate in data access.

Figure 1: LightSwitch Tiers

In Visual Studio LightSwitch, when you define a data source by creating a new table or connecting to an external data source, LightSwitch creates a corresponding data service and configures its data provider. For application-defined data, LightSwitch creates a special data source called “ApplicationData” (sometimes referred to as the intrinsic data source). In addition to creating a data service for the intrinsic data source, LightSwitch also creates and publishes the SQL database.

Figure 2 below shows a LightSwitch application with two data sources: the intrinsic “ApplicationData” and an attached “NorthwindData”. LightSwitch will create the corresponding “ApplicationDataService” and “NorthwindDataService” and will publish the database for the intrinsic database.

Figure 2: Data Sources in Project Explorer

Each data service has a data provider that corresponds to the kind of data storage service. LightSwitch supplies the data provider for Microsoft SQL Server and for Microsoft SharePoint, but others can be plugged in to support other data storage services. The following table summarizes the supported data providers and data storage services in LightSwitch 1.0.

Table 1: Supported Data Access Providers

Data Storage Service	Data Provider	Supported Features	Not Supported
Microsoft SQL Server SQL Server 2005 SQL Server 2008 SQL Server 2008 R2 including Express versions	SqlClient for the Entity Framework	Referential integrity Transactions SQL authentication Integrated Windows authentication	SQL Server Compact Stored Procedures
Microsoft SQL Azure	SqlClient for the Entity Framework	Referential integrity Transactions SQL authentication	Stored Procedures Integrated Windows authentication Azure Federated authentication
Microsoft SharePoint SharePoint 2010	OData client DataServiceContext	Lists Relationships	Attachments Transactions
Other (RIA)	Requires a custom WCF RIA DomainService..	Default queries Parameterized queries Transactions‡	Entity operations Custom operations Complex types Query “Includes”

‡ Provider specific.

So let’s begin our tour of data access and storage in LightSwitch. We’ll cover the following areas.

• LightSwitch Data Types
• Using Microsoft SQL Server and SQL Azure
• Using Microsoft SharePoint
• Using a WCF RIA DomainService as a Data Adapter
• Using an Alternate Entity Framework Provider

LightSwitch Data Types

Before we look at the various data providers that you can use with LightSwitch, it will help to understand the data types that LightSwitch supports. In the sections following, we will see how LightSwitch maps data types from other data source and data access providers to the built-in LightSwitch data types.

Table 2 below lists the simple data types that LightSwitch supports. The data types shown in italic fonts (Date, EmailAddress, Image, PhoneNumber) are referred to as semantic types. These do not represent a distinct value type, but provide specific formatting, visualization and validation for an existing simple data type. The set of simple data types is fixed but the set of semantic types is open for extensibility.

Table 2: LightSwitch Simple Data Types

LightSwitch Type	VB Type	C# Type	Range	Remarks
Binary	Byte()	byte[]	Each byte is 0 to 255	Variable length array of bytes; MaxLength specifies the maximum number of bytes
Boolean	Boolean	bool	True or False
Byte	Byte	byte	0 to 255
Date	Date	DateTime	Jan 1, 0001 AD (CE) to Dec 31, 9999 AD (CE)	A DateTime treated as date only; LightSwitch truncates any time portion
DateTime	Date	DateTime	00:00:00 Jan 1, 0001 AD (CE) to 23:59:59 Dec 31, 9999 AD (CE)
Decimal	Decimal	decimal	±1.0e−28 to ±7.9e28	Fixed decimal point value with 28-29 significant digits; suitable for financial and monetary values; stored with specific precision and scale
Double	Double	double	±5.0e−324 to ±1.7e308	Floating decimal point with 15-16 digits precision; suitable for scientific numbers
EmailAddress	String	string		A String treated as an email address
Guid	Guid	Guid	{00000000-0000-0000-0000-000000000000} to {FFFFFFFF-FFFF-FFFF-FFFF-FFFFFFFFFFFF}	A 128-bit integer used as a unique ID
Image	Byte()	byte[]		A Binary treated as an image
Int16	Short	short	-32,768 to 32,767	A signed 16-bit integer
Int32	Integer	int	-2,147,483,648 to 2,147,483,647	A signed 32-bit integer
Int64	Long	long	-9,223,372,036,854,775,808 to 9,223,372,036,854,775,807	A signed 64-bit integer
Money	Decimal	decimal		A Decimal treated as a monetary value
PhoneNumber	String	string		A String treated as a phone number
SByte	SByte	sbyte	-128 to 127	A signed 8-bit integer
Single	Single	float	±1.5e−45 to ±3.4e38	Floating decimal point with 7 digits precision
String	String	string	A sequence of zero or more Unicode characters	A variable length character string; MaxLength specifies the maximum number of characters
TimeSpan	TimeSpan	TimeSpan	±10675199.02:48:05.4775807	A time interval in days, hours, minutes, seconds, and fractions of a second When stored in SQL the range is 00:00:00.0000000 to 23:59:59.9999999

Nullable Data Types

Each of the LightSwitch simple and semantic data types has a corresponding nullable data type. The data type is represented in the LightSwitch modeling language as datatype?. In the design experience, the q-mark notation is not displayed. Instead a “Required” property indicates a non-nullable value. In code, LightSwitch uses the corresponding nullable VB and C# data types, for example "Integer?" or "int?". The one exception is that both required and non-required string values are represented as the CLR String type. If a string is required, LightSwitch validates that it is non-null and non-empty.

Using Microsoft SQL Server and SQL Azure

This section applies to data accessed from SQL Server, SQL Server Express, or SQL Azure, including the intrinsic “ApplicationData” database created for a LightSwitch application. When you publish a LightSwitch application, you specify the connection string that will be used to access data in SQL Server or SQL Azure. Your production SQL connection string should have the following properties set. (See ConnectionString Property for more information.) 

Keyword	Value	Remarks
Data Source	“ServerName”	The name of the SQL Server, SQL Express instance, or “tcp:” address of the SQL Azure database
Initial Catalog	“DatabaseName”	The SQL database name
User ID	“SQLLoginName”	The SQL Server login account
Password	“SQLLoginPassword”	The SQL Server login password
Integrated Security	False	Integrated security is not supported with SQL Azure.
Pooling	True	Enables connection pooling (True recommended)
Connect Timeout	30	Timeout in seconds to wait for a connection to the server
User Instance	False	Don’t redirect SQL Express to a user instance (False recommended)
Encrypt	True	Use SSL encryption for data sent between SQL Server and LightSwitch (True recommended)
TrustServerCertificate	False	Bypass SSL certificate verification (False recommended)

Unsupported Features

LightSwitch does not support accessing SQL stored procedures. The LightSwitch designer ignores all other database objects that may be configured such as triggers, UDFs or SQL CLR. (It is possible to create a custom data adapter that accesses SQL stored procedures. This is covered below in “Using a WCF RIA DomainService as a Data Adapter”.)

LightSwitch does not support certain SQL data types as noted in the table below. When attaching to an external data source, LightSwitch omits columns with unsupported data types.

SQL Data Type Mapping

When you attach to an existing SQL Server or SQL Azure, LightSwitch maps the SQL column types to LightSwitch data types according to the following table. A non-nullable SQL type is mapped to a LightSwitch “Required” data type. LightSwitch also imports the length, precision and scale of certain data types.

Table 3: SQL Data Type Mapping

Imported Column Type	LightSwitch Type	Supported Attributes
binary(n)	Binary	Required, MaxLength=n
image	Binary	Required, MaxLength=Max
timestamp	Binary	Required, MaxLength=8
varbinary(n)	Binary	Required, MaxLength=n
bit	Boolean	Required
tinyint	Byte	Required
date	Date	Required
datetime	DateTime	Required
datetime2(n)	DateTime	Required
smalldatetime	DateTime	Required
decimal(p,s)	Decimal	Required, Precision=p, Scale=s
money	Decimal	Required, Precision=19, Scale=4
numeric(p,s)	Decimal	Required, Precision=p, Scale=s
smallmoney	Decimal	Required, Precision=10, Scale=4
float	Double	Required
uniqueidentifier	Guid	Required
smallint	Int16	Required
int	Int32	Required
bigint	Int64	Required
real	Single	Required
char(n)	String	Required, MaxLength=n
nchar(n)	String	Required, MaxLength=n
ntext	String	Required
nvarchar(n)	String	Required, MaxLength=n
text	String	Required
varchar(n)	String	Required, MaxLength=n
xml	String	Required, MaxLength=Max
time(n)	TimeSpan	Required
datetimeoffset	not supported
geography	not supported
geometry	not supported
hierarchyid	not supported
sql_variant	not supported

SQL Data Provider

The LightSwitch logic tier uses the Entity Framework data access provider for SQL Server. In the data service implementation, LightSwitch uses an Entity Framework ObjectContext to move entity instances to and from SQL Server. The LightSwitch developer doesn’t see the Entity Framework objects or API directly. He writes the logic tier business logic in terms of the LightSwitch Entity and Data Workspace API—without concern for the underlying data access mechanism.

LightSwitch supports transactions over SQL Client. The default isolation level for query operations is IsolationLevel.ReadCommitted. The default isolation level for updates is IsolationLevel.RepeatableRead. LightSwitch doesn’t enlist in a distributed transaction by default. You can control that behavior by creating an ambient transaction scope; if available, LightSwitch will compose with it. For more details, see Transaction Management under the Logic Tier architecture post.

Generating the Intrinsic SQL Database

As we’ve noted above, a LightSwitch application has a default application database called “Application Data“. This database provides the data storage for entity types defined by your LightSwitch application – as opposed to entity types that are imported from an external storage service.

At design-time, LightSwitch uses a local SQL Server Express database on the developer’s machine for the intrinsic database. In production, LightSwitch uses Microsoft SQL Server Express, Microsoft SQL Server or Microsoft SQL Azure for the application database. Runtime data access is the same as for any other attached SQL Server or SQL Azure database, as described above.

LightSwitch deploys the intrinsic database schema when you publish the application. It will also attempt to upgrade an existing database schema when you upgrade the application.

Note: LightSwitch does not deploy design-time or test data to production. It just deploys the schema. If you have requirements to publish data along with your application, you’ll need to do that as a first-time install or first-time run step.

SQL Schema Generation

LightSwitch maintains a model of the application data entities and relationships. From this model it generates a database storage schema that can be used to create tables and relationships in SQL Server or SQL Azure.

When you use the “Create Table” command in the LightSwitch designer, you are actually defining an entity type and a corresponding entity set. From the entity set, LightSwitch infers the database table and columns. The column types are mapped from the entity properties according to the following table.

Table 4: Generated SQL Column Types

LightSwitch Type	Supported Attributes	Generated SQL Column Type
Binary	Required, MaxLength=n	varbinary(n) [null]
Boolean	Required	bit [null]
Date	Required	date [null]
DateTime	Required	datetime [null]
Decimal, Money	Required, Precision=p, Scale=s	decimal(p,s) [null]
Double	Required	float [null]
Int16	Required	smallint [null]
Int32	Required	int [null]
Int64	Required	bigint [null]
String, PhoneNumber, EmailAddress	Required, MaxLength=n	nvarchar(n) [null]

The following LightSwitch data types are not supported when defining entity properties on the intrinsic database: Byte, Guid, SByte, Single, and TimeSpan. These were omitted to keep the design experience simple. They are supported, however, when attaching to an external database.

A LightSwitch “Required” property translates into a SQL “NOT NULL” column.

The LightSwitch designer handles keys and foreign keys for entity relationships. For every entity type, it creates an auto-increment primary key property named “Id” of type Int32. LightSwitch also automatically creates hidden foreign-key fields for entity properties that reference [1] entity or [0..1] entities. These are generated as SQL columns with an appropriate foreign-key constraint.

In general, there is full fidelity in converting data between the LightSwitch data types and the SQL column types. The one exception is DateTime. LightSwitch chose to generate DateTime properties as SQL datetime instead of as datetime2 so that our generated SQL would be compatible with SQL Server 2005. This means that the range and precision of date/time values that LightSwitch handles (via the System.DateTime type) is greater than what the SQL column can store. See SQL date and time types for more details. This is only a problem if your application needs to deal with dates prior to January 1, 1753, which isn’t typical for most business applications.

Schema Versioning

After you’ve built and deployed your Application Data for the first time, you may need to make updates to the application and to its data schema. LightSwitch allows you to deploy over the top of an existing Application Database – to a degree. At publish time LightSwitch will read the schema of the existing published database and attempt to deploy just the differences, for example adding tables, relationships, and adding or altering columns.

There are a number of changes that LightSwitch will not deploy because the changes could result in data loss. Here are some of the breaking changes that could be made in the LightSwitch Entity designer that could result in being unable to deploy the schema updates.

• Renaming an entity property
• Changing an entity property’s data type to something that is incompatible
• Rearranging the order of entity properties
• Adding a required property
• Adding a 1-many relationship
• Adding a 1-0..1 relationship
• Changing the multiplicity of a relationship (however changing from 1-many to 0..1-many is allowed)
• Adding a unique index

 LightSwitch doesn’t prevent you from making a breaking change during iterative development. In some cases you may be warned that a change may cause data loss for your design-time test data. If you don’t mind losing your test data, you can accept the warning and make the change. But beware that if you’ve already deployed the data schema once, such a change may prevent you from successfully deploying a schema update because the scripts that LightSwitch uses to update the data schema will fail and roll-back if the potential for data loss is detected.

There are also a certain schema changes that may cause data loss but are intentionally allowed. These include:

• Deleting an entity property. The column will be dropped and any existing data will be lost. 
• Deleting an entity. Any foreign-key constraints will be dropped, the table will be dropped, and any existing data will be lost.
• Deleting a relationship. Any existing foreign-keys and foreign-key constraints will be removed. There is the potential for data loss.

The Membership Database

If you use Windows Authentication or Form Authentication in your application, LightSwitch will also deploy the necessary SQL tables to store membership information for users and roles. These tables are included with the intrinsic database. Internally, LightSwitch uses the ASP.NET SQL Membership provider which requires these tables to be present.

• dbo.aspnet_Applications
• dbo.aspnet_Membership
• dbo.aspnet_Profile
• dbo.aspnet_Roles
• dbo.aspnet_SchemaVersions
• dbo.aspnet_Users
• dbo.aspnet_UsersInRoles

In addition to Users and Roles, LightSwitch applications have the concept of Permissions. Permissions are defined statically per-application in the LightSwitch application model (LSML). LightSwitch associates Permissions with Roles. A Role acts as an administrative grouping for Permissions. LightSwitch creates one additional table called RolePermissions to track this association.

• dbo.RolePermissions

Post deployment, you can add and remove users, add and remove roles, associate users with roles, and assign permissions to roles. A user gets all the permissions that are associated with the roles that he or she is in.

Design-time Data

During F5, LightSwitch generates a temporary SQL Server Express database to hold test data. This physical database is not deployed with the application, nor is any of the data that is entered during design-time.

LightSwitch attempts to maintain this data between F5 iterations, even as you make changes to the shape of the data. In cases where a data schema change cannot be performed on the design-time database without data loss, LightSwitch will warn you. If you accept the warnings, it will delete the test database and start from scratch with an empty database.

Using SharePoint Lists

LightSwitch allows you to access lists on a SharePoint 2010 site as tabular data.

Unsupported Features

LightSwitch only supports SharePoint 2010 and higher—those versions that support exposing OData. LightSwitch does not support managing SharePoint attachments. All other data types are supported, but there is limited support for displaying or editing some of them, as noted below.

SharePoint Data Type Mapping

LightSwitch maps column types from SharePoint lists to LightSwitch data types according to the following table.

Table 5: SharePoint Data Type Mapping

SharePoint List Column Type	LightSwitch Type	Remarks
Yes/No	Boolean
Date & Time	DateTime
Date Only	DateTime	A Date Only column is not imported as Date type, but can be changed to Date in the designer.
Currency	Double
Number	Double
Id	Int32	Id columns are imported as read-only and hidden.
Choice Menu	String	LightSwitch imports the set of choice values specified in SharePoint.
Hyperlink	String	No built-in Hyperlink control to view the Url
Multiple Lines of Text	String	No built-in HTML viewer control to view or edit the data
Picture	String	No built-in Picture viewer control to display the public Url
Single Line of Text	String	LightSwitch imports the max length if one is specified in SharePoint.
Attachment	not supported

A SharePoint Lookup column maps to an entity relationship in LightSwitch.

SharePoint Data Provider

All LightSwitch data services use an Entity Framework ObjectContext to manage data in the server data workspace. When there is no Entity Framework data provider available, LightSwitch uses a disconnected ObjectContext and builds its own shim later to move entities in and out of the ObjectContext via an alternate date access provider.

In the case of SharePoint 2010, there is no Entity Framework provider. LightSwitch has an internal adapter that talks to SharePoint via its OData feed, using an instance of System.DataServices.Client.DataServiceContext. Under the hood, LightSwitch does all the necessary query translation and entity type marshaling to go between the EF ObjectContext and the OData DataServiceContext.

The Entity Framework ObjectContext requires entity keys and foreign-keys to manage entity relationships. OData doesn’t handle entity relationships in the same way and does not naturally provide the foreign-key data. LightSwitch compensates for this by traversing the entity relationships to get to the necessary related entity keys and auto-populates the foreign-keys. This is a bit of extra work in the logic tier, but results in a consistent data access model and API.

Using a WCF RIA DomainService as a Data Adapter

LightSwitch lets you plug in a custom data adapter for cases where no other data provider technology is available. Because it requires writing some custom code and entity classes, it works best for custom in-house scenarios or for access to public services that always have the same data schema.

Rather than defining our own protocol for the data adapter, we noted that WCF RIA Services had already done a great job of defining an object that supports query and update to user-defined entity types: the DomainService. LightSwitch uses the same DomainService class as an in-memory adapter—without necessarily exposing it as a public WCF service endpoint.

The solution involves writing a custom DomainService and referencing the DLL from the LightSwitch project. LightSwitch uses .NET reflection to examine the DomainService and to infer the entity types and their read/insert/update/delete accessibilities. From this, LightSwitch generates its own data service and its own entity types so that business logic can be written against it just like with any other data service.

Unsupported Features

LightSwitch does not support the following features of the WCF RIA DomainService:

• Custom Entity Operations
• Domain Invoke Operations
• Multiple assemblies. The DomainService class and the entity classes that it exposes must be defined in the same assembly. The LightSwitch data import feature doesn’t resolve the entity types to a different assembly.

RIA Data Type Mapping

LightSwitch data types from the DomainService entities to LightSwitch data types according to the following table.

Table 6: RIA Entity Data Type Mapping

RIA Entity Property Type	LightSwitch Type	Supported Attributes
System.Byte[]	Binary
System.Boolean	Boolean	Required
System.Byte	Byte	Required
System.DateTime	DateTime	Required
System.Double	Double	Required
System.Guid	Guid	Required
System.Int16	Int16	Required
System.Int32	Int32	Required
System.Int64	Int64	Required
System.SByte	SByte	Required
System.Single	Single	Required
System.String	String
System.TimeSpan	TimeSpan	Required
All others	not supported

Data types exposed as nullable types will be imported as required.

LightSwitch also imports some of the metadata attributes specified on the entity types via System.ComponentModel.DataAnnotations. The supported metadata attributes are listed in Table 7.

Table 7: RIA Attribute Mapping

RIA Attribute	LightSwitch Attribute
DisplayAttribute	DisplayName, Description
EditableAttribute	IsReadOnly
EnumDataTypeAttribute	SupportedValue (choice list)
RangeAttribute	Range
RequiredAttribute	Required
ScaffoldColumnAttribute	Hidden
StringLengthAttribute	MaxLength
TimestampAttribute	Hidden

Exposing Stored Procedures

LightSwitch doesn’t support access to SQL stored procedures via the standard Entity Framework provider. However, you can use a custom DomainService to expose stored procedures.

Say for example that you have a SQL database where access to the data table is read-only. All inserts, updates and deletes must go through a stored procedure. You can create a DomainService that exposes access to the table as a query and implements custom Insert/Update/Delete methods to invoke the stored procedures.

Data Provider

Under the hood, LightSwitch does all of the necessary translation between its native entity types and the DomainService entity types. For queries, we also rewrite the query expression tree with the DomainService entity types and hand it off to the DomainSerivce for processing. For updates, we copy the changed LightSwitch entities into a new DomainService change set.

Summary

LightSwitch supports accessing data from a number of different data sources and aggregating that data together. LightSwitch can access data from SQL Server Express, SQL Server and SQL Azure and can also deploying its intrinsic database to any of these. LightSwitch can also access lists from SharePoint 2010, but does not yet have support for arbitrary OData sources.

To extend the data sources that LightSwitch can access, you can use a custom data adapter by creating a WCF RIA DomainService class and the related entity classes.